Solved

How do I split cells?

Posted on 2014-04-08
7
161 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 8

Accepted Solution

by:
Naresh Patel 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:Naresh Patel
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:Naresh Patel
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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

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…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

752 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