Erase numeric digits only

A

an

Hello!

I have an alphanumeric column data with:

nnnTEXT
nnnnnTEXT
nTEXT
nnTEXT
....

Where n=numeric digits

In column, I need to erase all numeric digits, to TEXT.
Is it possible with Excel Function?

Thanks in advance.
an
 
F

Frank Kabel

Hi
if the numbers always start at the beginning try the following array
formula (entered with CTRL+SHIFT+ENTER):
=MID(A1,MAX(IF(ISNUMBER(--MID(A1,ROW(INDIRECT("1:1024")),1)),ROW(INDIRE
CT("1:1024"))))+1,1024)
 
A

an

Ok, FK.

Work OK!
Many thanks.
an
-----Original Message-----
Hi
if the numbers always start at the beginning try the following array
formula (entered with CTRL+SHIFT+ENTER):
=MID(A1,MAX(IF(ISNUMBER(--MID(A1,ROW(INDIRECT ("1:1024")),1)),ROW(INDIRE
CT("1:1024"))))+1,1024)

--
Regards
Frank Kabel
Frankfurt, Germany



.
Click to expand...
 
J

JE McGimpsey

One way:

Public Function DeleteNumbers(ByVal sIn As String) As String
Dim i As Long
If sIn Like "*[0-9]*" Then 'only process if numbers
For i = 1 To Len(sIn)
If Mid(sIn, i, 1) Like "[!0-9]" Then
DeleteNumbers = DeleteNumbers & Mid(sIn, i, 1)
End If
Next i
Else
DeleteNumbers = sIn
End If
End Function

If you don't know much about UDF's see David McRitchie's "Getting
Started with Macros and User Defined Functions":

http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
A

Aladin Akyurek

Faster...

=SUBSTITUTE(A1,LEFT(A1,SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,
7,8,9},""))))),"")
 
Top