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*


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

i think it's kind of hard, because there is another double zero in your
text.

is it always preceded by a "C"?

if so, if your data is in a1, put this in b1

=MID(A1,FIND("C00",A1,1)+1,10)

--


Gary


"spectator" wrote in
message ...

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*


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

spectator,
You're looking for "00"+8digits from:
FED IMAD:0726B1QGC02C0003730726

or

05/07/25 0012345678

NickHK

"spectator" wrote in
message ...

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*


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

Is the multi-line affect shown in the example the result of wordwrap or are
the separate lines created by vbCrLf ? In other words, can we rely on the ten
digit number that starts with "00" to be at the beginning of a new line
within the cell? And/or can we rely on it following a date as per the example?

Greg



"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*


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

and to nickhk i am trying to get the second one. Thanks!


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

this would give you the 10 digit number after the word details:
=MID(A1,FIND("00",A1,FIND("DETAILS",A1,1)),10)


is there any thing that's always the same in these strings?
--


Gary


"spectator" wrote
in message ...

and to nickhk i am trying to get the second one. Thanks!


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

spectator,
If there is a space before
=MID(A1,FIND(" 00",A1,1)+1,10)
or for a line break (or vbCr or vbCrLf, depending on the source of the data)
=MID(A1,FIND(CHAR(10) & "00",A1,1)+1,10)

NickHK

"spectator" wrote
in message ...

and to nickhk i am trying to get the second one. Thanks!


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