Separating text in one cell into unique columns

[sanjhackett]

Hi,

I am trying to seperate a large amount of data into unique columns. How can i create a column with just health resort and yoga classes etc. An example of my data is:

Any assistance would be greatly appreciated.

<table x:str="" style="border-collapse: collapse; width: 96pt;" border="0" cellpadding="0" cellspacing="0" width="128"><col style="width: 48pt;" span="2" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt; width: 96pt;" width="128" height="17">yoga_classes^,^yoga_intensive_courses^,^meditation_intensive_courses^,^seminars^,^teacher_training^,^india_tour^,^massage_intensive_courses</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt;" height="17">health_resort</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt;" height="17">health_resort^,^yoga_classes^,^meditation_classes^,^yoga_intensive_courses^,^seminars^,^teacher_training^,^yoga_classes^,^massage_intensive_courses</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt;" height="17">health_resort</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt;" height="17">health_resort^,^health_resort</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt;" height="17">health_resort^,^health_resort^,^yoga_classes^,^yoga_intensive_courses^,^meditation_intensive_courses^,^seminars^,^teacher_training^,^india_tour^,^massage_intensive_courses</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt;" height="17">health_resort</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt;" height="17">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt;" height="17">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt;" height="17">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt;" height="17">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt;" height="17">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt;" height="17">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt;" height="17">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt;" height="17">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt;" height="17">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt;" height="17">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt;" height="17">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt;" height="17">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td colspan="2" style="height: 12.75pt;" height="17">
</td> </tr><tr><td style="vertical-align: top;">
</td><td style="vertical-align: top;">
</td></tr> </tbody></table>

 

[hiker95]

sanjhackett,

Welcome to the MrExcel board.

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

 

[jbeaucaire]

Highlight the data to split.
Click on Data > Text to Columns > Delimited > Next
Make sure [x] Comma and [x] Other ^ are filled in
Click on [x] Treat consecutive delimiters as one
Finish.

 

[sanjhackett]

Thanks for your reply!

This seperates them but it doesn't keep all the same ones together. I need for example all the "Health resort" in one column and alll the ""yoga classes in another column etc

At the moment it looks like this:

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 10pt; font-family: Arial,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 805px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td>
</td> <td>AN</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">30634</td> <td>health_resort^,^yoga_classes^,^meditation_classes</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">30635</td> <td>health_resort^,^health_resort^,^yoga_classes^,^meditation_classes</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">30636</td> <td>meditation_classes^,^yoga_intensive_courses^,^massage_intensive_courses</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">30637</td> <td>yoga_classes^,^meditation_classes^,^yoga_intensive_courses^,^massage_intensive_courses</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">30638</td> <td>health_resort^,^yoga_classes^,^yoga_intensive_courses^,^massage_intensive_courses</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">30639</td> <td>health_resort^,^health_resort^,^yoga_classes^,^meditation_classes^,^yoga_intensive_courses^,^massage_intensive_courses</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">30640</td> <td>health_resort^,^health_resort^,^yoga_classes^,^meditation_classes^,^yoga_intensive_courses^,^massage_intensive_courses</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">30641</td> <td>yoga_classes^,^meditation_classes^,^yoga_intensive_courses^,^massage_intensive_courses</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">30642</td> <td>yoga_classes^,^meditation_classes^,^yoga_intensive_courses^,^massage_intensive_courses</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">30643</td> <td>meditation_classes</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">30644</td> <td>health_resort^,^health_resort</td></tr></tbody></table>

I would like it to look like this:

<table style="padding-right: 2pt; padding-left: 2pt; font-size: 10pt; font-family: Arial,Arial; background-color: rgb(255, 255, 255);" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="font-weight: bold; width: 30px;"> <col style="width: 85px;"> <col style="width: 88px;"> <col style="width: 119px;"> <col style="width: 92px;"> <col style="width: 115px;"> <col style="width: 118px;"></colgroup> <tbody> <tr style="font-weight: bold; font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;"> <td>
</td> <td>AX</td> <td>AY</td> <td>AZ</td> <td>BA</td> <td>BB</td> <td>BC</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">30634</td> <td>Health retreat</td> <td>Yoga Classes</td> <td>Meditation Classes</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">30635</td> <td>Health retreat</td> <td>Yoga Classes</td> <td>Meditation Classes</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">30636</td> <td>
</td> <td>
</td> <td>Meditation Classes</td> <td>Yoga Intensive</td> <td>Massage Intensive</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">30637</td> <td>
</td> <td>Yoga Classes</td> <td>Meditation Classes</td> <td>Yoga Intensive</td> <td>Massage Intensive</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">30638</td> <td>Health retreat</td> <td>Yoga Classes</td> <td>
</td> <td>Yoga Intensive</td> <td>Massage Intensive</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">30639</td> <td>Health retreat</td> <td>Yoga Classes</td> <td>
</td> <td>Yoga Intensive</td> <td>Massage Intensive</td> <td>Mediation Intensive</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">30640</td> <td>Health retreat</td> <td>Yoga Classes</td> <td>Meditation Classes</td> <td>Yoga Intensive</td> <td>Massage Intensive</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">30641</td> <td>
</td> <td>Yoga Classes</td> <td>Meditation Classes</td> <td>Yoga Intensive</td> <td>Massage Intensive</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">30642</td> <td>
</td> <td>Yoga Classes</td> <td>Meditation Classes</td> <td>Yoga Intensive</td> <td>Massage Intensive</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">30643</td> <td>
</td> <td>
</td> <td>Meditation Classes</td> <td>
</td> <td>
</td> <td>
</td></tr></tbody></table>
Is it a matter of writing some sort of formula for each?
'Thanks again for your help too!



Excel tables to the web >> Excel Jeanie HTML 4

 

[jbeaucaire]

OK, no macro needed, just construct some search formulas, one for each column and drag down. It allows you to customize the search string and the results that appear when true.

Excel Workbook
	AN	AX	AY
30639	health_resort^,^health_resort^,^yoga_classes^,^meditation_classes^,^yoga_intensive_courses^,^massage_intensive_courses	Health Resort	Yoga Classes
Sheet4

 

[sanjhackett]

Thank you!!

That worked a treat. Thanks for your help!