Need to seperate alpha numeric data in a cell

[Andyb_140]

I have a number of cells that contain words and numbers. How can I ge
rid of or seperate the numbers form the data.
e.g Cell A1 contains - Bridge Terminal 123456
I need to just get 123456
Please note numbers can be anywhere in the data and are differen
lengths.

Thanks in advance

Andre

 

[kkknie]

I think you need a user defined function (UDF) for this. Go to the VB
editor, insert a new module and enter the following:

Function OnlyNumber(xstrIn)

Dim i As Integer
Dim strReturn As String

For i = 1 To Len(xstrIn)
If IsNumeric(Mid(xstrIn, i, 1)) Then strReturn = strReturn &
Mid(xstrIn, i, 1)
Next

OnlyNumber = CLng(strReturn)

End Function

Then call the function using

=OnlyNumber(A1)

K

P.S. If you want to return a string value rather than a number, change
OnlyNumber = CLng(strReturn) to OnlyNumber = strReturn.

 

[Peo Sjoblom]

Can they be in more than one place in the same string, i.e.

abc123efg456

If they can be in different positions but always in a single block like in
your example

=--MID(A1,MATCH(FALSE,ISERROR(--MID(A1,ROW(INDIRECT("1:100")),1)),0),100-SUM
(--ISERROR(--MID(A1,ROW(INDIRECT("1:100")),1))))

entered with ctrl + shift & enter

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Harlan Grove]

Can they be in more than one place in the same string, i.e.

abc123efg456

If they can be in different positions but always in a single block like in
your example

=--MID(A1,MATCH(FALSE,ISERROR(--MID(A1,ROW(INDIRECT("1:100")),1)),0),100-SUM
(--ISERROR(--MID(A1,ROW(INDIRECT("1:100")),1))))

entered with ctrl + shift & enter

...

Expanding on this, if there could be multiple separated decimal numeral
substrings, try

B1 [array formula]:
=MID(LEFT($A1,SMALL((1+(ISNUMBER(-MID($A1,ROW(INDIRECT("1:1024")),1))
-ISNUMBER(-MID("."&$A1,ROW(INDIRECT("1:1024")),1))))*1000000000
+ROW(INDIRECT("1:1024")),COLUMN()-1)-1),
SMALL((1-(ISNUMBER(-MID($A1,ROW(INDIRECT("1:1024")),1))
-ISNUMBER(-MID("."&$A1,ROW(INDIRECT("1:1024")),1))))*1000000000
+ROW(INDIRECT("1:1024")),COLUMN()-1),1024)

Granted this is a much longer formula, but fill it right into, say, C1:G1, then
fill B1:G1 down as needed.