|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
=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" <Louise[ at ]discussions.microsoft.com> wrote in message
news:C9BA44AA-1EB6-4211-A217-1B9DB2E4AC13[ at ]microsoft.com...
[Quoted Text] > 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:
[Quoted Text] > 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 <Louise[ at ]discussions.microsoft.com>
wrote:
[Quoted Text] >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:
[Quoted Text] > 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 <ronrosenfeld[ at ]nospam.org>
wrote:
[Quoted Text] >On Mon, 25 Sep 2006 11:54:02 -0700, Louise <Louise[ at ]discussions.microsoft.com> >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:
[Quoted Text] >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 <ronrosenfeld[ at ]nospam.org> >wrote: > >>On Mon, 25 Sep 2006 11:54:02 -0700, Louise <Louise[ at ]discussions.microsoft.com> >>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" <Louise[ at ]discussions.microsoft.com> wrote in message
news:9D265AB3-8E20-4112-8401-04BEE43AA982[ at ]microsoft.com...
[Quoted Text] > 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" <bob.NGs[ at ]somewhere.com> wrote in message
news:Oy%23UH8N4GHA.1588[ at ]TK2MSFTNGP02.phx.gbl...
[Quoted Text] > =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" <Louise[ at ]discussions.microsoft.com> wrote in message
> news:C9BA44AA-1EB6-4211-A217-1B9DB2E4AC13[ at ]microsoft.com...
>> 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" <Excel_Trickster[ at ]msn.com> wrote in message
news:eJVPYsd4GHA.3556[ at ]TK2MSFTNGP02.phx.gbl...
[Quoted Text] > Interesting formula, but fails on something like a1a1a1a1 or 123 Main 123.
>
> "Bob Phillips" <bob.NGs[ at ]somewhere.com> wrote in message
> news:Oy%23UH8N4GHA.1588[ at ]TK2MSFTNGP02.phx.gbl...
> >
=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" <Louise[ at ]discussions.microsoft.com> wrote in message
> > news:C9BA44AA-1EB6-4211-A217-1B9DB2E4AC13[ at ]microsoft.com...
> >> 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,
> >
> >
>
>
|
|
|