Phantom Formatting Ruining My Formula Results

[jardenp]

I have two columns. Column A is a unit number from 53100 - 53165. Column B is the simple formula =IF({column A cell}>53109,"RF",""). This works fine for the first third of my rows. The last two-thirds have some formatting that results in "RF" being returned for all unit numbers, whether they are >53109 or not.

I've selected column A and cleared all formats (from the Home/editing ribbon). I've changed the format of column A to text and then back to number. I've copied all and pasted the values back.

I don't know what the problem is. The first third automatically aligns to the right when the column is formatted to Number, but the others stay aligned left. When the column is formatted to Text, they all align left, but the formula still works for the first third and not for the others.

I'm baffled. If I run an arithmetical operation on the misbehaving unit numbers, the results are normal, e.g. cell A103 (unit number 53107) returns "RF" from the formula (it shouldn't) and "7" from "=A103-53100" (as it should).

Any advice?

Thanks!

 

[RoryA]

Format the cells as General. Then select the data, choose Text to columns on the data tab and press Finish.

 

[jardenp]

That worked perfectly. Thank you very much. Do you know what the hang up was though? I'm still curious.

And thanks for the quick reply!

 

[RoryA]

If the values are entered as text, they are stored that way even if you alter the format, unless you re-enter them (which is what the TTC effectively does) with the format changed to something other than Text.

 

[jardenp]

That makes sense. One work around I found was to highlight each cell, click on the formula bar to open it to editing, and then press enter. That would do the same thing as the TTC, but is unworkable for hundreds of rows.

Thanks for the follow up!