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

 

[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]

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

 

[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

 

[Ron Rosenfeld]

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

 

[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]

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