Force Excel 2007 to treat all values in a column as text

Question

I have a large column of data in Excel. This data should all be treated as text, but in some cells Excel is "magically" changing the data to numeric. This is screwing up my vlookpup() functions in another part of the spreadsheet, and I need to override Excel's automatic data type detection.

If I manually go through the cells, and append ' to each numeric cell, it works. I just don't want to do this by hand for several thousand cells.

For example, this works: Manually type '209

And this does not work: Manually type 209, right click and format as text.

Answer 1

Under the Data Tab, open the Text to Columns wizard and set the Column data format to Text. The destination cell can be set to the original data cell, but it will replace the original formatting with text formatting. Other aspects of formatting e.g. Bold, color, font, etc. are not changed using this method.

Answer 2

Setting the cells to "Text" format, as Jean mentioned, should work. The easiest way to do this, in any version of Excel, is:

Right-click cell, "Format Cells", "Number" tab, select "Text" format.

Answer 3

If changing the format of the column is not an option, it's helpful sometimes to create another column that's 'vlookup friendly' and leave your main column alone.

This is a trick I've used a few times:

Say your 'mixed' column is column A.

In column B, enter the formula:

 =CONCATENATE(A1)

or as Jean-François pointed out in a comment, the shorter version:

 =A1 & ""

And drag it down for to the bottom row.

Column B will be all strings. The VLookup can then use column B.