Extracting Data

[baggarwal]

Hi All:

I have the following Table:

Book1
	A	B	C	D
2	Data	Desired
3	NorthLosAngelesBranch	NorthLosAngeles
4	TorontoBranch	Toronto
5	SanAntoniaBranch	SanAntonio
6	DesMoinesBranch	DesMoines
Sheet1

Can someone please show me a formula that will extract all the data before branch. The data is in the first column. I am looking for a formula to place in the desired and drag it down.

Thanks so much

 

[Aladin Akyurek]

=LEFT(A2,SEARCH("@",SUBSTITUTE(A2&" "," ","@",2))-1)

But, why don't you keep the input as is?

 

[baggarwal]

Aladin this worked but can you please explain how this formula works or how you waved your magic wand?

 

[baggarwal]

Hi Aladin:

Can the formula be modified to handle an instance like Los Angeles Sales Rep. Here is the table

Book1
	A	B	C	D
2	Data	Desired
3	TorontoSalesRe	TorontoSales
4	TorontoSales	TorontoSales
5	TorontoSalesRep	TorontoSales
6	TorontoSalesR	TorontoSales
7	TorontoCSR	TorontoCSR
8	TorontoCSRE	TorontoCSR
9	TorontoCSRSa	TorontoCSR
10	LosAngelesSalesRep	LosAngelesSales
Sheet1

I noticed that that created an error.

 

[baggarwal]

Formula help

Can anyone else help with this request

 

[Aladin Akyurek]

Re: Formula help

Can anyone else help with this request

=LEFT(A2,SEARCH("@",SUBSTITUTE(A2&" "," ","@",MAX(2,LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))-1)

 

[baggarwal]

Hi Aladdin:

Thanks so much for your help. For some reason though the formula does not work for Los Angeles CSR when I drag it down it only gives me the Los Angeles part.

I tried my best to figure it out but gave up. What is the modification to the formula required to get this to work as well.

Thanks again

 

[NBVC]

Not as elegant perhaps, but if you've always got Sales or CSR in the text string, then:

=IF(ISNUMBER(SEARCH("Sales",A2)),LEFT(A2,SEARCH("Sales",A2)+4),LEFT(A2,SEARCH("CSR",A2)+2))

Book1
	A	B	C	D
1	Data
2	Toronto Sales Re	Toronto Sales
3	Toronto Salesx	Toronto Sales
4	Toronto Sales Rep	Toronto Sales
5	Toronto Sales R	Toronto Sales
6	Toronto CSR	Toronto CSR
7	Toronto CSRE	Toronto CSR
8	Toronto CSR Sa	Toronto CSR
9	Los Angeles Sales Rep	Los Angeles Sales
10	Los Angeles CSR	Los Angeles CSR
Sheet1

 

[baggarwal]

This is amazing thankyou. Can you explain how the formula works?

 

[NBVC]

This is amazing thankyou. Can you explain how the formula works?

You're welcome.

The Isnumber(Search("Sales",A2)) part checks to see if "Sales" appears in the formula....Search() returns a position so the Isnumber() returns TRUE if a position is found.

Having found an instance of "Sales" in the string, then the TRUE condition of the IF() formula is evaluated. I.e. LEFT(A2,SEARCH("Sales",A2)+4). This takes the Left part of the string in A2 up to and including the word "Sales".

If the IF() condition returns FALSE, it is assumed then the string contains "CSR" instead of "SALES" and therefore returns the string up to and including the "CSR" string.

 

[baggarwal]

Thankyou so much for your help.