# Extracting Data

#### baggarwal

##### Well-known Member
Hi All:

I have the following Table:
Book1
ABCD
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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
=LEFT(A1,FIND("Branch",A1,1)-2)

Try

=Left(A3,Search("Branch",A3)-2)

If "Branch" is always there :-

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

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"))

True, it is a duplicate post. However the OP apparently had trailing spaces. That is why I didn't go with the LEN idea.

Hi,

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

Hi,

=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.

Hi All:

Thanks so much for your help. How would I achieve the following desired results.
Book1
ABCD
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.

Replies
5
Views
175
Replies
2
Views
198
Replies
7
Views
135
Replies
1
Views
507
Legacy 143009
L
Replies
12
Views
319

1,217,756
Messages
6,138,425
Members
450,136
Latest member
Tabako1960

### 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.

### Which adblocker are you using?

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

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