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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

babycody

Well-known Member
Joined
Jul 8, 2003
Messages
1,395

ADVERTISEMENT

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

babycody

Well-known Member
Joined
Jul 8, 2003
Messages
1,395
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.
 

baggarwal

Well-known Member
Joined
Jul 10, 2002
Messages
591
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.
 

Forum statistics

Threads
1,136,261
Messages
5,674,705
Members
419,520
Latest member
Jennifer4Dillon

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
Top