Removing characters from a cell (keeping only the numbers)

[Monk]

Hi..,
Is there a formula to enable me to copy just the numbers of a cell which
also contains letters/characters (example Cell A1: FT’’/@12345’ )

All I want to do is remove all the non number characters (leaving me with
12345 in the above example)..?
I’m unable to use the left, mid and right formulas (well be longwinded) as
there is no specific number of characters before or after the actual numbers
(which are always together/not split up)…?

Any thoughts ..?

Many thanks
Monk

[Monk]

"Monk" wrote:

> Hi..,
> Is there a formula to enable me to copy just the numbers of a cell which
> also contains characters (example Cell A1: ''12345' )
>
> All I want to do is remove all the '' characters (leaving me with
> 12345 in the above example)..?
> I’m unable to use the left, mid and right formulas (well be longwinded) as
> there is no specific number of '''' characters before or after the actual numbers
> (which are always together/not split up)…?
>
> Any thoughts ..?
>
> Many thanks
> Monk
>

[Ron Coderre]

If the number will only be at the right end of the string, try this:

A1: (some string ending with numbers)
B1: =MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),99)*1


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Monk" wrote:

> Hi..,
> Is there a formula to enable me to copy just the numbers of a cell which
> also contains letters/characters (example Cell A1: FT’’/@12345’ )
>
> All I want to do is remove all the non number characters (leaving me with
> 12345 in the above example)..?
> I’m unable to use the left, mid and right formulas (well be longwinded) as
> there is no specific number of characters before or after the actual numbers
> (which are always together/not split up)…?
>
> Any thoughts ..?
>
> Many thanks
> Monk
>

[Monk]

Hello,
I attempted to edit the first post, but it has posted a new thread..

To clear up the situation.. It appears the only characters mixed within the
numbers is the ‘character which sometimes appears at the beginning and end of
the set of numbers (but not consistent). So just need to remove the ‘’’’
characters.. ?

I have tried your method Ron (with thanks), but as you mentioned it only
works if there is no ‘ characters to the right of the numbers, which
unfortunately there is on some of the entries.

Monk..!

[Ron Coderre]

OK...see if this works:
A1: (some string containing consequtive numbers)
B1:=LEFT(MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),MAX(SEARCH({0,1,2,3,4,5,6,7,8,9},"0123456789"&A1))),SUMPRODUCT(--ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&(LEN(A1)))),1))))

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Monk" wrote:

>
> Hello,
> I attempted to edit the first post, but it has posted a new thread..
>
> To clear up the situation.. It appears the only characters mixed within the
> numbers is the ‘character which sometimes appears at the beginning and end of
> the set of numbers (but not consistent). So just need to remove the ‘’’’
> characters.. ?
>
> I have tried your method Ron (with thanks), but as you mentioned it only
> works if there is no ‘ characters to the right of the numbers, which
> unfortunately there is on some of the entries.
>
> Monk..!
>

[Ron Rosenfeld]

On Mon, 23 Jan 2006 09:17:02 -0800, "Monk" <[email protected]>
wrote:

>Hi..,
>Is there a formula to enable me to copy just the numbers of a cell which
>also contains letters/characters (example Cell A1: FT’’/@12345’ )
>
>All I want to do is remove all the non number characters (leaving me with
>12345 in the above example)..?
>I’m unable to use the left, mid and right formulas (well be longwinded) as
>there is no specific number of characters before or after the actual numbers
>(which are always together/not split up)…?
>
>Any thoughts ..?
>
>Many thanks
>Monk

For strings up to 255 characters:

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

2. Use this Regular Expression formula:

=REGEX.SUBSTITUTE(A1,"[^0-9]")
--ron

[Gord Dibben]

Not a formula, but a macro.

Sub RemoveAlphas()
'' Remove alpha 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 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 Dibben MS Excel MVP

On Mon, 23 Jan 2006 09:17:02 -0800, "Monk" <[email protected]>
wrote:

>Hi..,
>Is there a formula to enable me to copy just the numbers of a cell which
>also contains letters/characters (example Cell A1: FT’’/@12345’ )
>
>All I want to do is remove all the non number characters (leaving me with
>12345 in the above example)..?
>I’m unable to use the left, mid and right formulas (well be longwinded) as
>there is no specific number of characters before or after the actual numbers
>(which are always together/not split up)…?
>
>Any thoughts ..?
>
>Many thanks
>Monk

[wjohnson]

Don't know how often you need to do this but you can copy the column and paste into WORD and then do a FIND and REPLACE - Just do a FIND any letter, in WORD this is, ^$.

[Monk]

Thanks to all who took time to reply..
Ron, your formual works brill.. Thanks again ... Monk *





"Ron Coderre" wrote:

> OK...see if this works:
> A1: (some string containing consequtive numbers)
> B1:=LEFT(MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),MAX(SEARCH({0,1,2,3,4,5,6,7,8,9},"0123456789"&A1))),SUMPRODUCT(--ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&(LEN(A1)))),1))))
>
> Does that help?
>
> ***********
> Regards,
> Ron
>
> XL2002, WinXP-Pro
>
>
> "Monk" wrote:
>
> >
> > Hello,
> > I attempted to edit the first post, but it has posted a new thread..
> >
> > To clear up the situation.. It appears the only characters mixed within the
> > numbers is the ‘character which sometimes appears at the beginning and end of
> > the set of numbers (but not consistent). So just need to remove the ‘’’’
> > characters.. ?
> >
> > I have tried your method Ron (with thanks), but as you mentioned it only
> > works if there is no ‘ characters to the right of the numbers, which
> > unfortunately there is on some of the entries.
> >
> > Monk..!
> >

[Ron Coderre]

Here's a shorter formula for extracting consecutive numbers from anywhere in
a string:
A1: (string containing consecutive numbers, eg abc123xyz)

B1:=MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),SUMPRODUCT(--ISNUMBER(--MID(A1,ROW(INDEX($A:$A,1,1):INDEX($A:$A,LEN(A1),1)),1))))

It replaces my previously posted:
LEFT(MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),MAX(SEARCH({0,1,2,3,4,5,6,7,8,9},"0123456789"&A1))),SUMPRODUCT(--ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&(LEN(A1)))),1))))


***********
Regards,
Ron

XL2002, WinXP-Pro


"Monk" wrote:

> Thanks to all who took time to reply..
> Ron, your formual works brill.. Thanks again ... Monk *
>
>
>
>
>
> "Ron Coderre" wrote:
>
> > OK...see if this works:
> > A1: (some string containing consequtive numbers)
> > B1:=LEFT(MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),MAX(SEARCH({0,1,2,3,4,5,6,7,8,9},"0123456789"&A1))),SUMPRODUCT(--ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&(LEN(A1)))),1))))
> >
> > Does that help?
> >
> > ***********
> > Regards,
> > Ron
> >
> > XL2002, WinXP-Pro
> >
> >
> > "Monk" wrote:
> >
> > >
> > > Hello,
> > > I attempted to edit the first post, but it has posted a new thread..
> > >
> > > To clear up the situation.. It appears the only characters mixed within the
> > > numbers is the ‘character which sometimes appears at the beginning and end of
> > > the set of numbers (but not consistent). So just need to remove the ‘’’’
> > > characters.. ?
> > >
> > > I have tried your method Ron (with thanks), but as you mentioned it only
> > > works if there is no ‘ characters to the right of the numbers, which
> > > unfortunately there is on some of the entries.
> > >
> > > Monk..!
> > >