Give this formula a try instead...

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

--
Rick (MVP - Excel)


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

"Sean Timmons" wrote:

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" wrote:

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