Formatting conundrum

[Eloise T]

Note attached Excel file with only one cell, B3.

Note the cell's contents and the corresponding data in the Formula Bar DO NOT MATCH!

Can anyone tell me why?

If you do a =TRIM(CLEAN(B3)) from another cell it fixes the discrepancy, but how did it get there in the first place?

If you try to change the data from the Formula Bar, it won't be reflected in cell B3.
e.g. Change 75 in the cell to be BOLD and RED. It won't "stick."

P.S. I'm using Excel 2007.

 

[vletm]

Did You copy and paste that 'B3' from somewhere?
If You want to paste ONLY value UN75F63000AF (without formatting)
then activate that cell B3 and paste to formula bar
then cell format won't paste
or
make Paste Special ... Values.

 

[Hui]

Your first Character in the cell B3 is the Character with Ascii code 9
ie: type =CODE(MID(B3,1,1)) some where

9 is the code for Tab, so Excel is trying to do the best it can and that is why you have an issue

If you want you can use =Clean(B3)
that will remove it

 

[Eloise T]

Thank you Hui and vletm. You guys always have good responses and make me think.

Interestingly, ASCII code 9 has always been a pest that somehow rears its ugly head and wiggles into the data I get from the sales staff when they send me their numbers for crunching.

Is =TRIM(CLEAN(B3)) overkill?

 

[Hui]

Yep
Clean() gets rid of Non-Printing characters
Trim() removes spaces

 

[vletm]

'Reason >> Consequence'
If the sales staff send 'numbers' like that and You (like) to trim(clean) ... hmm..
But if You don't want to do that always then You could try to find 'reason'.
Why do they send 'numbers' like that?

 

[Eloise T]

Yep
Clean() gets rid of Non-Printing characters
Trim() removes spaces

A space is not considered a non-printable character, correct?

 

[Eloise T]

'Reason >> Consequence'
If the sales staff send 'numbers' like that and You (like) to trim(clean) ... hmm..
But if You don't want to do that always then You could try to find 'reason'.
Why do they send 'numbers' like that?

They are "Excel-mentally" bankrupt.