Extracting Data

baggarwal

Well-known Member
Joined
Jul 10, 2002
Messages
591
Hi All:

I have the following Table:
Book1
ABCD
2DataDesired
3NorthLosAngelesBranchNorthLosAngeles
4TorontoBranchToronto
5SanAntoniaBranchSanAntonio
6DesMoinesBranchDesMoines
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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
If the word Branch is always there:

=LEFT(A3,LEN(A3)-7)

If Branch is not always there, perhaps:

=LEFT(A3,SEARCH(" branch",A3&" branch"))
 
Upvote 0
True, it is a duplicate post. However the OP apparently had trailing spaces. That is why I didn't go with the LEN idea.
 
Upvote 0
Hi,

How about

=SUBSTITUTE(A2," Branch","")

I still think that using the LEFT function is a better approach. Reading the OPs other post leads me to believe that they are importing this information. Using SUBSTITUTE would still leave trailing spaces. This could cause problems when using a function like VLOOKUP. Perhaps =TRIM(SUBSTITUTE(A2," Branch","")) would be best if the word Branch is always a part of the string.
 
Upvote 0
Hi All:

Thanks so much for your help. How would I achieve the following desired results.
Book1
ABCD
2DataDesired
3TorontoSalesReTorontoSales
4TorontoSalesTorontoSales
5TorontoSalesRepTorontoSales
6TorontoSalesRTorontoSales
7TorontoCSRTorontoCSR
8TorontoCSRETorontoCSR
9TorontoCSRSaTorontoCSR
Sheet1


I have data that comes in as Toronto Sales RE, Toronto Sales R, I just want to convert them all into Toronto Sales in one shot. Can someone please tell me how this can be done.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top