Change from Custom Format to Text Format

[mollywage]

I have a list of thousands records in a custom format of yyyy-mm-dd (samples as followed). I need to find the easiest way to copy or change this format to text format besides filtering and manually changing them. Any help is appreciated!


CUSTOM FORMAT TEXT FORMAT
EXP DATE EXP DATE

2010-01-31 2010-01-31
2010-01-31
2010-01-31
2010-01-31
2011-06-30
2011-06-30
2011-06-30
2011-06-30
2011-06-30
2011-06-30
2010-01-31
2011-06-30
2009-12-31
2009-12-31
2009-12-31
2009-12-31
2010-01-31
2010-01-31
2010-01-31
2010-01-31
2010-01-31
2010-01-31
2010-01-31
2011-06-30
2009-12-31
2009-12-31

 

[Makrini]

=""&TEXT(A3,"yyyy-dd-mm")

 

[c_m]

Try:
=TEXT(A2,"yyyy-mm-dd")

 

[NEM25]

If I understand you correctly, then Excel has an option for doing this for you using a wizard. I have used this method frequently since discovering it:

1. Select the column that you want to change (i.e. click on the column letter to select the whole column)

2. On the menu at the top, goto Data => Text to Columns (this will open up the 'Convert Text to Columns Wizard'.

3. For steps one and two, just click 'Next'.

4. For step 3, change the 'Column Data Format' to Text, and then click 'Finish'.

This will convert the format of the selected column to text.

I hope this is helpful.

Regards,

NEM.

 

[mollywage]

Hi Makrini,
I tried all three methods that were posted. Yours worked for what I need in terms of making sure the entire column is in “text” format. Meaning that after using your formula, I can highlight the entire column and use “format cells” and change the column to text as well. I am not sure if this is necessary, but for the sake of uploading the file to another software. I took this step. I also use the formula like yours, but does not have the “”&. This did not work once I changed the column to text. Can you explain what the “”& does to make this formula work differently?

Thanks for your reply.

 

[mollywage]

Update: method no. 2 without the ""& worked as well.

Thanks.

 

[Makrini]

Basically the ""&

is forcing Excel to recognise it as text...

(& is like concatenate)

So ="A" & "B"

would result in "AB"

working without the "" Excel tries to pre guess the format you want. Very handy for a complete novice (i.e those people who prefer WORD to EXcel) - but just plain annoying for the rest of us.