Remove Numeric from a Text String - Help?

[Bob Phillips]

=TRIM(SUBSTITUTE(A1,MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")
),SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"")))),""))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Louise]

Hi,

Can anyone helps me with a problem I have got?

I have a column of cells with data in e.g.

123 apple
4390 banana
yellow 56973

I need to remove the numerics and leave the text - how can i do this easily?

Thanks,

 

[CLR]

ASAP utilities, a free add-in available at www.asap-utilities.com has a
feature that will do it nicely for you..........

Vaya con Dios,
Chuck, CABGx3

 

[Ron Rosenfeld]

Hi,

Can anyone helps me with a problem I have got?

I have a column of cells with data in e.g.

123 apple
4390 banana
yellow 56973

I need to remove the numerics and leave the text - how can i do this easily?

Thanks,

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

Then use this formula:

=TRIM(REGEX.SUBSTITUTE(A1,"\d"))


--ron

 

[Louise]

Thanks - I have downloaded the add-in, can you tell me how I would use it to
solve my problem?

Thanks again!
Louise

 

[Gord Dibben]

You can also use a User Defined Function

Function DeleteNonNumerics(ByVal sStr As String) As Long
Dim i As Long
If sStr Like "*[0-9]*" Then
For i = 1 To Len(sStr)
If Mid(sStr, i, 1) Like "[0-9]" Then
DeleteNonNumerics = DeleteNonNumerics & Mid(sStr, i, 1)
End If
Next i
Else
DeleteNonNumerics = sStr
End If
End Function

=DeleteNonNumerics(cellref)


Gord Dibben MS Excel MVP

 

[Gord Dibben]

ooooops!

Having a problem with the dyslexia again.

Do not use this UDF..........it strips text, not numbers.

Try this macro...........

Sub RemoveNums()
'' Remove numeric characters from a string.
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String

Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)

For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Not (Mid(rngR.Value, intI, 1)) Like "[0-9]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR

End Sub


Gord

You can also use a User Defined Function

Function DeleteNonNumerics(ByVal sStr As String) As Long
Dim i As Long
If sStr Like "*[0-9]*" Then
For i = 1 To Len(sStr)
If Mid(sStr, i, 1) Like "[0-9]" Then
DeleteNonNumerics = DeleteNonNumerics & Mid(sStr, i, 1)
End If
Next i
Else
DeleteNonNumerics = sStr
End If
End Function

=DeleteNonNumerics(cellref)


Gord Dibben MS Excel MVP

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

Then use this formula:

=TRIM(REGEX.SUBSTITUTE(A1,"\d"))


--ron

Gord Dibben MS Excel MVP

 

[CLR]

Be sure and experiment on a COPY of your workbook, not the real thing until
you see how things go.........

After installing the Add-in, highlight the area you wish changed, then click
on "ASAP Utilities" in the upper toolbar, then select "TEXT" and then
"Delete all numbers in selection" then OK................

Vaya con Dios,
Chuck, CABGx3

 

[Bob Umlas]

Interesting formula, but fails on something like a1a1a1a1 or 123 Main 123.

 

[Bob Phillips]

Yeah, but none of the examples were of that form.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)