How do I force a column in excel to be stored as text?

Solution 1:

All you need to do for this one is put a single quote in front of your text. This also works for dates you don't want Excel picking up.

eg.

'23446

You can also format your entire column as text which will override the whole column. In Excel 2007, hilight your cells, Right click->Format Cells. Click on text from the options on the left.

Solution 2:

As answered above, formatting a number as string is not advisable because you need to activate the cell in order for the conversion to be actually executed (either by F2 or double clicking or use Find+Replace on the column, and replace a dot with a dot or any other character, but if you don't have a character persistent in each cell in your column then this workaround won't work). And once you enter a cell for some editing it will automatically return to number if the value is compatible.

Now, answering Scott's question in comments section. If you already have your data and manually adding a quote in front of your data in each cell is not an option you can use a helper column and write

="'"&A1

in B1, where A1 is the reference to your cell, and drag down across the formula in B1. At this point you will see the quote, but you can paste data in column B as values (CTRL+C & ALT+E+S and select values, or paste special as values from the top menu). Then find+replace a '(quote) with a '(quote) and you will have a column with values forced to text.

Solution 3:

The answers already mentioned are sometimes not practical:

• Manually prefixing each value is cumbersome and only works for current data.
• Adding another column may not be desirable for the final receiver of the "report".

Since Excel 2013 (or even before), there is a much better way:

• Select the full column.
• Click DATA | Text to Columns.
• In the dialog, click Next | click Next | choose Text | click Finish.