I've inherited a big spreadsheet with a column that is full of entries
like

"Symptom : PAIN Cause : INFECTION Rx : AMOXICILLIN Symptom :
SORENESS Cause : STRAIN Rx : TYLENOL"

Does anyone know of a good way to change this column into three
separate columns like

Symptom1 Cause1 Rx1 Symptom2 Cause2 Rx2
PAIN INFECTION AMOXICILLIN SORENESS STRAIN TYLENOL

Thanks for any suggestions. (I've looked at a few books and couldn't
find an example, and I thought somebody out there may have encountered
this problem...)

Kay

\tools\data\text to columns, delimiting using : should do it

"Kay E" wrote in message
...
I've inherited a big spreadsheet with a column that is full of entries
like

"Symptom : PAIN Cause : INFECTION Rx : AMOXICILLIN Symptom :
SORENESS Cause : STRAIN Rx : TYLENOL"

Does anyone know of a good way to change this column into three
separate columns like

Symptom1 Cause1 Rx1 Symptom2 Cause2 Rx2
PAIN INFECTION AMOXICILLIN SORENESS STRAIN TYLENOL

Thanks for any suggestions. (I've looked at a few books and couldn't
find an example, and I thought somebody out there may have encountered
this problem...)

Kay

Hi Kay,

If your data are actually structured like:
Symptom : PAIN Cause : INFECTION Rx : AMOXICILLIN
Symptom : SORENESS Cause : STRAIN Rx : TYLENOL
and not all on one line, like:
Symptom : PAIN Cause : INFECTION Rx : AMOXICILLIN Symptom : SORENESS Cause : STRAIN Rx : TYLENOL
then, provided each category consists of one word only, it's relatively easy.

With:
Symptom : PAIN Cause : INFECTION Rx : AMOXICILLIN
Symptom : SORENESS Cause : STRAIN Rx : TYLENOL
the first step would be to use Date|Text to Columns|Delimited check Space & Other. For other, insert a colon (:)
Click Finish.

This will give six columns
Symptom|PAIN|Cause|INFECTION|Rx|AMOXICILLIN
Symptom|SORENESS|Cause|STRAIN|Rx |TYLENOL
Now, insert a blank row and copy the new second row and paste it into the worksheet at B1
Finally, select columns A, C, E & G (eg select A then hold down the Ctrl key as you select C, E & G) then press delete.

Voila! Data in three columns with headers.

If your data structure isn't so simple, it'll take more work to parse

Cheers


--
macropod
[MVP - Microsoft Word]


"Kay E" wrote in message ...
| I've inherited a big spreadsheet with a column that is full of entries
| like
|
| "Symptom : PAIN Cause : INFECTION Rx : AMOXICILLIN Symptom :
| SORENESS Cause : STRAIN Rx : TYLENOL"
|
| Does anyone know of a good way to change this column into three
| separate columns like
|
| Symptom1 Cause1 Rx1 Symptom2 Cause2 Rx2
| PAIN INFECTION AMOXICILLIN SORENESS STRAIN TYLENOL
|
| Thanks for any suggestions. (I've looked at a few books and couldn't
| find an example, and I thought somebody out there may have encountered
| this problem...)
|
| Kay
|