1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Parsing Data in Excel

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

Thread Status:
Not open for further replies.
Advertisement
  1. smody121

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

    Attached Files:

  2. bomb #21

    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? :confused:
     
  3. smody121

    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.
     
  4. bomb #21

    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?
     
  5. smody121

    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?
     
  6. bomb #21

    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 :eek:)
     
  7. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    "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 ;)
     
  8. smody121

    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...
     
  9. bomb #21

    bomb #21

    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? :)
     
  10. smody121

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

    smody121 Thread Starter

    Joined:
    Aug 19, 2010
    Messages:
    65
    I'm not opposed to getting into marco world
     
  12. bomb #21

    bomb #21

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

    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
     
  14. bomb #21

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

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

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/972996

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice