# Parsing Data in Excel

Discussion in 'Business Applications' started by smody121, Jan 6, 2011.

Not open for further replies.

Joined:
Aug 19, 2010
Messages:
65
Please look at attached data sample. I have one cell that has many rows of data separated by a return (I think). Text to Columns didn't help, or at least I couldn't figure out a way for it to help. I have another 15,000 lines of these that need to be parsed out so that one value from column B matches the one value in column A...

File size:
15 KB
Views:
50
2. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
"so that one value from column B matches the one value in column A"?

Which value of:

356172QA7
356172QB5
356172QC3
356172QD1
356172QE9
356172QF6
356172QG4
356172QH2
356172QJ8

"matches" 3.575?

Joined:
Aug 19, 2010
Messages:
65
Sorry for being too vague, all of the values match 3.575. I would like to separate each value of column B into it's own cell so I can then match them with the value in A.

4. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
So you have 3.575 in A3, 9 values in B3. Don't know if (a) B is always 9 values (b) all 9 values are always different. But we can try something.

In C3:

=MID(B3,1,9)

In D3:

=MID(\$B3,FIND(C3,\$B3)+10,9)

Copy D3 to D3:K3. Voila?

Joined:
Aug 19, 2010
Messages:
65
That worked, thanks! By the way, all values in column B are 9 characters long, but they have different amounts of values. So C3:K3 may turn into C15:F15 since there are only 4 values needing parsing. That creates a blank column and then the data re-parses the rest of the columns containing formulas. You can see what I mean if you copy paste the data past K3 and go out to like M3 with the formula. Any ideas on how to eliminate this? The reason I would need this leads me to:

Bonus question... what is the easiest way to transpose these values in their rows to columns? So our values from C3:K3 get dumped into say X3:X11?

6. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
Or another way? In C3:

=SUBSTITUTE(B3,CHAR(10),"#")

Then copy C3 & Paste Special > Values. Then text-to-columns that (# as delim).

(it's probably CHAR(10) ; trying to do ALT codes on a laptop kb's a nightmare )

7. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
"Bonus question" ... worth what?

Once C3:K3 are values you can select X3:X11, then type:

=TRANSPOSE(

, then select C3:K3, then type:

)

, then press: CTRL+Shift+Enter -- to create an array formula.

I guess it depends on your definition of "easy".

Joined:
Aug 19, 2010
Messages:
65
Ok I think I figured it out:

In C3:

=MID(B3,1,9)

In D3

=MID(\$B3,FIND(\$B3,\$B3)+10,9)

In E3:

=MID(\$B3,FIND(\$B3,\$B3)+20,9)

In F3:

=MID(\$B3,FIND(\$B3,\$B3)+30,9)

etc. and that should fix the spacing problem.

Now I just need to figure out how to transpose all of this data efficiently...

9. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
"Any ideas on how to eliminate this?"

Nice edit you snuck in there.

IMO, you're getting into macro "territory". Do you want to go there?

Joined:
Aug 19, 2010
Messages:
65
Bonus worth some Feel Good points? I was able to transpose the first line just fine, thanks. But I now need to somehow transpose the rest of the 14,000 lines. I can't seem to just anchor the columns and drag across the rows...

Joined:
Aug 19, 2010
Messages:
65
I'm not opposed to getting into marco world

12. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
If C3:K3 transposes to X3:X11 then (example) C4:F4 transposes to -- where?

Joined:
Aug 19, 2010
Messages:
65
X12:X20 would make the most sense, maybe Y3:Y11 if that's easier or more possible

14. ### bomb #21

Joined:
Jul 1, 2005
Messages:
8,546
Clarification. Do you 100% know that no values in B have more "sub-values" than will fit in C:W? If so, how?

X12:X20 would mean code for sure*. Y3:Y11 would be a doddle.

*edit: plus there could be a # rows issue

Joined:
Aug 19, 2010
Messages:
65
Well what I know is that each value in column B will be exactly 9 characters. They are codes that are completely unique to a value which I will be looking up against later.

The count of "CUSIPs" in each cell of column B differs from 0 to about 35. So in my big spreadsheet, I have my formulas going from C:BK and that safely covers all B cells that have longer lists of "CUSIPs". So yes, I'm 100% sure that each value (CUSIP) has 9 characters, but not sure about how many values there are in each B cell.

As Seen On