=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" wrote in message
...
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,

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,

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






"Louise" wrote:

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,

On Mon, 25 Sep 2006 11:54:02 -0700, Louise
wrote:

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

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

Thanks again!
Louise


"CLR" wrote:

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






"Louise" wrote:

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,

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

On Mon, 25 Sep 2006 15:11:22 -0400, Ron Rosenfeld
wrote:

On Mon, 25 Sep 2006 11:54:02 -0700, Louise
wrote:

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

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


On Mon, 25 Sep 2006 13:16:03 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

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

On Mon, 25 Sep 2006 15:11:22 -0400, Ron Rosenfeld
wrote:

On Mon, 25 Sep 2006 11:54:02 -0700, Louise
wrote:

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

Gord Dibben MS Excel MVP

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





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

Thanks again!
Louise


"CLR" wrote:

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






"Louise" wrote:

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,

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

"Bob Phillips" wrote in message
...
=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" wrote in message
...
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,

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

--
HTH

Bob Phillips

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

"Bob Umlas" wrote in message
...
Interesting formula, but fails on something like a1a1a1a1 or 123 Main 123.

"Bob Phillips" wrote in message
...

=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" wrote in message
...
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,