Excel Date to String conversion


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.


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?

I have no idea about the year of publication of the question; it might be old now. So, I expect my answer to be more of a reference for future similar questions after my post.

I don't know if anybody out there has already given an answer similar to the one I am about to give, which might result -I think- being the simplest, most direct and most effective: If someone has already given it, I apologize, but I haven't seen it. Here, my answer using CStr instead of TEXT:

Asuming cell A1 contains a date, and using VBA code:

Dim strDate As String

'Convert to string the value contained in A1 (a date)
strDate = CStr([A1].Value)

You can, thereafter, manipulate it as any ordinary string using string functions (MID, LEFT, RIGHT, LEN, CONCATENATE (&), etc.)

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

In Excel 2010, marg's answer only worked for some of the data I had in my spreadsheet (it was imported). The following solution worked on all data.

Sub change()
    toText Selection
End Sub

Sub toText(target As range)
Dim cell As range
Dim txt As String
    For Each cell In target
        txt = cell.text
        cell.NumberFormat = "@"
        cell.Value2 = txt
    Next cell
End Sub