Real Data Formatting Challenge

[Pat]

Okay, I'm importing an Excel file (using Excel 200) and I have two columns
with REALLY bad data formatting. I need to try and figure out some way to
extract certain pieces of data from each cell:

State (some use 2 letter and some use full name)
Profession (Physical Therapist, RN, etc)

There is no common deliminator (sp) but the data is all there (though some
logic will be needed to decipher for example in the 5th row WY- 7pm-7am-
Med.surg. is and RN position. I can write code to decipher that. I just
can't figure out a way to get all the data into some managable format. Here
is a copy of the data in the column in question:


Name
MT-DayPT
ME - Day - Physical Therapist
MT ER/M/S Nights
WY- 7pm-7am- Med.surg.
TX-RN OR/Circulator
TX - ER RN Central Texas
NH-PT-PERM
VT-Family Practice Physician
IL - Physical Therapist
WA - ER Nurse - Nights - Start 12/22
WA - ER Nurse - Days - Start 12/22
ME - Physical Therapy Assistant
AK OR RN
AK PT
TX- Days- Perm Staff PT
OK Med/Surg 7p-7a Float
OK. PICU RN Nights
WA- 12 hr- night shifts- L&D
MT - RN Circulator
NC- CVICU/PICU Varied- Day Shift
NC- CVICU/PICU Varied- Night Shift
NC- CRNA- 11a to 7:30p
HI - LPN
CA-Surgery RN
MA - PERM Speech Therapist - St Lukes Hospital - Mixed shifts w/ weekends
MA - PERM Speech Therapist - Tobey Hospital - Days
NH- Days/Eves Cath Lab RN
MT PT Inpatient
Kansas-PT- IP- NEW RATE!
OR - PERM - Med Tech - Day Shift
MT - Physical Therapist
MT - Interim Director of Womens and Childrens Unit
VA - Physical Therapist
SC PT
SC PT
NE - Physical Therapist
PHARMACIST - FULL TIME PERMANENT
CO - Medical Technologist
NE Day Shift Occupational Therapist
NE Day Shift Occupational Therapy Assistant
NE Day Shift Physical Therapist
MT ICU RN 13wk
MT Emergency Dept RN
MT - OR Circulating RN (permanent placement)
MT - CT/Radiologic Technologist (permanent placement)
PA - RN TELE DAY/EVENING - NEW RATE
PA - RN TELE NIGHTS
WI- Pharmacist Clinic Retail
WI- Pharmacist Clinic Retail
MT - 7p OB/medsurg
ME - Physical Therapist
CA- Occupational Therapist
MN- Ultrasound PERM
MN- Physical Therapist PERM
MN- Physical Therapist PERM
MN- Med Tech/ MLT PERM
WY - OB Night Nurse
NE- RN for NICU-nights
IL-Cath Lab Staff RN
IL-OR Staff RN
VT PT Days Full time- NEW RATE!
IA - Night Shift - Neuro Science
RI - 1st Shift Physical Therapist I/P
AZ- ER RN **DAYS**
AK - NPs and PAs Needed ASAP
AK - MedSurg /Inpatient Nights
AK - MedSurg /Inpatient ASAP
AK - ER/Weekends
AK - Pharmacist
AR PT Outpatient
AR PTA Outpatient
MT - PERM - Family Practice Physician
MT - PERM - RN alternating day-night shifts
MT- OR/RN
MT- Billings / Acute Dialysis RN
IA-OR/RN (Ortho/General)
IA - ASAP MedSurg/Float Nurse/Nights
IA - ASAP MedSurg/Float Nurse/Nights
MS-Physical Therapist
NE FT Days OT
NE Neonate Nurse Practitioner
NE FT Days Physical Therapist - In-Patient
CA - IP Physical Therapist PERM PLACEMENT
AZ - House Supervisor
AZ - ICU (URGENT)
AZ - PEDS MED-SURG ASAP!!!!
MI-Internal Medicine
MN - Physical Therapist
OH PT days inpatient outpatient- NEW RATE!
TX - TELE RN - Nights
CO - Med Tech
WI- Physical Therapist
WI - NP (Work Med)
CO - SURGERY TECH
Another Medefis Test No Bids
NH Physical Therapist
MT- Speech Pathologist- FT Days
AK-PA or NP


Any help from the experts would be greatly appreciated!!!!!!

 

[vezerid]

That's a good one to give to my students as a project!

Here are two formulas that can help you sort MOST of the entries out,
so that you complete it manually. To extract the state:

=IF(OR(CODE(MID(A2,3,1))={32,45,46}),LEFT(A2,2),"")

This one tests that the third character is space, dash or dot. If so,
the complementary formula for the rest of the description:

=IF(B2<>"",TRIM(SUBSTITUTE(SUBSTITUTE(MID(A2,3,LEN(A2)),"-","",
1),".","",1)))

This formula will extract the second part. It will leave a FALSE in
the case of Kansas and some entries where state is not mentioned but
will satisfactorily sort out the rest.

HTH
Kostis Vezerides

 

[Pat]

Thank you, glad it was a "good" question. Your formula helps a lot and it
will be a lot easier to fix the imported data then typing it all!

 

[vezerid]

Glad it was helpful!

Kostis

Thank you, glad it was a "good" question.  Your formula helps a lot andit
will be a lot easier to fix the imported data then typing it all!