Help With Manipulating Data

[Pat]

I have a file that I'm getting from an source, the first field contains a
field like this:

OK - Interim OR Director / Southwestern Medical Center
IA - ASAP MedSurg/Float Nurse/Nights / Allen Hospital
AZ - ICU (URGENT) / Tuba City Regional
MT- OR/RN / Billings Clinic
CO - RN OR Eve shift / Medical Center of the Rockies

Each line represents one cell. My problem is that I need this data
transformed some how in to three fields. State, Position, Facility (which is
the order and information contained in the cell).

Any thoughts, suggestions? Thanks in advance for your help.

 

[Shane Devenshire]

Hi,

In many case the best way to break up data that is in single cell (column)
into two or more cells is to use the Text to Columns Wizard.

1. Select a single column of cells which you want to split.
2. Choose the command Data, Text to Columns,
3. Choose Delimited and click Next
4. Choose one of the delimiter or define one of your own in the Other box,
and then click Next
5. In many cases there is nothing you need to do at this step, so just click
Finish.

In your case run the command once and choose Fixed Width on the first
screen, on the second screen click between the 5th and 6th character and
click Finish.

Then select the second column of data, which now contains the other two
pieces of information and repeat the Text to Columns command but this time
choose Delimited and on the next screen enter / in the Other box and click
Finish.

If this information helps, please click the Yes button.

Cheers,
Shane Devenshire

 

[Jarek Kujawa]

another way:

State: =LEFT(A1,2)
Position: =MID(A1,FIND(" - ",A1)+3,LEN(A1)-FIND(" - ",A1)-3-FIND(" /
",A1)-4)
Facility: =MID(A1,FIND(" / ",A1)+3,LEN(A1)-FIND(" / ",A1)-2)

HIH

 

[James]

You may need to do a bit of preperation on your cells for this to work, by
using the replace option in Excel.
Use the replace option to replace the Hyphen " - " with a comma " , "
Highlight all the cells then Data/Text to Columns, Delimited checked/Next.
"Tab" checked "Comma" checked "Other" checked then type a "/" into the box.
Next/ Finish.
A problem arises with these lines "MT- OR/RN / Billings Clinic" and "IA -
ASAP MedSurg/Float Nurse/Nights / Allen Hospital" because "OR", "RN" and
"Float Nurse","Nights" appear in seperate columns.

OK Interim OR Director Southwestern Medical Center
IA ASAP MedSurg Float Nurse Nights Allen Hospital
AZ ICU (URGENT) Tuba City Regional
MT OR RN Billings Clinic
CO RN OR Eve shift Medical Center of the Rockies


Apart from these it works great.