Get word from a string
 

This is a record from bank statement:

WIRE TYPE:FX OUT DATE:081104 TIME:1701 ET TRN:2008103100323691 FX:GBP
92.83 1.624484 BNF:ZEN INTERNET LTD ID:29366585 BNF BK:NATWEST BANK
ID:SC010744 PMT DET:0000002005115610FROM JUPITERRESEARCH PRO-FORMA
NUMBER 6290280

I want to use Function or Code to get ZEN INTERNET LTD, that is the
words between BNF and ID. Beneficiary's name could be 2, words, 3
words, 4 words, so that there is no pattern.

How could I do this? Please advise. Thanks a lot!

H.Z..

Get word from a string
 

On Sat, 13 Dec 2008 11:07:20 -0800 (PST), Curious
wrote:

This is a record from bank statement:

WIRE TYPE:FX OUT DATE:081104 TIME:1701 ET TRN:2008103100323691 FX:GBP
92.83 1.624484 BNF:ZEN INTERNET LTD ID:29366585 BNF BK:NATWEST BANK
ID:SC010744 PMT DET:0000002005115610FROM JUPITERRESEARCH PRO-FORMA
NUMBER 6290280

I want to use Function or Code to get ZEN INTERNET LTD, that is the
words between BNF and ID. Beneficiary's name could be 2, words, 3
words, 4 words, so that there is no pattern.

How could I do this? Please advise. Thanks a lot!

H.Z..


If the record is in cell A1, you may try the following formula:

=MID(A1,FIND("BNF:",A1)+4,FIND("ID:",A1)-FIND("BNF:",A1)-5)

Hope this helps / Lars-Åke

Get word from a string
 

Try this
=MID(G17,FIND("BNF",G17)+4,FIND("ID",G17)-FIND("BNF",G17)-4)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Curious" wrote in message
...
This is a record from bank statement:

WIRE TYPE:FX OUT DATE:081104 TIME:1701 ET TRN:2008103100323691 FX:GBP
92.83 1.624484 BNF:ZEN INTERNET LTD ID:29366585 BNF BK:NATWEST BANK
ID:SC010744 PMT DET:0000002005115610FROM JUPITERRESEARCH PRO-FORMA
NUMBER 6290280

I want to use Function or Code to get ZEN INTERNET LTD, that is the
words between BNF and ID. Beneficiary's name could be 2, words, 3
words, 4 words, so that there is no pattern.

How could I do this? Please advise. Thanks a lot!

H.Z..

Get word from a string
 

Try this...

BeneficiaryName = Trim(Split(Split(BankRecordText, "BNF:")(1), "ID:")(0))

where BankRecordText is the text you posted.

--
Rick (MVP - Excel)


"Curious" wrote in message
...
This is a record from bank statement:

WIRE TYPE:FX OUT DATE:081104 TIME:1701 ET TRN:2008103100323691 FX:GBP
92.83 1.624484 BNF:ZEN INTERNET LTD ID:29366585 BNF BK:NATWEST BANK
ID:SC010744 PMT DET:0000002005115610FROM JUPITERRESEARCH PRO-FORMA
NUMBER 6290280

I want to use Function or Code to get ZEN INTERNET LTD, that is the
words between BNF and ID. Beneficiary's name could be 2, words, 3
words, 4 words, so that there is no pattern.

How could I do this? Please advise. Thanks a lot!

H.Z..

Get word from a string
 

If you are actually after a worksheet formula instead of code, you might
consider this one in addition to the others posted so far...

=TRIM(MID(LEFT(A1,FIND("ID:",A1)-1),FIND("BNF:",A1)+4,999))

The formula assumes no record will be longer than 999 characters (increase
the 999 if it will).

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Try this...

BeneficiaryName = Trim(Split(Split(BankRecordText, "BNF:")(1), "ID:")(0))

where BankRecordText is the text you posted.

--
Rick (MVP - Excel)


"Curious" wrote in message
...
This is a record from bank statement:

WIRE TYPE:FX OUT DATE:081104 TIME:1701 ET TRN:2008103100323691 FX:GBP
92.83 1.624484 BNF:ZEN INTERNET LTD ID:29366585 BNF BK:NATWEST BANK
ID:SC010744 PMT DET:0000002005115610FROM JUPITERRESEARCH PRO-FORMA
NUMBER 6290280

I want to use Function or Code to get ZEN INTERNET LTD, that is the
words between BNF and ID. Beneficiary's name could be 2, words, 3
words, 4 words, so that there is no pattern.

How could I do this? Please advise. Thanks a lot!

H.Z..

Get word from a string
 

Or, a UDF
=gsm(G17,"BNF:","ID:")

Function gsm(rng, FT, ST)
gsm = Trim(Split(Split(rng, FT)(1), ST)(0))
End Function

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rick Rothstein" wrote in message
...
Try this...

BeneficiaryName = Trim(Split(Split(BankRecordText, "BNF:")(1), "ID:")(0))

where BankRecordText is the text you posted.

--
Rick (MVP - Excel)


"Curious" wrote in message
...
This is a record from bank statement:

WIRE TYPE:FX OUT DATE:081104 TIME:1701 ET TRN:2008103100323691 FX:GBP
92.83 1.624484 BNF:ZEN INTERNET LTD ID:29366585 BNF BK:NATWEST BANK
ID:SC010744 PMT DET:0000002005115610FROM JUPITERRESEARCH PRO-FORMA
NUMBER 6290280

I want to use Function or Code to get ZEN INTERNET LTD, that is the
words between BNF and ID. Beneficiary's name could be 2, words, 3
words, 4 words, so that there is no pattern.

How could I do this? Please advise. Thanks a lot!

H.Z..