How do I split cells?

The attached spreadsheet displays product information.

1.Column D needs to be split so the first word is also in column C. (See example in red).

2.This could be tricky! Collumn 'AM' displays product categories separated by a semi colon (;). Each category needs to be placed in a separate column. Sometimes column 'AM' will include up to five different categories. I am only concerned with splitting the first two categories.

e.g.

Maintenance & Consumables;Lubricants & Cutting Oils|SPECIAL OFFERS; Maintenance & Consumables - Special Offers|SPECIAL OFFERS;All Offers And Deals

I would want

Maintenance & Consumables in collumn 'AK'

Lubricants & cutting oils in collumn 'AL'


similarly for

Hand Tools;Bits


I would want

Hand Tools in column 'AK

Bits in column 'AL'


I would always want the semi colon to be removed.


Good Luck!
rob700Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
Naresh PatelConnect With a Mentor TraderCommented:
Use =LEFT(D2,SEARCH(" ",D2)-1)  for Column D
0
 
Santosh GuptaCommented:
missing attachment.
0
 
rob700Author Commented:
Apologies

Attachment below
Excel-Questions---splitting-cell.xlsx
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Go Premium
 
Lee IngallsDirector of IT/TS, Quality and FinanceCommented:
Use Text to Columns on the Data tab.
You can use the semi-colon and the ampersand as delimiters.
Select delimited rather than fixed width.
This will get you started...
Text-to-Column.pdf
0
 
Santosh GuptaCommented:
for condition 1,

use formula in column C


=LEFT(D2,FIND(" ",D2)-1)


for condition 2,

1. select column AN and insert 8 to 10 Rows.
2. now select column AM, Go to DATA menu and select "text to column"
3. select delimited and then  select "semicolon" as delimiters.
4. click finish.
5. deleted unwanted column.
0
 
Naresh PatelTraderCommented:
Use for Column D=LEFT(D2,SEARCH(" ",D2)-1)
Use for Column AK=LEFT(AM2,FIND(";",AM2)-1)
Use for column AL=RIGHT(AM2,LEN(AM2)-FIND(";",AM2))
0
 
Naresh PatelTraderCommented:
Use For Column AL =IF(ISERR(FIND("|",AM2))=FALSE,LEFT(RIGHT(AM2,LEN(AM2)-FIND(";",AM2)),FIND("|",RIGHT(AM2,LEN(AM2)-FIND(";",AM2)))-1),RIGHT(AM2,LEN(AM2)-FIND(";",AM2)))


You closed the question but I thought what ever I posted is not up to the mark.

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.