• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 196
  • Last Modified:

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
0
tomfarrar
Asked:
tomfarrar
  • 5
  • 5
  • 3
1 Solution
 
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
 
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now