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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
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,171,248
Messages
5,874,651
Members
433,064
Latest member
yankat22

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