+ Reply to Thread
Results 1 to 8 of 8

splitting multiple rows of data in one cell into multiple cells

  1. #1
    Registered User
    Join Date
    08-15-2010
    Location
    melbourne, australia
    MS-Off Ver
    Excel 2007
    Posts
    39

    splitting multiple rows of data in one cell into multiple cells

    hi all, I have a problem that need your help

    I extraced data from some database in this format:
    10,000
    30,000
    40,000
    60,000
    for example, all 4 rows of data above would be in one cell. Of course i Have hundred of cells like this and not all have 4 rows, some have more some have less. Im wondering if there is a command that would make it possible to separate this one cell into 4 separate cells(rows of data) ?

    thank you.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    38,939

    Re: splitting multiple rows of data in one cell into multiple cells

    Sounds like Data | Text to Columns should do this for you. You could either use the newline character as the delimiter or, if the numbers are all the same length, fixed length.

    Regards

  3. #3
    Registered User
    Join Date
    08-15-2010
    Location
    melbourne, australia
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: splitting multiple rows of data in one cell into multiple cells

    erm I have tried it but it doesnt work
    for example:
    15,000
    10,000
    when i click text to column and follow the wizard, in the end it gives me jsut the 15,000

    not sure if i do it correctly though because im quite beginner in regards to excel

  4. #4
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097

    Re: splitting multiple rows of data in one cell into multiple cells

    Hi,

    Try Data > Text to columns > Delimited > Tab + Other and type in Alt+010 on the numeric keypad > Finish
    oldchippy
    -------------


    Blessed are those who can give without remembering and take without forgetting

    If you are happy with the help you have received, please click the <--- STAR icon on the left - Thanks.

    Click here >>> Top Excel links for beginners to Experts

    Forum Rules >>>Please don't forget to read these

  5. #5
    Registered User
    Join Date
    08-15-2010
    Location
    melbourne, australia
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: splitting multiple rows of data in one cell into multiple cells

    @oldchippy
    im using laptop and hence have no numeric keypad

    @TMshucks
    attached, ill explain what i wanna do as well: basically the numbers there are nominal value, I need to change them into real value, but because there are four amounts occuring at 4 different dates I need to times them with diff coefficients, and i cant do it if they are all in one cell. thank you ! much appreciated
    Attached Files Attached Files

  6. #6
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097

    Re: splitting multiple rows of data in one cell into multiple cells

    Hi,

    Aren't there any keys within the main keyboard so that you can apply the numeric keypad combination?

    What is the laptop?

  7. #7
    Registered User
    Join Date
    08-15-2010
    Location
    melbourne, australia
    MS-Off Ver
    Excel 2007
    Posts
    39

    Re: splitting multiple rows of data in one cell into multiple cells

    yeah there is, I tried it and it works ! thx a lot oldchippy

    there is one further complication though, is it possible to make it in rows instead of columns ?
    Im still unsure of how I can perform the calculation if its in columns

    thx again !!

  8. #8
    Forum Expert oldchippy's Avatar
    Join Date
    02-14-2005
    Location
    Worcester, UK
    MS-Off Ver
    Excel 2007 (Home)
    Posts
    7,097

    Re: splitting multiple rows of data in one cell into multiple cells

    It should have put it in column not rows?

    Are you saying the dates are all below one another in separate cells and the values in the next column?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1