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!
<bWIRE 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


--
spectator
------------------------------------------------------------------------
spectator's Profile: http://www.excelforum.com/member.php...o&userid=25635
View this thread: http://www.excelforum.com/showthread...hreadid=390453

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.


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

--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=390453

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!


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

--
RarrMike
------------------------------------------------------------------------
RarrMike's Profile: http://www.excelforum.com/member.php...o&userid=25637
View this thread: http://www.excelforum.com/showthread...hreadid=390453

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


--
spectator
------------------------------------------------------------------------
spectator's Profile: http://www.excelforum.com/member.php...o&userid=25635
View this thread: http://www.excelforum.com/showthread...hreadid=390453

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

spectator Wrote:
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*


--
Piranha
------------------------------------------------------------------------
Piranha's Profile: http://www.excelforum.com/member.php...o&userid=20435
View this thread: http://www.excelforum.com/showthread...hreadid=390453