Parsing Data in Excel

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

smody121

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

Attachments

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? :confused:
 

smody121

Thread Starter
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.
 
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?
 

smody121

Thread Starter
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?
 
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 :eek:)
 
Joined
Jul 1, 2005
Messages
8,546
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?

:D

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". :p ;)
 

smody121

Thread Starter
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...
 
Joined
Jul 1, 2005
Messages
8,546
"Any ideas on how to eliminate this?"

Nice edit you snuck in there. :p

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

smody121

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

smody121

Thread Starter
Joined
Aug 19, 2010
Messages
65
X12:X20 would make the most sense, maybe Y3:Y11 if that's easier or more possible
 
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
 

smody121

Thread Starter
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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
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.

Join over 807,865 other people just like you!

Latest posts

Members online

Top