filtering out text from an alphanumeric string

**himanshu_8** · 10-04-2010, 09:42 AM

Hi,

I have a list of data pieces having a numeric character in the following format:

9 dupont
Reliance
IBM 10
Quest 11
12
Avon Corp 13
.
.
.

Can someone suggest a function to remove the numerals from the list and get the following output:

dupont
Reliance
IBM
Quest
Avon Corp

**Domski** · 10-04-2010, 10:43 AM

A UDF like this would do the trick:

Public Function NoNumbers(rngMyRange As Range) As String

Dim lngMyCount As Long

For lngMyCount = 1 To Len(rngMyRange)
   
    If Not IsNumeric(Mid(rngMyRange, lngMyCount, 1)) Then
         
        NoNumbers = NoNumbers & Mid(rngMyRange, lngMyCount, 1)
        
    End If
    
Next lngMyCount

NoNumbers = Trim(NoNumbers)

End Function

Dom

**Marcol** · 10-04-2010, 11:04 AM

Not sure about Mac2008, but this works in 2007

=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3,"1",""),"2",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8",""),"9",""),"0",""))

There is a more "elegant" way, but I can't remember it, I'll keep on trying!

Hope this helps