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

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

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

one way

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"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

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

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

"Dinesh" wrote in message
...
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