Macro to Find dates and replace with Text.

uhguy

Board Regular
Joined
Aug 22, 2008
Messages
79
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,

Code:
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!
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,732
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:
Code:
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

Board Regular
Joined
Aug 22, 2008
Messages
79
Wow that was quick, so short and does exactly what I need it too! Thanks Joe!!
 

Watch MrExcel Video

Forum statistics

Threads
1,095,367
Messages
5,444,045
Members
405,264
Latest member
JohnP1972

This Week's Hot Topics

Top