number remove, data get

JBeaucaire · #6 02-23-2012, 05:42 AM

Put this new FUNCTION into a standard code module:

Code:

Option Explicit

Function CleanText(ByVal Txt As String) As String
Dim X As Long     'Code base by Rick Rothstein (MVP - Excel)
    For X = 1 To Len(Txt)
        If Mid(Txt, X, 1) Like "*[!A-Za-z]*" Then Mid(Txt, X, 1) = Chr(1) ' Leave only letters
    Next
    CleanText = Replace(Txt, Chr(1), "")
End Function

Now, use this as a formula. Your strings are in column A, in B1 enter:

=CLEANTEXT(A1)

...and copy down.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Edit Data Source- Linking template charts to new data	lbf	PowerPoint	0	10-28-2011 12:19 PM
Mailing: how to make the "page number" in Word is the same as "row number" in excel w	Jamal NUMAN	Word	1	09-03-2011 11:37 AM
How to remove "Table of Figures" as we do to remove the "Table of Contents"?	Jamal NUMAN	Word	1	07-08-2011 05:40 PM
Powerpoint: adding data to trend lines w/o data labels	HaiLe	PowerPoint	0	04-11-2011 09:21 AM
How to organize and insert blank cells between a huge number of data?	tareq	Excel	12	09-29-2010 02:12 PM