How can I force Excel to treat a column as text?

I have a column in Excel that contains values such as:

Mar 5934

It's actually not a date, but Excel automatically assumes that it's a date.

How can I force Excel to treat it as text? If I format it as Text, I get the numeric value of the date: 1473449.

I actually want it to be 'Mar 5934' as text, so that the system into which I am importing it reads it correctly.

There are more than 10,000 rows, so I can't edit them manually.

Alternatively, is it possible to copy the formatted "date" into another column as text?

Solution 1:

This worked for me:

• Export the workbook as a CSV file
• Create a new workbook
• Click Data > From Text/CSV and select your CSV file
• Set "Data Type Detection" to "Do not detect data types"
• Select the column
• Right-click anywhere in it and select "Format Cells..."
• Set the format to "Text".

Solution 2:

a workaround if you're in a hurry:

create new column to the right; set original column data type to text; select topmost cell in new column; enter formula:

=TEXT(REF of cell to the left,"?")

fill down; ctrl-c; paste values on original column; delete created column