Extract multiple 5-digit numbers from alphanumeric text

[jkg77]

Hello,


I need to extract multiple 5-digit numbers from a cell that contains both letters and numbers. I found a thread that solves for how to extract a single number but this doesn't work for me because my data might contain multiple 5-digit numbers.


Here is an example of how my data looks (column A). I would like to extract the 5-digit numbers to column B.


I would like to have the 5-digit numbers in the same cell, separated by commas. Also, the data might contain numbers that are more or less than 5 digits - these should be ignored.


Thank you!

	Column A	Column B
1	One number is 12345. And a second number is 54321.	12345, 54321
2	There is just 1 five digit number: 73638	73638
3	There is 1 five digit number (00293) but also 1 six digit number (038822), which should be ignored. Numbers might have leading zeros.	00293
4	12345, 54321, and 55555.....but not ABC123 or 123ABC.	12345, 54321, 55555

<tbody>
</tbody>

 

[Scott Huish]

This UDF should do that:

Function Multi5(r As String) As String
Dim m
With CreateObject("vbscript.regexp")
    .Pattern = "\b\d{5}\b"
    .Global = True
    If Not .test(r) Then Exit Function
    For Each m In .Execute(r)
        Multi5 = Multi5 & m & ", "
    Next
Multi5 = Left(Multi5, Len(Multi5) - 2)
End With
End Function

Excel 2010
	A	B
1	One number is 12345. And a second number is 54321.	12345, 54321
2	There is just 1 five digit number: 73638	73638
3	There is 1 five digit number (00293) but also 1 six digit number (038822), which should be ignored. Numbers might have leading zeros.	00293
4	12345, 54321, and 55555.....but not ABC123 or 123ABC.	12345, 54321, 55555
Sheet1

Cell Formulas
Range	Formula
B1	=multi5(A1)
B2	=multi5(A2)
B3	=multi5(A3)
B4	=multi5(A4)

 

[markmzz]

Hello,
I need to extract multiple 5-digit numbers from a cell that contains both letters and numbers. I found a thread that solves for how to extract a single number but this doesn't work for me because my data might contain multiple 5-digit numbers.
Here is an example of how my data looks (column A). I would like to extract the 5-digit numbers to column B.
I would like to have the 5-digit numbers in the same cell, separated by commas. Also, the data might contain numbers that are more or less than 5 digits - these should be ignored.
Thank you!

	Column A	Column B
1	One number is 12345. And a second number is 54321.	12345, 54321
2	There is just 1 five digit number: 73638	73638
3	There is 1 five digit number (00293) but also 1 six digit number (038822), which should be ignored. Numbers might have leading zeros.	00293
4	12345, 54321, and 55555.....but not ABC123 or 123ABC.	12345, 54321, 55555

<tbody>
</tbody>

Try this small modification in Pgc01's formula too (for 1 to 3 numbers):

=IFERROR(MID(A2,AGGREGATE(15,6,INDEX(ROW(INDIRECT("1:"&LEN(A2)))/(MMULT(1*(1*ISNUMBER(-MID(" "&A2,ROW(INDIRECT("1:"&LEN(A2)))+{0,1,2,3,4,5,6},1))={0,1,1,1,1,1,0}),{1;1;1;1;1;1;1})=7),),1),5),"")&
IFERROR(", "&MID(A2,AGGREGATE(15,6,INDEX(ROW(INDIRECT("1:"&LEN(A2)))/(MMULT(1*(1*ISNUMBER(-MID(" "&A2,ROW(INDIRECT("1:"&LEN(A2)))+{0,1,2,3,4,5,6},1))={0,1,1,1,1,1,0}),{1;1;1;1;1;1;1})=7),),2),5),"")&
IFERROR(", "&MID(A2,AGGREGATE(15,6,INDEX(ROW(INDIRECT("1:"&LEN(A2)))/(MMULT(1*(1*ISNUMBER(-MID(" "&A2,ROW(INDIRECT("1:"&LEN(A2)))+{0,1,2,3,4,5,6},1))={0,1,1,1,1,1,0}),{1;1;1;1;1;1;1})=7),),3),5),"")

Markmzz

 

[jkg77]

Thanks Scott! As an VBA newbie, I'm wondering if there's a formula solution?? (Or maybe it's easier if I just figure out how to implement your solution.)

 

[jkg77]

Thanks Markmzz - that definitely works for up to 3 numbers. I see how I can add more numbers, if needed, so this might work best for me.

Cheers!
Justin.

 

[Rick Rothstein]

Thanks Scott! As an VBA newbie, I'm wondering if there's a formula solution?? (Or maybe it's easier if I just figure out how to implement your solution.)

I know you have a rather lengthy formula solution, but if you should want to explore Scott's UDF solution, here is how you would implement it...

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the code Scott posted in Message #2 into the code window that just opened up. That's it.... you are done. You can now use Multi5 just like it was a built-in Excel function. For example,

=Multi5(A2)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[markmzz]

Thanks Markmzz - that definitely works for up to 3 numbers. I see how I can add more numbers, if needed, so this might work best for me.

Cheers!
Justin.

You are welcome and I'm glad to help.

Markmzz

 

[István Hirsch]

If you want to extract more than 3 numbers with a formula, give this a try. Enter the formula in C1 with Ctrl - Shift - Enter, then copy across and down. Then enter the formula in b1 and copy down. You get the list in B1.

Excel Workbook
	A	B	C	D	E	F	G
1	One number is 12345. And a second number is 54321 and 91333 or 56784 or 00856	91333, 56784, 54321, 12345, 00856	91333	56784	54321	12345	00856
2	There is just 1 five digit number: 73638	73638	73638
3	There is 1 five digit number (00293) but also 1 six digit number (038822), which should be ignored. Numbers might have leading zeros.	00293	00293
4	h12345, 54321, and 55555.....but not ABC123 or 123ABC.	55555, 54321, 12345	55555	54321	12345
5	erter h1234r 123456 oerte
Sheet3

 

[markmzz]

Hi!

If István's suggestion (with help columns) is possible for you, try this too:

In C2 and copy down and to the right

=IFERROR(MID($A2,AGGREGATE(15,6,INDEX(ROW(INDIRECT("1:"&LEN($A2)))/
(MMULT(1*(1*ISNUMBER(-MID(" "&$A2,ROW(INDIRECT("1:"&LEN($A2)))+{0,1,2,3,4,5,6},1))={0,1,1,1,1,1,0}),{1;1;1;1;1;1;1})=7),),COLUMNS($C2:C2)),5),"")

In B2 and copy down

​

=SUBSTITUTE(TRIM(C2&" "&D2&" "&E2&" "&F2&" "&G2&" "&H2&" "&I2&" "&J2)," ",", ")​

	A	B	C	D	E	F	G	H	I	J	K
1	Data	Result	HelpCol01	HelpCol02	HelpCol03	HelpCol04	HelpCol05	HelpCol06	HelpCol07	HelpCol08
2	10/12/2010.IBN.NTL.WebcastEBrochure.54695LVWCR_V2	54695	54695
3	10/12/2010.IBN.NTL.WebcastEBrochure.54695LVWCR_V2	54695	54695
4	10/13/2010.IBN.NTL.WebcastEBrochure.54713LVWCR_V2	54713	54713
5	4/20/2010.IBN.NTL.WebcastEBrochure.52511LVWCR_V1	52511	52511
6	1/19/2010.IBN.NTL.WebcastEBrochure.52509LVWCR_V1	52509	52509
7	57639ER_Drop1-110808-1335	57639	57639
8	57818ER_Drop1-110803-0646	57818	57818
9	9/14/2010.NTL.TeleBroc.55129T_V1_N	55129	55129
10	One number is 12345. And a second number is 54321.	12345, 54321	12345	54321
11	There is just 1 five digit number: 73638	73638	73638
12	Thereis 1 five digit number (00293) but also126345 1 six digit 123656 number (038822), which should be ignored. Numbers 12345 might 12345have 12345 leading zeros.12345 There12345 is 1 five digit 12345number (00293) but also1 six digit number (038822), which s	00293, 12345, 12345, 12345, 12345, 12345, 12345, 00293	00293	12345	12345	12345	12345	12345	12345	00293
13	12345, 54321, and 55555.....but not ABC123 or 123ABC.	12345, 54321, 55555	12345	54321	55555
14	tetetet tetette tetetteete tetetet 12345	12345	12345
15
***	*****************************************************	******************	*********	*********	*********	*********	*********	*********	*********	*********	**

<tbody>
</tbody>

Markmzz

 

[István Hirsch]

It seems my formula in post #8 does not extract the 5-digit number if it is the first substring of the string. Corrected version in cell C1:

=IFERROR(TEXT(LARGE(IF(NOT(ISNUMBER(--(MID(" "&$A1,ROW($1:$200),1))))*ISNUMBER(--(MID(" "&$A1,ROW($1:$200)+1,5)&"."))*NOT(ISNUMBER(--(MID(" "&$A1,ROW($1:$200)+6,1))))=1,--MID(" "&$A1,ROW($1:$200)+1,5),""),COLUMN()-2),"00000"),"")