a function required

[JIM.H.]

Hello,
8/26/2004 3:10:10 AM: MyNOTE[ 286571] - TBS QTY:[1] +
TSV QTY: [1] RESULT: [2] - EXP TSV QTY: [1]
8/26/2004 3:10:10 AM: MyNOTE[ 286571] - TBS QTY:[10] +
TSV QTY: [1] RESULT: [2] - EXP TSV QTY: [1]
8/26/2004 3:10:10 AM: MyNOTE[ 286571] - TBS QTY:[100] +
TSV QTY: [1] RESULT: [2] - EXP TSV QTY: [1]

Column A has this kind of rows, I want to get TBS QTY
values (1, 10, and 100) in a cell, how can I do this?
Thanks,
Jim.

 

[Bernie Deitrick]

Jim,

For one of those strings in cell A1:

=MID(A1,FIND("TBS QTY:[",A1) +LEN("TBS QTY:["),FIND("] + TSV",A1) -FIND("TBS
QTY:[",A1) -LEN("TBS QTY:["))

All on one line (watch the line breaks), and copied down to match your data.

HTH,
Bernie
MS Excel MVP

 

[JIM.H.]

Thanks Bernie,

That was A1 row. I copy-pate your formula to B1 and it
worked fine. Now I have this in A2:
8/26/2004 3:10:10 AM: --- ["003","000004210009",100,1,]
If B1 has a number (coming from A1) I need to get 100
(might be 1, 10) in this rows and put it C1 (so this is
coming from A2), so that I can compare B1 with C1.
Thanks,
Jim.

-----Original Message-----
Jim,

For one of those strings in cell A1:

=MID(A1,FIND("TBS QTY:[",A1) +LEN("TBS QTY:["),FIND("] + TSV",A1) -FIND("TBS
QTY:[",A1) -LEN("TBS QTY:["))

All on one line (watch the line breaks), and copied down to match your data.

HTH,
Bernie
MS Excel MVP

Hello,
8/26/2004 3:10:10 AM: MyNOTE[ 286571] - TBS QTY:[1] +
TSV QTY: [1] RESULT: [2] - EXP TSV QTY: [1]
8/26/2004 3:10:10 AM: MyNOTE[ 286571] - TBS QTY:[10] +
TSV QTY: [1] RESULT: [2] - EXP TSV QTY: [1]
8/26/2004 3:10:10 AM: MyNOTE[ 286571] - TBS QTY: [100] +
TSV QTY: [1] RESULT: [2] - EXP TSV QTY: [1]

Column A has this kind of rows, I want to get TBS QTY
values (1, 10, and 100) in a cell, how can I do this?
Thanks,
Jim.

.

 

[Ron Rosenfeld]

Hello,
8/26/2004 3:10:10 AM: MyNOTE[ 286571] - TBS QTY:[1] +
TSV QTY: [1] RESULT: [2] - EXP TSV QTY: [1]
8/26/2004 3:10:10 AM: MyNOTE[ 286571] - TBS QTY:[10] +
TSV QTY: [1] RESULT: [2] - EXP TSV QTY: [1]
8/26/2004 3:10:10 AM: MyNOTE[ 286571] - TBS QTY:[100] +
TSV QTY: [1] RESULT: [2] - EXP TSV QTY: [1]

Column A has this kind of rows, I want to get TBS QTY
values (1, 10, and 100) in a cell, how can I do this?
Thanks,
Jim.

=LEFT(MID(A1,FIND("TBS",A1)+9,255),-1+
FIND("]",MID(A1,FIND("TBS",A1)+9,255)))

or, if you want to convert it to a number:

=--LEFT(MID(A1,FIND("TBS",A1)+9,255),-1+
FIND("]",MID(A1,FIND("TBS",A1)+9,255)))


--ron