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!
Solved
How do I split cells?
Posted on 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!
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!
[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
3
2
- +1
7 Comments
Active 1 day ago
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
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
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.
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.
Active today
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("
Use for Column AK=LEFT(AM2,FIND(";",AM2)-1)
Use for column AL=RIGHT(AM2,LEN(AM2)-FIND("
Featured Post
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…
Suggested Courses
Course of the Month10 days, 6 hours left to enroll
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.