vba - Find/replace macro that properly formats a date cell

Question

I'm using an app that exports certain data to a CSV format. The date column, however, is not properly format in the raw data and contains unnecessary trailing characters for the year, which prevents Excel from interpreting it correctly once imported.

If I manually run Find and Replace on the column, the values are automatically recognized as dates. If I record the operation as a macro, however, and run it again, the trailing character is removed, but the data in the column is retained as a text, not as a date.

Here's the VBA code:

Sub formatDate()

Columns("A:A").Select
Selection.Replace What:=" г.", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
End Sub

The regional format for a date is DD.MM.YYYY. Also, if I simply edit any of the resulting cells (F2) and hit Enter without anything else, Excel switches to the proper format.

Sample data:

4 март 2017 г.
4 март 2017 г.
3 март 2017 г.
1 март 2017 г.
28 февруари 2017 г.
27 февруари 2017 г.
27 февруари 2017 г.
26 февруари 2017 г.
26 февруари 2017 г.

Answer 1

Use TextToColumns to quickly strip off the trailing characters and convert to dates.

With the text-that-look-like-dates in a fixed dd.mm.yyyy format,

with selection
    .TextToColumns Destination:=.cells(1, 1), DataType:=xlFixedWidth, _
                   FieldInfo:=Array(Array(0, xlDMYFormat), Array(10, xlSkipColumn))
    .numberformat = "dd.mm.yyyy"
end with

With the text-that-look-like-dates showing a space after the date and before the trailing text,

With Selection
    .TextToColumns Destination:=.Cells(1, 1), DataType:=xlDelimited, ConsecutiveDelimiter:=True, _
                   Tab:=False, Semicolon:=False, Comma:=False, Space:=True, Other:=False, _
                   FieldInfo:=Array(Array(1, xlDMYFormat), Array(2, xlSkipColumn))
End With

If the TextToColumns is giving you problems with the Bulgarian month names (VBA is very US-EN-centric), then simply add a single line to your existing code and optionally set the cell format.

with Selection
    .Replace What:=" г.", Replacement:=vbNullString, LookAt:=xlPart, _
             SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
             ReplaceFormat:=False
    .value = .value2
    .numberformat = "dd.mm.yyyy"
end with

I've used Selection here (not particularly recommended) but you should be able to easily convert.