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).
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!
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!
