1

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 г.
zkvvoob
  • 394
  • 1
  • 4
  • 26
  • 1
    Are the text-that-look-like-dates-with-extras in a fixed format so they are all the same length; e.g. are they 03/05/2017 or 3/5/2017? If so, then Text-To-Columns with Fixed length and the proper DMY vs. MDY format is a better method. Sorry, no sample data so no solution. –  Mar 17 '17 at 17:03
  • Unfortunately, the text-that-should-be-a-date is not with fixed length, as the generating program uses the month's name and, besides, the date is sometimes with 1 digit, sometimes - with two. – zkvvoob Mar 17 '17 at 18:17
  • So I guess supplying several examples of the 'date-with-text' might have been a good idea. –  Mar 17 '17 at 18:24

1 Answers1

2

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.

Community
  • 1
  • 1
  • Here's a sample list of dates. Would that help you tweak the VBA? http://pastebin.com/ePwiEd3w Also, I can see you've used TextToColumns, I assume that means I'll need an empty column somewhere? Or not? – zkvvoob Mar 17 '17 at 18:21
  • Just to clarify, because I'm not too proficient in VBA: should there be an empty column somewhere in the array of raw data, say after the date column (because it's not the only one)? This is what happens if I use the second snippet above: http://imgur.com/a/fPvo5; If I use the first one, `17` is stripped from the year. If I use the last one, the trailing characters do get removed, but what's left is still a text for Excel. :( – zkvvoob Mar 17 '17 at 18:59
  • How are you getting `" г."` to show up in VBA code without being reverted to `" ?."`? The `г` is actually unicode `ChrW(1075)` which VBA code does not reproduce without ChrW. –  Mar 17 '17 at 19:15
  • Well, initially I used the point-and-click method to record the macro, so the `г.` appeared on its own in the code. – zkvvoob Mar 18 '17 at 07:20