Extract multiple numbers from a cell

Here are some examples of text with multiple numbers embedded:
A1 = 3031 // 2841;1886-ring road location
A2 = 3305 //1455-historical
A3 = //3491;3492
A4 = //inactive location; historical = 1790; enter new locaion

Result should be:
B1 = 3031;2841;1886
B2 = 3305;1455
B3 = 3491;3492
B4 = 1790

Try this UDF (User Defined function). From workbook launch VBE using Alt+F11.
From menu Insert a Module and paste the below function.Close and get back to
workbook and try the below formula.

=ExtractNumbers(A1)

Function ExtractNumbers(strData As String) As String
Dim intTemp As Integer, strChr As String
For intTemp = 1 To Len(strData)
strChr = Asc(Mid(strData, intTemp, 1))
Select Case strChr
Case 48 To 57
ExtractNumbers = ExtractNumbers & Mid(strData, intTemp, 1)
Case 47, 59
If ExtractNumbers "" Then
If Right(ExtractNumbers, 1) ";" And Right(ExtractNumbers, 1) "/"
Then
ExtractNumbers = ExtractNumbers & ";"
End If
End If
End Select
Next
If Not IsNumeric(Right(ExtractNumbers, 1)) Then
ExtractNumbers = Left(ExtractNumbers, Len(ExtractNumbers) - 1)
End If
End Function

--
Jacob (MVP - Excel)


"Pomona" wrote:

Here are some examples of text with multiple numbers embedded:
A1 = 3031 // 2841;1886-ring road location
A2 = 3305 //1455-historical
A3 = //3491;3492
A4 = //inactive location; historical = 1790; enter new locaion

Result should be:
B1 = 3031;2841;1886
B2 = 3305;1455
B3 = 3491;3492
B4 = 1790

If you have (line break) issues with the previous code ; try the below version

Function ExtractNumbers(strData As String) As String
Dim intTemp As Integer, strChr As String
For intTemp = 1 To Len(strData)
strChr = Asc(Mid(strData, intTemp, 1))
Select Case strChr
Case 48 To 57
ExtractNumbers = ExtractNumbers & Mid(strData, intTemp, 1)
Case 47, 59
If ExtractNumbers "" Then
If Right(ExtractNumbers, 1) ";" And Right(ExtractNumbers, 1) _
"/" Then ExtractNumbers = ExtractNumbers & ";"
End If
End Select
Next
If Not IsNumeric(Right(ExtractNumbers, 1)) Then
ExtractNumbers = Left(ExtractNumbers, Len(ExtractNumbers) - 1)
End If
End Function


--
Jacob (MVP - Excel)


"Jacob Skaria" wrote:

Try this UDF (User Defined function). From workbook launch VBE using Alt+F11.
From menu Insert a Module and paste the below function.Close and get back to
workbook and try the below formula.

=ExtractNumbers(A1)

Function ExtractNumbers(strData As String) As String
Dim intTemp As Integer, strChr As String
For intTemp = 1 To Len(strData)
strChr = Asc(Mid(strData, intTemp, 1))
Select Case strChr
Case 48 To 57
ExtractNumbers = ExtractNumbers & Mid(strData, intTemp, 1)
Case 47, 59
If ExtractNumbers "" Then
If Right(ExtractNumbers, 1) ";" And Right(ExtractNumbers, 1) "/"
Then
ExtractNumbers = ExtractNumbers & ";"
End If
End If
End Select
Next
If Not IsNumeric(Right(ExtractNumbers, 1)) Then
ExtractNumbers = Left(ExtractNumbers, Len(ExtractNumbers) - 1)
End If
End Function

--
Jacob (MVP - Excel)


"Pomona" wrote:

Here are some examples of text with multiple numbers embedded:
A1 = 3031 // 2841;1886-ring road location
A2 = 3305 //1455-historical
A3 = //3491;3492
A4 = //inactive location; historical = 1790; enter new locaion

Result should be:
B1 = 3031;2841;1886
B2 = 3305;1455
B3 = 3491;3492
B4 = 1790

VBA is the better solution if it is allowed by your administrator, otherwise
it is possible if you use 2 helper columns per figure required. e.g:

in C1:

=MATCH(1,INDEX((MID($A1,ROW(INDIRECT("1:"&LEN($A1) )),1)="0")*
(MID($A1,ROW(INDIRECT("1:"&LEN($A1))),1)="9"),),0 )

in D1:

=IF(ISNA(MATCH(1,INDEX((MID($A1,ROW(INDIRECT(C1&": "&
LEN($A1))),1)"0")+(MID($A1,ROW(INDIRECT(C1&":"&LE N($A1)))
,1)"9"),),0)-1),LEN($A1)-C1+1,MATCH(1,INDEX((MID($A1,
ROW(INDIRECT(C1&":"&LEN($A1))),1)"0")+
(MID($A1,ROW(INDIRECT(C1&":"&LEN($A1))),1)"9"),), 0)-1)

in E1:

=IF(C1+D1LEN($A1),C1+D1-1+MATCH(1,INDEX((MID($A1,
ROW(INDIRECT(C1+D1&":"&LEN($A1))),1)="0")*
(MID($A1,ROW(INDIRECT(C1+D1&":"&LEN($A1))),1)="9" ),),0),NA())

Now copy D1 to F1, then copy E1:F1 to G1:H1 (as far along as required), and
copy C1:H1 as far down as required.


In J1:

=IF(ISNA($C1),"",MID($A1,$C1,$D1)&IF(ISNA($E1),"",
";"&MID($A1,$E1,$F1)&IF(ISNA($G1),"",";"&MID($A1,$ G1,$H1))))

extending this formula as far as you require.



I originally tried getting this to work without helper columns, but even
getting the first figure out of each case results in a ridiculous formula.

For the record:

=MID($A1,MATCH(1,INDEX((MID($A1,ROW(INDIRECT("1:"& LEN($A1)
)),1)="0")*(MID($A1,ROW(INDIRECT("1:"&LEN($A1))), 1)="9"),),0),
MATCH(1,INDEX((MID($A1,ROW(INDIRECT(MATCH(1,INDEX( (MID(
A1,ROW(INDIRECT("1:"&LEN($A1))),1)="0")*(MID($A1, ROW(
INDIRECT("1:"&LEN($A1))),1)="9"),),0)&":"&LEN($A1 ))),1)"0")+(
MID($A1,ROW(INDIRECT(MATCH(1,INDEX((MID($A1,ROW(IN DIRECT(
"1:"&LEN($A1))),1)="0")*(MID($A1,ROW(INDIRECT("1: "&LEN($A1)))
,1)="9"),),0)&":"&LEN($A1))),1)"9"),),0)-1)





"Jacob Skaria" wrote in message
...
Try this UDF (User Defined function). From workbook launch VBE using
Alt+F11.
From menu Insert a Module and paste the below function.Close and get back
to
workbook and try the below formula.

=ExtractNumbers(A1)

Function ExtractNumbers(strData As String) As String
Dim intTemp As Integer, strChr As String
For intTemp = 1 To Len(strData)
strChr = Asc(Mid(strData, intTemp, 1))
Select Case strChr
Case 48 To 57
ExtractNumbers = ExtractNumbers & Mid(strData, intTemp, 1)
Case 47, 59
If ExtractNumbers "" Then
If Right(ExtractNumbers, 1) ";" And Right(ExtractNumbers, 1) "/"
Then
ExtractNumbers = ExtractNumbers & ";"
End If
End If
End Select
Next
If Not IsNumeric(Right(ExtractNumbers, 1)) Then
ExtractNumbers = Left(ExtractNumbers, Len(ExtractNumbers) - 1)
End If
End Function

--
Jacob (MVP - Excel)


"Pomona" wrote:

Here are some examples of text with multiple numbers embedded:
A1 = 3031 // 2841;1886-ring road location
A2 = 3305 //1455-historical
A3 = //3491;3492
A4 = //inactive location; historical = 1790; enter new locaion

Result should be:
B1 = 3031;2841;1886
B2 = 3305;1455
B3 = 3491;3492
B4 = 1790