Need help fast! Thanks!

S

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!
<b>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</b>
 
B

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 int
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 i
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 th
small + sign to the bottom row of your data to copy all (B1 to H1 etc
formula

+-------------------------------------------------------------------
|Filename: FindNum.zip
|Download: http://www.excelforum.com/attachment.php?postid=3642
+-------------------------------------------------------------------
 
R

RarrMike

Since it is always a 10 digit number, it should start with 00 and en
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 o
numbers, there is no way to distinuish it. I have included
spreadsheet with the example for you. Let me know if this works

+-------------------------------------------------------------------
|Filename: findnumber.zip
|Download: http://www.excelforum.com/attachment.php?postid=3645
+-------------------------------------------------------------------
 
Top