Why/how can I stop Excel from changing ' ' to '?

Question

Open Excel 2007 and put

' '

in a cell and it will show as

 '

Why? / More importantly / annoyingly how do you stop it?

I set the format to TEXT. No change.

Answer 1

Excel uses the single quote at the start of a cell to indicate that what follows should be treated as text. If you want to have ' ' display you can enter '' '. If you are concerned with having the data ' ' display correctly I don't have a good answer for you without knowing more about your requirements. It is possible that if this applies to a column you could create a custom display format '@ that would cause it to display as you want.