|
Real Data Formatting Challenge
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!!!!!! |
Real Data Formatting Challenge
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 On Nov 25, 5:01*pm, Pat wrote: 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!!!!!! |
Real Data Formatting Challenge
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" wrote: 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 On Nov 25, 5:01 pm, Pat wrote: 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!!!!!! |
Real Data Formatting Challenge
Glad it was helpful!
Kostis On Nov 25, 8:31*pm, Pat wrote: 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" wrote: 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 On Nov 25, 5:01 pm, Pat wrote: 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!!!!!! |
| All times are GMT +1. The time now is 07:16 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com