Find specific codes in a large excel in cells with a lot of surrounding text

[luuccaapee]

Hello

I have a fairly large excel about 1000 rows. The data source is unfortunately not very clean, meaning it was put in as free text. I need to find very specific 13 digit codes in one of the Columns for example in E: hsjjdalfiw 1234567891234 msbdjwkal. The problem is the number is not always at the same place, or the text around it is not always the same. Sometimes there is not even space around the number for example hfjdl-1234567891234, and of course the codes are always different numbers.

Other examples:

13th street,TX 1349203940123 MR Smith
Mr Johnson, 1349203940123-BE-789, empresa JXD
1349203940123, sdmeSD 6789 sdsd

Is there any formula or any useful trick to extract only these 13 digit codes, so I dont have to do it all manually?

Many thanks!

 

[FormR]

Hi, welcome to the forum!

Here is one possible option you can try:

=TEXTJOIN(",",1,IFERROR(0+MID(SUBSTITUTE(A1," ","|"),SEQUENCE(LEN(A1)-12),13),""))

 

[bebo021999]

is it?

Nhờ giúp..xlsm
	E	F
1	13th street,TX 1349203940123 MR Smith	1349203940123
2	Mr Johnson, 1349203940123-BE-789, empresa JXD	1349203940123
3	1349203940123, sdmeSD 6789 sdsd	1349203940123
4	hsjjdalfiw 1234567891234 msbdjwkal	1234567891234
5	hfjdl-1234567891234	1234567891234
Sheet4

Cell Formulas
Range	Formula
F1:F5	F1	=AGGREGATE(14,6,MID(E1,ROW(INDIRECT("1:"& LEN(E1)-12)),13)+0,1)

 

[Peter_SSs]

.. or avoiding the volatile function INDIRECT, you could try ..

22 04 18.xlsm
	E	F
1	13th street,TX 1349203940123 MR Smith	1349203940123
2	Mr Johnson, 1349203940123-BE-789, empresa JXD	1349203940123
3	1349203940123, sdmeSD 6789 sdsd	1349203940123
4	hsjjdalfiw 1234567891234 msbdjwkal	1234567891234
5	hfjdl-1234567891234	1234567891234
Extract 13 digit Num

Cell Formulas
Range	Formula
F1:F5	F1	=AGGREGATE(14,6,MID(E1,SEQUENCE(LEN(E1)-12),13)+0,1)

 

[luuccaapee]

.. or avoiding the volatile function INDIRECT, you could try ..

22 04 18.xlsm
	E	F
1	13th street,TX 1349203940123 MR Smith	1349203940123
2	Mr Johnson, 1349203940123-BE-789, empresa JXD	1349203940123
3	1349203940123, sdmeSD 6789 sdsd	1349203940123
4	hsjjdalfiw 1234567891234 msbdjwkal	1234567891234
5	hfjdl-1234567891234	1234567891234
Extract 13 digit Num

Cell Formulas
Range	Formula
F1:F5	F1	=AGGREGATE(14,6,MID(E1,SEQUENCE(LEN(E1)-12),13)+0,1)

Dear Peter,
many thanks for your answer!
I was mistaken and its 12 numbers and not 13. When I copy your formula, I always get the error message. Should I just subtract 1 number in your formula to change it to 12 numbers?

I have attached a small sample.

Again many thanks for your help and best regards!

 

[Peter_SSs]

Should I just subtract 1 number in your formula to change it to 12 numbers?

You need to change two numbers in the formula

=AGGREGATE(14,6,MID(A3,SEQUENCE(LEN(A3)-11),12)+0,1)