Find specific Numbers in a cell with a lot of text

[luca.pe]

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!

[Glenn Kennedy]

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet with REPRESENTATIVE data, an explanation and some expected results.

[Glenn Kennedy]

Never mind... try this:

=--LOOKUP(2,1/(MMULT(0+(ISNUMBER(-MID(" "&E2,ROW(INDIRECT("1:"&LEN(E2)))+{0,1,2,3,4,5,6,7,8,9,10,11,12,13,14},1))+0={0,1,1,1,1,1,1,1,1,1,1,1,1,1,0}),{1;1;1;1;1;1;1;1;1;1;1;1;1;1;1})=15),MID(E2,ROW(INDIRECT("1:"&LEN(E2))),13))

Check it out IN THE FILE... not by copy/pasting the formula from here.

[menem]

If you don't mind to use array formula.

If data in E6
plase try this formula (follow with ctrl-shift-enter for array formula)
=MAX(IFERROR(VALUE(MID(E6,ROW($A$1:$A$300),13)),0))

Regards.

[Glenn Kennedy]

Hahaha. Much simpler!!!!

[menem]

Thanks, Glenn.

But I've got a question, if op want to get more than 15 digits of number, how to solve them?


Regards.

If I could use TEXTJOIN , I could use this formula .... T_T

Data in E11 and follow with Ctrl-shift-enter
=MID(E11,FIND(REPT("1",16),textjoin("",TRUE,IFERROR(IF(VALUE(MID(E11,ROW($A$1:$A$300),1))>=0,"1")," "))),16)

[luca.pe]

Hi Glenn and hi menem,

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!
Sample.png

[AliGW]

What happened when you tried this?

=MAX(IFERROR(VALUE(MID(B3,ROW($A$1:$A$300),12)),0))

Please get into the habit of providing workbooks instead of screenshots. Thanks.

[Glenn Kennedy]

If Ali's adaptation of menem's formula does NOT work... upload a FILE not a non-editable PICTURE of one.

[luca.pe]

Many thanks for your help,

I have attached a small sample, if it is too small I can attach a larger one.

Many thanks and best regards!

[AliGW]

AliGW on MS365 Insider (Windows) 64 bit

=MAX(IFERROR(VALUE(MID(A3,ROW($A$1:$A$300),12)),0))

	A	B
1
2
3	Order: 4700270618, Projekt 149471-160923000013	160923000013
4	Service and Maitance: Projekt 337611-180419001111	180419001111
5	171962 - 200924000018 / Indoor LUKS Building 11	200924000018

Sheet: Tabelle1

[Glenn Kennedy]

luca. don't forget that the formula is an array formula and needs CTRL-HIFT-ENTER

[AliGW]

That's true.

[luca.pe]

Many thanks!

A follow-up question:

What if instead a 0 or just 4 numbers I want the cell to be empty, how would I have to adapt the formula?
Also in my case I have 3 cells (attached the example), I would then at the end put in a cell where it looks for the 3 cells with the formula and just gives me the number in one cell.
In the example cell G (final) VLOOKUP ?

many thanks!!

[menem]

Please provide your desire result in G column (all rows).

Regards.

[Glenn Kennedy]

A guess, based on menem's solution (and on my monster...).

See file.

[luca.pe]

Hi Glenn,

Awesome, many thanks!
Could you help me with the formulas if instead a 12-digit number, I am looking for a 6 digit number?

BR

[AliGW]

Maybe:

=IF(LEN(MAX(IFERROR(VALUE(MID(A3&C3&E3,ROW($A$1:$A$300),12)),0)))<12,"",MAX(IFERROR(VALUE(MID(A3&C3&E3,ROW($A$1:$A$300),12)),0)))

=IF(LEN(MAX(IFERROR(VALUE(MID(A3&C3&E3,ROW($A$1:$A$300),6)),0)))<6,"",MAX(IFERROR(VALUE(MID(A3&C3&E3,ROW($A$1:$A$300),6)),0)))

[Glenn Kennedy]

See file.

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.

[luca.pe]

Hi Glenn,

You helpmed a while back with finding a 6 digit code.
Now I want to find a 4 digit code and only in 1 cell - i tried to adjust your formula but just cant get it to work. Could you help me out?

got to this:


many thanks and best regards!

[luca.pe]

Dear Glenn,


I just tried it again like this: =WENNFEHLER(--VERWEIS(2;1/(MMULT(0+(ISTZAHL(-TEIL(" "&H9;ZEILE(INDIREKT("1:"&LÄNGE(H9)))+{0\1\2\3\4\5};1))+0={0\1\1\1\1\1\1\0});{1;1;1;1;1;1})=6);TEIL(H9;ZEILE(INDIREKT("1:"&LÄNGE(H9)));4));"")

Can you spot the error, I am making?
Many thanks and best regards!

[AliGW]

Attac a workbook!!!

[luca.pe]

see attachment

My thinking was to take Glenn's Formula, that worked for the 6 digit codes and adjust it to 4 digits.

BR