Sorry ... I entered the data in cells c2 & c3 as per your original post,
named the range and ran the macro. It worked GREAT. I'm using Excel 2007 on
Windows XP.

"DipyDawg" wrote:

Thanks for the response...unfortunately it didn't work...named the
range...created the module, copied/pasted the macro into the module and went
to run and nothing...

"ND Pard" wrote:

Try this:

Name the range you want to have cleaned "rng_to_Clean".

Then copy and paste the following macro into a new Module.

Finally, run the macro.

I think it'll do the trick.

Good Luck.


Sub Clean_Range()
Dim c As Range
Dim i As Integer
Dim strNew As String
strNew = "'"
For Each c In Range("rng_to_Clean")
For i = 1 To Len(c.Value)
If IsNumeric(Mid(c, i, 1)) Or Mid(c, i, 1) = " " Then
If strNew = "'" And Mid(c, i, 1) = " " Then
Else
strNew = strNew & Mid(c, i, 1)
End If
End If
Next i
c.Value = Trim(strNew)
strNew = "'"
Next c
End Sub