Modify search function

[Dinesh]

Hi,

I have following formula that needs to be modified.

=IF(ISERROR(SEARCH("BOFA",V98)),"0",MID(V98,SEARCH("- ",V98)+1,99)+0)

Seconf search criteria which is "- " can be either "- " or "for". How can i
incorporate the second criteria as "for"?

Thanks,
Dinesh

 

[Dinesh]

Actually I want to modify the whole text search as following. Disregard the
earlier question.
Below is a text string. I want to extract the $ value from it. Text string
must have “BOFA†name on it.
Here it’s get complicated. Precedent to a $ value, there are different type
of text string. In the example below is “- â€, in addition to that some time
it can be “Inc†and “for “as well.
Here is my formula which has a “- “text string and works perfectly. Also
can you explain the function of +1, 99)+0) in the formula.
=IF(ISERROR(SEARCH("BOFA",D1)),"0",MID(D1,SEARCH("- ",D1)+1,99)+0)

Text string
Wireless Data Services MLA 6733 SCH 002-000 CSC-71745 Proceeds to BOFA for -
621063.11

 

[Dinesh]

Actually I want to modify the whole text search as following. Disregard the
earlier question.
Below is a text string. I want to extract the $ value from it. Text string
must have “BOFA†name on it.
Here it’s get complicated. Precedent to a $ value, there are different type
of text string. In the example below is “- â€, in addition to that some time
it can be “Inc†and “for “as well.
Here is my formula which has a “- “text string and works perfectly. Also
can you explain the function of +1, 99)+0) in the formula.
=IF(ISERROR(SEARCH("BOFA",D1)),"0",MID(D1,SEARCH("- ",D1)+1,99)+0)

test string
Wireless Data Services MLA 6733 SCH 002-000 CSC-71745 Proceeds to BOFA for -
621063.11

 

[Ashish Mathur]

Hi,

Use this formula

=IF(ISERROR(MID(C18,SEARCH({"-","inc","for"},C18,SEARCH("BOFA",C18,1))+1,50)*1),"",MID(C18,SEARCH({"-","inc","for"},C18,SEARCH("BOFA",C18,1))+1,50)*1)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com