Formatting the cell as text
Clever Excel formulas December 4th, 2007
Sometimes when you enter text in excel, it automatically converts it to some other format. This can be very annoying. Like if I want to enter age bands as 11-20,21-30 etc then excel would read 11-20 as 20-Nov.
We can get around this problem by adding a single quote in front. This quote won’t get displayed or printed but just tells excel to treat the entries as text. However if you press F2 and try and edit the values, you will see a single quotation mark in front.
But make sure you enter the quote before hitting enter i.e. if excel has already made it 20-Nov and now if you put a quote in front then it won’t change.
This can also come in handy when you have to enter more than 15 digits in a single cell and you want to keep the format as text. If you don’t enter a quote in front or change it to a text format, excel would display it in scientific notation like 4545121215241541514 would be shown as 4.54512E+18.
Share ThisPopularity: 10%
