Extract multiple 5-digit numbers from alphanumeric text

J

jkg77

New Member
Joined
Jun 9, 2017
Messages
5
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!

[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD][/TD]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]One number is 12345. And a second number is 54321.[/TD]
[TD]12345, 54321[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]There is just 1 five digit number: 73638[/TD]
[TD]73638[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]There is 1 five digit number (00293) but also 1 six digit number (038822), which should be ignored. Numbers might have leading zeros.[/TD]
[TD]00293[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]12345, 54321, and 55555.....but not ABC123 or 123ABC.[/TD]
[TD]12345, 54321, 55555[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
This UDF should do that:

Code: 
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
AB
1One number is 12345. And a second number is 54321.12345, 54321
2There is just 1 five digit number: 7363873638
3There is 1 five digit number (00293) but also 1 six digit number (038822), which should be ignored. Numbers might have leading zeros.00293
412345, 54321, and 55555.....but not ABC123 or 123ABC.12345, 54321, 55555
Sheet1
Cell Formulas
RangeFormula
B1=multi5(A1)
B2=multi5(A2)
B3=multi5(A3)
B4=multi5(A4)
 
Upvote 0
jkg77 said:
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!
[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD][/TD]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]One number is 12345. And a second number is 54321.[/TD]
[TD]12345, 54321[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]There is just 1 five digit number: 73638[/TD]
[TD]73638[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]There is 1 five digit number (00293) but also 1 six digit number (038822), which should be ignored. Numbers might have leading zeros.[/TD]
[TD]00293[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]12345, 54321, and 55555.....but not ABC123 or 123ABC.[/TD]
[TD]12345, 54321, 55555[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

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
 
Last edited:
Upvote 0
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.)
 
Upvote 0
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.
 
Upvote 0
jkg77 said:
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.)
Click to expand...
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.
 
Last edited:
Upvote 0
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
ABCDEFG
1One number is 12345. And a second number is 54321 and 91333 or 56784 or 0085691333, 56784, 54321, 12345, 008569133356784543211234500856
2There is just 1 five digit number: 736387363873638
3There is 1 five digit number (00293) but also 1 six digit number (038822), which should be ignored. Numbers might have leading zeros.0029300293
4h12345, 54321, and 55555.....but not ABC123 or 123ABC.55555, 54321, 12345555555432112345
5erter h1234r 123456 oerte
Sheet3
 
Upvote 0
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)," ",", ")​


[TABLE="class: grid, width: 1128"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Data[/TD]
[TD]Result[/TD]
[TD]HelpCol01[/TD]
[TD]HelpCol02[/TD]
[TD]HelpCol03[/TD]
[TD]HelpCol04[/TD]
[TD]HelpCol05[/TD]
[TD]HelpCol06[/TD]
[TD]HelpCol07[/TD]
[TD]HelpCol08[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]10/12/2010.IBN.NTL.WebcastEBrochure.54695LVWCR_V2[/TD]
[TD]54695[/TD]
[TD]54695[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]10/12/2010.IBN.NTL.WebcastEBrochure.54695LVWCR_V2[/TD]
[TD]54695[/TD]
[TD]54695[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]10/13/2010.IBN.NTL.WebcastEBrochure.54713LVWCR_V2[/TD]
[TD]54713[/TD]
[TD]54713[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4/20/2010.IBN.NTL.WebcastEBrochure.52511LVWCR_V1[/TD]
[TD]52511[/TD]
[TD]52511[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]1/19/2010.IBN.NTL.WebcastEBrochure.52509LVWCR_V1[/TD]
[TD]52509[/TD]
[TD]52509[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]57639ER_Drop1-110808-1335[/TD]
[TD]57639[/TD]
[TD]57639[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]57818ER_Drop1-110803-0646[/TD]
[TD]57818[/TD]
[TD]57818[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]9/14/2010.NTL.TeleBroc.55129T_V1_N[/TD]
[TD]55129[/TD]
[TD]55129[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]One number is 12345. And a second number is 54321.[/TD]
[TD]12345, 54321[/TD]
[TD]12345[/TD]
[TD]54321[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]There is just 1 five digit number: 73638[/TD]
[TD]73638[/TD]
[TD]73638[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]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[/TD]
[TD]00293, 12345, 12345, 12345, 12345, 12345, 12345, 00293[/TD]
[TD]00293[/TD]
[TD]12345[/TD]
[TD]12345[/TD]
[TD]12345[/TD]
[TD]12345[/TD]
[TD]12345[/TD]
[TD]12345[/TD]
[TD]00293[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]12345, 54321, and 55555.....but not ABC123 or 123ABC.[/TD]
[TD]12345, 54321, 55555[/TD]
[TD]12345[/TD]
[TD]54321[/TD]
[TD]55555[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]tetetet tetette tetetteete tetetet 12345[/TD]
[TD]12345[/TD]
[TD]12345[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]*****************************************************[/TD]
[TD]******************[/TD]
[TD]*********[/TD]
[TD]*********[/TD]
[TD]*********[/TD]
[TD]*********[/TD]
[TD]*********[/TD]
[TD]*********[/TD]
[TD]*********[/TD]
[TD]*********[/TD]
[TD]**[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz
 
Upvote 0
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"),"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Extract all 5 character alphanumerics in a string that where the 1st char is a letter, and the 2nd char is a number
2
Replies
14
Views
583
Cubist
Cubist
C
  • Solved
Extract 8 character number from text string
2
Replies
15
Views
740
Fluff
Fluff
E
  • Solved
Formula for extracting multiple occurrences of text substring in a cell
2
Replies
19
Views
1K
Sektor
Sektor
V
  • Question Question
Extract First Digit from Alphanumeric Number
Replies
7
Views
1K
alansidman
A
L
  • Solved
Return column number from a table of data
Replies
3
Views
125
Fluff
Fluff

Forum statistics

Threads
1,223,886
Messages
6,175,196
Members
452,616
Latest member
intern444

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top