Whole Column as text

Question

I have an excel document which the first column is formated:

date
2017-08-01
2017-08-01
2017-08-01
2017-08-01
2017-08-01

What I do is to copy the whole column to a text document and paste it back to the worksheet. Tt fixes the date format on its own, it comes back, perfectly:

DATE
01/08/2017
01/08/2017
01/08/2017
01/08/2017
01/08/2017

I have tried formating the cells within excel (dd/mm/yyyy) and record macro, nothing works, only the cutting and pasting onto notepad, now I need to achieve this by vba, I tried the normal:

     Range("a2:a5").Select
 Selection.NumberFormat = "@"

But it did not work, any suggestions?

Have you tried `Range("A2:A5").NumberFormat="dd/mm/yyyy"` (note - no need to use Select, just format the range). — Darren Bartrup-Cook, Sep 26 '17 at 15:45

score 1 · Answer 1 · answered Sep 26 '17 at 15:59

If the dates are stored as text you can multiply them by 1 (or add 0) and then format. The calculation on the range lets Excel know it's dealing with numbers.

The code was taken from Multiply Entire Range By Value?

Sub Test()

    Dim rData As Range

    Set rData = ThisWorkbook.Worksheets("Sheet1").Range("A2:A5")
    rData = Evaluate(rData.Address & "*1")
    rData.NumberFormat = "dd/mm/yyyy"

End Sub

score 0 · Accepted Answer · answered Sep 26 '17 at 16:39

In addition to the Evaluate methods for convertiong texts to number:

[a2:a6] = [--a2:a6]
[a2:a6].NumberFormat = "dd/mm/yyyy"

there is also the Excel method of copying blank cell and selecting Add in the Paste Special dialog:

[b1].Copy                                           ' copy blank cell
[a2:a6].PasteSpecial , xlPasteSpecialOperationAdd   ' and add it to the destination range
[a2:a6].NumberFormat = "dd/mm/yyyy"

Whole Column as text

2 Answers2