When using Excel, by default, cell contents that look like a number are treated as a number.
At times, this can be inconvenient as Excel also truncates leading zeroes on text that is a number, but shouldn’t be calculated on (and needs that leading zero). One example of such a number is ZIP codes.
Excel also tries to convert values that look like dates to dates, and subsequently modifies the formatting (e.g. January 2017 can become Jan-17).
A quick shortcut to convert a number or date to a text value is to precede the number or date with an apostrophe (‘). This keeps leading zeroes in place and also prevents Excel from converting a text date value to another date format.
This also works to convert a cell value to text when you want the cell to start with certain symbols that can be used in formulas (e.g. +, -, /, *, =, (, ), [, ]), or to convert Boolean values (true, false) to text.
The apostrophes only show up in the formula bar; they don’t display in the actual cells or print out.
Great post! Very useful.
Thanks, Ty!