Excel Date to String conversion

In a cell in Excel sheet I have a Date value like:

01/01/2010 14:30:00

I want to convert that Date to Text and also want the Text to look exactly like Date. So a Date value of 01/01/2010 14:30:00 should look like 01/01/2010 14:30:00 but internally it should be Text.

How can I do that in Excel?

Solution 1:

=TEXT(A1,"DD/MM/YYYY hh:mm:ss")

(24 hour time)

=TEXT(A1,"DD/MM/YYYY hh:mm:ss AM/PM")

(standard time)

Solution 2:

Here is a VBA approach:

Sub change()
    toText Sheets(1).Range("A1:F20")
End Sub

Sub toText(target As Range)
Dim cell As Range
    For Each cell In target
        cell.Value = cell.Text
        cell.NumberFormat = "@"
    Next cell
End Sub

If you are looking for a solution without programming, the Question should be moved to SuperUser.

Solution 3:

Here's another option. Use Excel's built in 'Text to Columns' wizard. It's found under the Data tab in Excel 2007.

If you have one column selected, the defaults for file type and delimiters should work, then it prompts you to change the data format of the column. Choosing text forces it to text format, to make sure that it's not stored as a date.

Solution 4:

In some contexts using a ' character beforehand will work, but if you save to CSV and load again this is impossible.

'01/01/2010 14:30:00