Applying an Excel formula to the same cell whose data you want to modify?

Discussion in 'Mac Apps and Mac App Store' started by jent, Apr 12, 2011.

  1. jent macrumors 6502a

    jent

    Joined:
    Mar 31, 2010
    #1
    I have a few huge columns of data in an Excel spreadsheet. I'm comparing percentages between two of the columns, and annoyingly, a handful of the entries have an additional provision. Instead of just saying "55" (to mean 55%) for example, it'll say "55% or $2.05 per pound."

    I've discovered that the two columns I'm comparing, when they have an additional provision after the percentage, are exactly the same. Since my main task is to just find out what number of entries are the same between the two columns and what number of entries are different between the two columns, I looked up a formula to strip the cell of the text after the percentage symbol (and of the percentage symbol itself).

    Code:
    =LEFT(A1,FIND("%",A1&"%")-1)
    The thing is, if I paste this formula into the cell I want to modify, I get "0". Otherwise, I get exactly what I want. "55% or $2.05 per pound" becomes "55".

    I don't want to create a new column because I've already created some formulas that depend on the current columns being where they are, and conditional formatting to color cells based on a few rules. Is there a way I can apply a formula to the cells in an already-existing column? Thanks!
     
  2. GGJstudios macrumors Westmere

    GGJstudios

    Joined:
    May 16, 2008
    #2
    No, you can't apply a formula in a cell to the cell itself. You're better off using the "text to columns" function to strip off all unwanted data from the column.
     

Share This Page