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!

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

 

[markmzz]

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"),"")

Hi István,

Maybe I'm wrong, but I think that your formula still have problem.

Try this small modification in your formula:

=IFERROR(TEXT(LARGE(IF(NOT(ISNUMBER(-(MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1))))*ISNUMBER(-(MID($A2&" ",ROW(INDIRECT("1:"&LEN($A2)))+1,5)&","))*
NOT(ISNUMBER(-(MID($A2,ROW(INDIRECT("1:"&LEN($A2)))+6,1)))),1*MID($A2,ROW(INDIRECT("1:"&LEN($A2)))+1,5),""),COLUMNS($C1:C1)),"00000"),"")

Markmzz

 

[István Hirsch]

Hi István,

Maybe I'm wrong, but I think that your formula still have problem.

Try this small modification in your formula:

=IFERROR(TEXT(LARGE(IF(NOT(ISNUMBER(-(MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1))))*ISNUMBER(-(MID($A2&" ",ROW(INDIRECT("1:"&LEN($A2)))+1,5)&","))*
NOT(ISNUMBER(-(MID($A2,ROW(INDIRECT("1:"&LEN($A2)))+6,1)))),1*MID($A2,ROW(INDIRECT("1:"&LEN($A2)))+1,5),""),COLUMNS($C1:C1)),"00000"),"")

Markmzz

Thanks, this is my corrected version:

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