Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How do I split cells?

Posted on 2014-04-08
7
Medium Priority
?
164 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 9

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
Independent Software Vendors: 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:
Naresh Patel earned 1500 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

722 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