Solved

How do I split cells?

Posted on 2014-04-08
7
158 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

810 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