I've been scratching my head how to solve this one. I have a report that contains a column with job position information. The field can contain up to 100 positions in each cell. I want to parse them out to their own columns. The only consistent thing about each new position is that it starts with a "P" and an 8 digit number... oh and line breaks (see below)
Here's a de-identified sample of what could be in a cell:
Position |
P01234567 OB-PA (Unfilled) |
P12345678 Physician - Radiation Oncologist-7 (Closed)
P12345679 Physician - Joe Smith
P12345680 Physician - Radiation Oncologist-2 (Unfilled) |
P40000008 Student Pharmacist - Rotation -5 (Closed)
P40000007 Student Pharmacist - Rotation -4 (Closed)
P40000006 Student Pharmacist - Rotation -3 (Closed)
P40000005 Student Pharmacist - Rotation (Unfilled)
P40000004 Student Pharmacist - Rotation (Unfilled)
|
Yes, there are two line breaks in between the data. I can remove them using the cleanse tool or use them to my advantage
Are there any solutions that anyone can think of that could account for 1 to 100 of these in a single cell and parse them out (ultimately) to their own columns in the format of:
Position 1 | Position 2 | Position 3 | Position 4 | Position N |
P01234567 OB-PA (Unfilled) | null | null | null | null |
P12345678 Physician - Radiation Oncologist-7 (Closed) | P12345679 Physician - Joe Smith | P12345680 Physician - Radiation Oncologist-2 (Unfilled) | null | null |
RegEx and parse will only allow me to do so many.
Thanks!
Solved! Go to Solution.
@JFurda
If I understand you intention correctly
@Qiu That's nearly it! My incoming data has two line breaks (a blank line between each line of data) I threw a filter between the Text to Rows and Tile tools to keep any entries where !IsEmpty([Position]) .
Many, many thanks for the efficient solution! I can even join my data back to the remainder of the report based on the RecordID.