Extra apostrophes in Excel

[TechMonkey]

Ok strange one here. For one user (a fiddler) an XML sheet opened with excel shows apostrophes before just about every cell value. Anyone else looking at the sheet doesn't seem them. So anyone else will 5a, he will see '5a. Now he has his own startup Excel sheet but deleting that didn't stop it. Doing a google search just seems to bring up how to remove extra characters from a badly imported sheet. Ohh and the original XML sheet doesn't have them in.

Any one any ideas?

EDIT: Ohh the other thing is it only shows in the formula bar. Not in the actual cell

[elsiegee40]

That single quote is saying the cell contents is text - you can type '123 (say) into a cell and Excel will treat ist as text rather than as a number.

I will have a hunt round to see why it is being displayed... it must be an option somewhere.

[TechMonkey]

Ta, I've had a look at all the options I can think of and nothing jumps out. It has to be somethign turned on though as I've just noticed that header cells that have the title vertically have the ^ before them as well. Grrrrr, I hate fiddelers that don't know how to fix things they do!!

[TechMonkey]

Found it. Took much hunting down. It is text alignment prefix characters found under
(Tools > Options)(Transition tab, "Transition Navigation Keys").

Grrrrrrr, sorted

[jmcdermott]

a quick fix would be in an empty col enter the formula
=RIGHT(A1,(LEN(A1)-1))
assuming col 'A' is the col you want to remove the first char from.

fill down and you should be done.

[elsiegee40]

Found it. Took much hunting down. It is text alignment prefix characters found under
(Tools > Options)(Transition tab, "Transition Navigation Keys").

Grrrrrrr, sorted

Well done, I've bookmarked this thread as I'll never find it when it happens to someone here!

[TechMonkey]

a quick fix would be in an empty col enter the formula
=RIGHT(A1,(LEN(A1)-1))
assuming col 'A' is the col you want to remove the first char from.

fill down and you should be done.

Ahhhh, but when you delete the characters they reappear as it is linked to the formatting not the actual content. Well as far as I can see. It looks like it is some obscure remnant of Lotus 1-2-3 and compatibilty options MS have left in for those changing over. You end up having to either have a ' for left align, a ^ for center align or " for right align.