CODEWITHSUNDEEP
vbams-word
user43924
user43924

Reputation: 11

Convert date string to month names in VBA

The macro searches for a code [DM] and then needs to convert the date found from "17/11/2013" to "17-Nov-2013". I have tried a number of options suggested without any success so far but I expect that I need to sonehow link the cell text as the text to be formatted.

Sub ChangeMonth()
Dim strCellText As String

'Change month numbers to words
Do
    Selection.HomeKey Unit:=wdStory
    Selection.Find.ClearFormatting
    With Selection.Find
        .Text = "[DM]"
        .Forward = True
        .Wrap = wdFindContinue
    End With
    If Selection.Find.Execute = False Then
        Exit Do
    Else
        Selection.Delete Unit:=wdCharacter, Count:=1
        Selection.SelectCell
        Debug.Print Format(CDate(strCellText), "DD-MMM-YYYY")
    End If
Loop


End Sub

Upvotes: 0

Views: 5153

Answers (1)

David Zemens
David Zemens

Reputation: 53663

Try simply the Format function?

Debug.Print Format(#2014/01/01#, "DD/MMM/YYYY")

Results in:

01/Jan/2014

Likewise for other date formats:

Debug.Print Format(#01/01/2014#, "DD-MMM-YYYY")

Results in:

01-Jan-2014

The Format function takes a date or date-like string as its first argument. Depending on the data you have, you may need to wrap it in CDate to ensure the correct conversion, like:

Format(Cdate(var), "DD-MM-YYYY")
'assumes "var" is a variable which represents the date/string value

enter image description here

And another screenshot using CDate to coerce a string to a date type (although I don't think this is necessary, but just in case):

enter image description here

In your code I made a few changes:

Sub ChangeMonth()
Dim strCellText As String
Dim c As Selection
Dim rng As Range

'Change month numbers to words
Do
    Selection.HomeKey Unit:=wdStory
    Selection.Find.ClearFormatting
    With Selection.Find
        .Text = "[DM]"
        .Forward = True
        .Wrap = wdFindContinue
    End With
    If Selection.Find.Execute = False Then
        Exit Do
    Else
        Selection.Delete Unit:=wdCharacter, Count:=1
        Selection.SelectCell
        Set c = Selection
        'Set rng = c.Range.Words(1, c.Words.Count - 1)
        strCellText = Trim(Left(c.Text, Len(c.Text) - 2))
        'Debug.Print Format(CDate(strCellText), "DD-MMM-YYYY")
        Selection.Text = Format(CDate(strCellText), "DD-MMM-YYYY")
    End If
Loop


End Sub

Upvotes: 1

Related Questions