|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
June 22nd, 2009, 10:50 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
extract cusotmer name from test string
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 
|
|
#2
June 22nd, 2009, 11:02 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
extract cusotmer name from test string
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
|
|
#3
June 22nd, 2009, 11:19 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
extract cusotmer name from test string
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
|
|
#5
June 23rd, 2009, 01:00 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
extract cusotmer name from test string
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
|
|
#6
June 23rd, 2009, 01:27 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
extract cusotmer name from test string
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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
