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
LVL 7
tomfarrarAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
nutschCommented:
column B
=LEFT(A1,FIND("|",SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1))-1)

column D:
=TRIM(RIGHT(A23,4))

Column C:
=TRIM(SUBSTITUTE(SUBSTITUTE(A1,D1,""),B1,""))
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
Don ThomsonCommented:
As some of your data has more than 1 word for the first field it's not going to be easy
Would the text for the first field ever have more than 2 words (i.e.  Like Cretin Derham Hall)
0
 
tomfarrarAuthor Commented:
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
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
tomfarrarAuthor Commented:
Actually, yes, the first column could have more than words.  I depends on the name of the school.
0
 
Don ThomsonCommented:
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)
0
 
nutschCommented:
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
0
 
Don ThomsonCommented:
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?)
0
 
tomfarrarAuthor Commented:
I tried your formula, nutsch, but let me try it again.  Be back shortly.
0
 
Don ThomsonCommented:
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 :)
0
 
nutschCommented:
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
0
 
tomfarrarAuthor Commented:
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.
0
 
tomfarrarAuthor Commented:
Thanks again!  Your solution worked perfectly.  I am studying the formula to better understand how it accomplished the task.
0
 
Don ThomsonCommented:
Nutsch - Nice formula - I too would be interested in how it determines the correct number of word in the first parsed record
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.