# Parsing Data in Excel

Status
This thread has been Locked and is not open to further replies. The original thread starter may use the Report button to request it be reopened but anyone else with a similar issue should start a New Thread. Watch our Welcome Guide to learn how to use this site.

#### smody121

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...

#### Attachments

• 15 KB Views: 50

#### bomb #21

"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?

#### smody121

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.

#### bomb #21

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?

#### smody121

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?

#### bomb #21

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 )

#### bomb #21

smody121 said:
That worked, thanks! 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?
"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".

#### smody121

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...

#### bomb #21

"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?

#### smody121

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...

#### smody121

I'm not opposed to getting into marco world

#### bomb #21

If C3:K3 transposes to X3:X11 then (example) C4:F4 transposes to -- where?

#### smody121

X12:X20 would make the most sense, maybe Y3:Y11 if that's easier or more possible

#### bomb #21

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

#### smody121

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.

Status
This thread has been Locked and is not open to further replies. The original thread starter may use the Report button to request it be reopened but anyone else with a similar issue should start a New Thread. Watch our Welcome Guide to learn how to use this site.

As Seen On

### Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

over 807,865 other people just like you!