Need help fast! Thanks!

[spectator]

A large spread sheet contains one column that has the following info in each cell. Each block of text contains a ten digit number that begins with 00 and appears in a different place in the text. I need to write a formula that will consistently pull the ten digit number to a new cell. Any suggestions or help on an answer? thanks! Here is example!
WIRE TYPE:WIRE IN DATE:072605 TIME:0740 CDT
TRN:050726010169 FEDREF/SEQ:3517452206JO/000373
RELATED REF:CAP OF 05/07/25 FED IMAD:0726B1QGC02C0003730726
ORIG:LAUNDRY SERVICES ID:2215462
SND BK:JPMORGAN CHASE BANK ID:0210423221
BNF:CLEAN CUT IRONING SERVICES ID:007654321
PAYMENT DETAILS: CAP OF 05/07/25
0012345678 - -SUSAN SMITH

[Bryan Hessey]

Your problem is that you have no identifying feature, the code "00" appears in other places also.

Try the attached sheet, and duplicate pairs of columns E-F, G-H into I-J, K-L etc until you get a full set of numbers in the last column

It may not be perfect, it relies on adding +1 to error out, and if other has 10 characters with 00 it will pass this test.

Hope this helps



ps, I presume that you know, having copied my formula to B1, C1 ~~~ G1, H1 of your worksheet, that you can select B1 to H1, and formula-drag the small + sign to the bottom row of your data to copy all (B1 to H1 etc) formula.

[RarrMike]

Since it is always a 10 digit number, it should start with 00 and end with a space after it, therefore you would use the function

=MID(A1,SEARCH("00?????????? ",A1,1),10)

If the number is random and could be contained with a bigger set of numbers, there is no way to distinuish it. I have included a spreadsheet with the example for you. Let me know if this works!

[spectator]

Thanks for all the help! keep suggestions coming if any! thank u!

[Piranha]

Spectator,
You have info in the two other forums where you multiposted.
Dave

A large spread sheet contains one column that has the following info in each cell. Each block of text contains a ten digit number that begins with 00 and appears in a different place in the text. I need to write a formula that will consistently pull the ten digit number to a new cell. Any suggestions or help on an answer? thanks! Here is example!
WIRE TYPE:WIRE IN DATE:072605 TIME:0740 CDT
TRN:050726010169 FEDREF/SEQ:3517452206JO/000373
RELATED REF:CAP OF 05/07/25 FED IMAD:0726B1QGC02C0003730726
ORIG:LAUNDRY SERVICES ID:2215462
SND BK:JPMORGAN CHASE BANK ID:0210423221
BNF:CLEAN CUT IRONING SERVICES ID:007654321
PAYMENT DETAILS: CAP OF 05/07/25
0012345678 - -SUSAN SMITH