Macro to delete text from cells and leave dates.

[PastorJessie]

Please help.

I am using Excel 2010 and I need a macro to delete all text from cells and leave the dates.

Below is an example of the contents of six cells. Some have just text, but some have text and dates.

<TABLE style="WIDTH: 146pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=194><COLGROUP><COL style="WIDTH: 146pt; mso-width-source: userset; mso-width-alt: 7094" width=194><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 146pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=194>1st Time Attender: 4/8/2012</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>1st Time Attender:</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>1st Time Attender: 4/5/2012</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>2nd Time Attender:</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>2nd Time Attender: 4/8/2012</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20>2nd Time Attender: 4/5/2012</TD></TR></TBODY></TABLE>


In this example, I would want the new cell contents to be:

4/8/2012

4/5/2012

4/8/2012
4/5/2012


Any help you can give me will be greatly appreciated.

Thanks in advance!

 

[JoeMo]

Assuming your data starts in cell A1 (adjust to suit) and that any date is always preceded by ":" (colon):

Sub DatesOnly()
Dim lRw As Long, rng As Range
lRw = Range("A" & Rows.Count).End(xlUp).Row
Set rng = Range("A1", "A" & lRw)
For Each c In rng
    c.Value = Trim(Mid(c.Value, InStr(1, c.Value, ":") + 1, _
        Len(c.Value) - InStr(1, c.Value, ":") + 1))
Next c
End Sub