VBA Code to Extract Month and Place in Format

[OilEconomist]

Thanks in advance for your assistance. How can I take the MS Excel formula:

= CONCATENATE( TEXT(RIGHT(LEFT($C9,7), 2), "00"), " ", TEXT( (RIGHT(LEFT($C9, 7), 2) ) * 29, "MMMM") )

and transform it into a VBA Code

Basically I want to take a column C which has date values in a text format such as “2019-11-04 @ 15:03” and in a column F, transform it into the format of month number and month name. Here is how it would look for the various months, but the issue is it gives me the date. It will show: "11 November" but it the cell it has it as the date 11/4/2019.

01 January
02 February
03 March
04 April
05 May
06 June
07 July
08 August
09 September
10 October
11 November
12 December

The code I have written is as follows, but there is in issue with this line:

"= CONCATENATE( TEXT( RIGHT(LEFT($C9, 7), 2), ""00""), "" "", TEXT(( RIGHT( LEFT($C9, 7), 2))*29, ""MMMM"" ) )"

as it's returning a full date.

Sub DtFrmt()

Dim LastRow As Long

    Sheets("Sheet1").Activate
               
    With Sheets("Sheet1")
        LastRow = .Cells.Find(What:="*", after:=.Cells(1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    End With
               
    Range("F9:F" & LastRow).Formula = _
        "= CONCATENATE( TEXT( RIGHT(LEFT($C9, 7), 2), ""00""), "" "", TEXT(( RIGHT(LEFT($C9, 7), 2))*29, ""MMMM"") )"
    Range("F9:F" & LastRow).Value = Range("F9:F" & LastRow).Value

End Sub

 

[GWteB]

When text is entered into a cell, Excel checks whether the text can be converted to a valid date. If this is the case, Excel immediately performs a conversion. To prevent this, the concerned worksheet range must be formatted as text beforehand, like ...

    With Range("F9:F12")
        .NumberFormat = "General"
        .Formula = "= CONCATENATE( TEXT( RIGHT(LEFT($C9, 7), 2), ""00""), "" "", TEXT(( RIGHT(LEFT($C9, 7), 2))*29, ""MMMM"") )"
        .NumberFormat = "@"
        .Value = .Value
    End With

Hope this helps.

EDIT:
Have changed the order.

 

[jasonb75]

You can use a much shorter formula too

Sub DateFormat()
With Worksheets("Sheet1").Range("F9:F" & Range("C" & Rows.Count).End(xlUp).Row)
    .NumberFormat = "#"
    .Formula = "=TEXT(MID($C9,6,2)*29,""mm mmmm"")"
    .NumberFormat = "@"
    .Value = .Value
End With
End Sub

 

[OilEconomist]

When text is entered into a cell, Excel checks whether the text can be converted to a valid date. If this is the case, Excel immediately performs a conversion. To prevent this, the concerned worksheet range must be formatted as text beforehand, like ...

    With Range("F9:F12")
        .NumberFormat = "General"
        .Formula = "= CONCATENATE( TEXT( RIGHT(LEFT($C9, 7), 2), ""00""), "" "", TEXT(( RIGHT(LEFT($C9, 7), 2))*29, ""MMMM"") )"
        .NumberFormat = "@"
        .Value = .Value
    End With

Hope this helps.

EDIT:
Have changed the order.

Thanks @GWteB as this works as well. I used @jasonb75 's solution as it was a little bit shorter. Thanks so much for your help!

 

[OilEconomist]

You can use a much shorter formula too

Sub DateFormat()
With Worksheets("Sheet1").Range("F9:F" & Range("C" & Rows.Count).End(xlUp).Row)
    .NumberFormat = "#"
    .Formula = "=TEXT(MID($C9,6,2)*29,""mm mmmm"")"
    .NumberFormat = "@"
    .Value = .Value
End With
End Sub

Thanks so much @jasonb75 as it worked.

 

[GWteB]

You're welcome. Glad we could help.

 

[OilEconomist]

You're welcome. Glad we could help.

@GWteB I just feel bad I could not mark both as correct. Once again thanks for your help on this one and the ones in the past.

 

[GWteB]

No worries, I'm not that competitive

 

[Rick Rothstein]

Here is another way to write the macro you want...

Sub DateFormat()
  With Worksheets("Sheet2").Range("F9:F" & Range("C" & Rows.Count).End(xlUp).Row)
    .Value = Evaluate("TEXT(LEFT(" & .Offset(, -3).Address & ",10),""mm mmmm"")")
  End With
End Sub