Macro to Find dates and replace with Text.

[uhguy]

Hi,

Not sure if this is even possible but maybe it is and Im just not creative enough to create.

I would like to be able to find dates in column C dates and replace the cell with text representing the month.

For instance if cell C9 has 1/1/13, the macro would replace the data in there with "'January"

So far this is what I have,

Range("C9").Select
    ActiveCell.FormulaR1C1 = "=MONTH(RC[0])"
    Range("C9").Select
    Selection.NumberFormat = "@"
    Selection.NumberFormat = "mmmm;@"
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

The problem is that the value of that cell is actually = 1, not "January".
The other problem is that I need to happen to all cells in column C, my macro only would change the cell i selected, in this case C9

Thanks in advance!

 

[JoeMo]

This requires you to select the range with dates you want to convert to month names, but you can easily adapt it to your needs:

Sub ReplaceDateWithMonth()
'Select the range before running this macro
For Each c In Selection
    If IsDate(c) Then
        c.Value = MonthName(Month(c.Value))
    End If
Next c
End Sub

 

[uhguy]

Wow that was quick, so short and does exactly what I need it too! Thanks Joe!!

 

[JoeMo]

Wow that was quick, so short and does exactly what I need it too! Thanks Joe!!

You are welcome - thanks for your reply.