extract cusotmer name from test string

[Dinesh]

I have long string of text that start with customer name. Immediately after
the text string, it populates either the schedule # (which start with "sch")
or master lease agreement # (start with "MLA"). I want to exract only the
customer name from it. What will be my formula? Below is a two typical
example.

A) ABC corp MLA 12667 MMA50-014-0 BSC-73081
B) Black and Decker SCH 303A4 SCH 3A61-COR BSC-15701

Thanks,
Dinesh

 

[Sean Timmons]

May want to use something like this:

=IF(ISERROR(FIND(" MLA ",A1)),LEFT(A1,FIND(" SCH ",A1)-1),LEFT(A1,FIND(" MLA
",A1)-1))

I entered space before and after in the find string to avoid capturing
companies that may have MLA or SCH within the name...

 

[Dinesh]

I did come up with "#value" error if no "MLA" or "SCH" present in the text. I
thought "iserror" would take care of it.

 

[Don Guillett]

one way

=IF(ISERR(SEARCH("mla",H1)),"",LEFT(H1,SEARCH("mla",H1)+2))

 

[Rick Rothstein]

Give this formula a try instead...

=LEFT(A1,SEARCH(" SCH "," "&SUBSTITUTE(UPPER(A1),"MLA","SCH")&" SCH ")-1)

 

[Ashish Mathur]

Hi,

Use this formula and copy down. B14 holds the text string.

=LEFT(B14,MIN(SEARCH({"MLA","SCH"},B14&"MLASCH"))-1)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com