Extract multiple numbers from a cell

[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

 

[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

 

[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

 

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