Search code examples
exceloffice-2013

Format all cells in column from date to text

I have an excel document with about 500 rows. I need to format all the cells in , let's say, B column from date to text.

This is how it looks like now:

2012-06-15
2012-06-01
2012-06-14

What it looks like when formated to text:

41075
41061
41074

It has come to my understanding that this is a timestamp representing days since 1st januari 1900, right? Anyhow, this is not what I want. I want the value of the field to be exactly what it is but with the column type of text. I've found various solutions for this using functions like this: =TEXT(B1, "yyyy-mm-dd") but that is not reformating the cell, it is extracting a value from one cell, reformat it and represent is as text in another.

The rule I'm after: I want all cells in B column to have the type text without changing the value

Thanks!

Solution

  • If you have a situation where columns A to D are dates of 500 rows you then:

    1. Use the =TEXT(A1, "yyyy-mm-dd") function you describe in cell E1.

    2. Copy this formula 4 columns wide, and 500 rows down.

    3. Then copy and paste values to the preceding cells.

    Copy Example:

    Copy

    Output:

    Output