# Extract multiple numbers from a cell

P

#### Pomona

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

J

#### Jacob Skaria

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

J

#### Jacob Skaria

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

S

#### Steve Dunn

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&":"&LEN(\$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+D1<LEN(\$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(INDIRECT(
"1:"&LEN(\$A1))),1)>="0")*(MID(\$A1,ROW(INDIRECT("1:"&LEN(\$A1)))
,1)<="9"),),0)&":"&LEN(\$A1))),1)>"9"),),0)-1)