Phantom Formatting Ruining My Formula Results

jardenp

Active Member
Joined
May 12, 2009
Messages
369
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!
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,917
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Format the cells as General. Then select the data, choose Text to columns on the data tab and press Finish.
 

jardenp

Active Member
Joined
May 12, 2009
Messages
369
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,917
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

Active Member
Joined
May 12, 2009
Messages
369
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,132,685
Messages
5,654,746
Members
418,149
Latest member
amamiche67

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top