Parsing Data - Excel 2007
Hi - I have data that looks like the attached that I would like to parse into 3 fields as shown on the first line. Can someone give me guidance on how to best accomplish this? Thanks.
STAT.JPG
STAT.JPG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
My apology, I was not clear. The data in Column A looks like this for the first record:
"Cretin-Derham Hall OL 6-8" (without the quotations). There is no "|". The data would need to look like this for Columns B,C and D after parsing:
Col B - Cretin-Derham Hall
Col C - OL
Col D - 6-8
Thanks. - Tom
"Cretin-Derham Hall OL 6-8" (without the quotations). There is no "|". The data would need to look like this for Columns B,C and D after parsing:
Col B - Cretin-Derham Hall
Col C - OL
Col D - 6-8
Thanks. - Tom
ASKER
Actually, yes, the first column could have more than words. I depends on the name of the school.
You need a bunch of hidden columns
1 - Length of full expression
2 - Position of 1st Space
3 - Position of 2nd space
4 - Position of 3rd space
.
.
6 - Count of total spaces
As the last 2 fields are always a single word (no Spaces) then once you find the last valid space position - then that number +1 is the position of the last field. then go back one and the second last valid space+1 is the start of the second field and that second last space position is the Length - that number which you can calculate a =left(a1,xxx)
1 - Length of full expression
2 - Position of 1st Space
3 - Position of 2nd space
4 - Position of 3rd space
.
.
6 - Count of total spaces
As the last 2 fields are always a single word (no Spaces) then once you find the last valid space position - then that number +1 is the position of the last field. then go back one and the second last valid space+1 is the start of the second field and that second last space position is the Length - that number which you can calculate a =left(a1,xxx)
Have you actually tried my formulas? There's no | in your text, but there's one after I substitute it for the penultimate space in your data.
Applied to Cretin-Derham Hall OL 6-8, I get:
Cretin-Derham Hall
OL
6-8
Applied to Cretin-Derham Hall OL 6-8, I get:
Cretin-Derham Hall
OL
6-8
nutch - I tried your formulas and they only worked for the first field (column B)
The second filed ended up as the 2nd and 3rd field and the last field (D) was blank - It is pointing to A23 (What's that all about?)
The second filed ended up as the 2nd and 3rd field and the last field (D) was blank - It is pointing to A23 (What's that all about?)
ASKER
I tried your formula, nutsch, but let me try it again. Be back shortly.
Nutch
d should be =TRIM(RIGHT(A1,4)) However - if the length of the last field is > 3 then is bombs again (Not that their are people that are 10 ft in Height :)
d should be =TRIM(RIGHT(A1,4)) However - if the length of the last field is > 3 then is bombs again (Not that their are people that are 10 ft in Height :)
Indeed DTH, I had started on the 23rd row before adjusting, and indeed again, This doesn't take into account people taller than 10 ft or shorter than 1 foot. I'll take my chances.
Thomas
Thomas
ASKER
You are so correct, nutsch. Your formula does work if I create the formulas in the order you showed them (Col B, D and then C) and also replace TRIM(RIGHT(A23,4)) with TRIM(RIGHT(A1,4)). Thank you.
ASKER
Thanks again! Your solution worked perfectly. I am studying the formula to better understand how it accomplished the task.
Nutsch - Nice formula - I too would be interested in how it determines the correct number of word in the first parsed record
Would the text for the first field ever have more than 2 words (i.e. Like Cretin Derham Hall)