Solved

How do I split cells?

Posted on 2014-04-08
7
160 Views
Last Modified: 2014-04-08
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!
0
Comment
Question by:rob700
7 Comments
 
LVL 13

Expert Comment

by:Santosh Gupta
ID: 39986082
missing attachment.
0
 

Author Comment

by:rob700
ID: 39986089
Apologies

Attachment below
Excel-Questions---splitting-cell.xlsx
0
 
LVL 8

Expert Comment

by:Lee Ingalls
ID: 39986104
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Accepted Solution

by:
itjockey earned 500 total points
ID: 39986108
Use =LEFT(D2,SEARCH(" ",D2)-1)  for Column D
0
 
LVL 13

Expert Comment

by:Santosh Gupta
ID: 39986132
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
 
LVL 8

Expert Comment

by:itjockey
ID: 39986147
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
 
LVL 8

Expert Comment

by:itjockey
ID: 39986285
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question