+ Reply to Thread
Results 1 to 6 of 6

how to separate data in cell into separate cells , sorted by value

  1. #1
    Registered User
    Join Date
    06-22-2014
    Location
    canada
    MS-Off Ver
    2010
    Posts
    12

    how to separate data in cell into separate cells , sorted by value

    hoping i can trouble someone on this forum for some help. i have spreadsheet which has been exported from an online registration system. currently, one of the cells contains a number of various values, separated by commas. i need to have each value appear in a separate cell - but all sorted according to the value. the values however are not all consistent . it's described more clearly in the attached document, but for example.
    line 1 . column A: values a,b,d
    line 2. column A: values a,c
    line 3 column A: value d

    how can i make each line have the same values appear in the same column
    line 1. [column A: a] [column B : b] [column C: blank] [column D: d]
    line 2. [column A: a] [column B : blank] [column C: c] [column D: blank]
    ....

    thanks for your time !
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor Gatti's Avatar
    Join Date
    06-08-2015
    Location
    Brasil, São Paulo, Ribeirão Preto
    MS-Off Ver
    365
    Posts
    346

    Re: how to separate data in cell into separate cells , sorted by value

    Hello my friend

    I inserted one row in row 12 (above the "Racer 1"), then inserted event 1 in C12, event 2 in D12...

    To the cels bellow where you want the get the value, I put this formula:

    Please Login or Register  to view this content.
    Workbook with the sample: sample registration data.xls

    Solves your problem?

  3. #3
    Registered User
    Join Date
    06-22-2014
    Location
    canada
    MS-Off Ver
    2010
    Posts
    12

    Re: how to separate data in cell into separate cells , sorted by value

    hi gatti, thank you so much for taking the time to get back to me - and for solving my problem. i've tried to apply the formula you suggested to the actual spreadsheet - but i can't seem to figure out how to adapt the formula you created. for simplicity i had just provide a summary of the problem in my original post. the actual xls report is attached. hate to ask you for more help, but could you look at the attached . the spreadsheet is in the format that it will be generated by the online entry system. the cell that i will need separate is column W. the final version will have about 4000 rows. thanks again !
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor Gatti's Avatar
    Join Date
    06-08-2015
    Location
    Brasil, São Paulo, Ribeirão Preto
    MS-Off Ver
    365
    Posts
    346

    Re: how to separate data in cell into separate cells , sorted by value

    Hello again

    Just checked your file

    You have the data in Column W, but where you want to put the values? You gonna split it in other columns? Where the splited "races" will be?

  5. #5
    Registered User
    Join Date
    06-22-2014
    Location
    canada
    MS-Off Ver
    2010
    Posts
    12

    Re: how to separate data in cell into separate cells , sorted by value

    hi gatti, could i just insert 8 blank columns after W as the destination for splited races ?

  6. #6
    Valued Forum Contributor Gatti's Avatar
    Join Date
    06-08-2015
    Location
    Brasil, São Paulo, Ribeirão Preto
    MS-Off Ver
    365
    Posts
    346

    Re: how to separate data in cell into separate cells , sorted by value

    Oh, ok... Then just insert 8 columns after W (X to AE), then put headers on it (Race 1 to Race 8), and insert the formula (iniciating in X3):

    Please Login or Register  to view this content.
    The solved workbook:registration sample 1.xlsx

    Is this OK?

    If it solves your problem, mark the Thread as [SOLVED] and, if possible, add reputatioon

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Need help to separate numeric values into separate cells (LEFT and RIGHT won't work)
    By RichMcc in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-27-2013, 07:48 PM
  2. [SOLVED] separate data from one cell into two separate cells question
    By lilsnoop in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-19-2012, 05:53 PM
  3. Sorted data on separate sheets
    By Wibs in forum Excel General
    Replies: 5
    Last Post: 10-30-2011, 02:53 PM
  4. Replies: 10
    Last Post: 09-27-2011, 03:40 PM
  5. Excel 2003 separate values into separate cells
    By nickolm in forum Excel General
    Replies: 1
    Last Post: 06-17-2010, 10:05 AM
  6. [SOLVED] Separate first and second name in one cell into separate cells.
    By Dwight in Georgia in forum Excel General
    Replies: 4
    Last Post: 01-25-2006, 05:15 PM
  7. Replies: 3
    Last Post: 06-08-2005, 11:05 AM
  8. [SOLVED] RE: Separate first and second name in one cell into separate cells.
    By Gary's Student in forum Excel General
    Replies: 0
    Last Post: 04-27-2005, 07:06 PM

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