need help fast!

[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

[Gary Keramidas]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[NickHK]

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

or

05/07/25 0012345678

NickHK

"spectator" <[email protected]> wrote in
message news:[email protected]...
>
> 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]

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

[Greg Wilson]

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

[Gary Keramidas]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[NickHK]

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>