+ Reply to Thread
Results 1 to 2 of 2

Formula for Splitting one column of assorted lists with a comma into 3 seperate columns

  1. #1
    Registered User
    Join Date
    09-25-2019
    Location
    Arizon
    MS-Off Ver
    Arizona
    Posts
    3

    Formula for Splitting one column of assorted lists with a comma into 3 seperate columns

    So basically I have a column with either 1, 2 or 3 numbers in them, for example: cell D2 has "123, 456", cell D3 has "987", and cell D4 has "123, 456, 789". I need those to be separated into 3 sperate columns so the end result can be: cell A2 has "123", cell B2 has "456"; cell A3 has "987"; and cell A4 has "123", cell B4 has "456", cell C4 has "789". I would like a formula for this, so that it is much more automated than Text to Columns.

    I can get the single value cells, and then either or of the other valued-cells, but not all 3. Have used "RIGHT" and "SEARCH" and "MID" and other combos but have nothing. Anything help would be greatly appreciated!
    Last edited by ktclifford; 09-27-2019 at 10:34 AM.

  2. #2
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Splitting one column of assorted lists with a comma into 3 seperate columns

    Select data, choose Data > Data Tools > Text To Columns.

    Select 'Delimited' > Next

    Select 'Comma' and 'Space', and select 'Treat consecutive delimiters as one' > Next

    Finish.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

+ 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] Splitting textstring (adress) into seperate columns
    By soli004 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-02-2014, 01:01 PM
  2. Splitting Information in columns into seperate rows
    By undergraduate in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-19-2010, 06:15 PM
  3. Splitting comma delimited numbers into new columns
    By salmansohail in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-30-2010, 07:00 PM
  4. Splitting a comma seperated value into multiple columns using SQL View
    By supraman3001 in forum Access Tables & Databases
    Replies: 1
    Last Post: 02-08-2010, 05:25 PM
  5. Replies: 1
    Last Post: 11-07-2009, 10:09 AM
  6. Splitting comma delimited numbers into new columns
    By Cicada in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 04-23-2009, 07:30 PM
  7. Splitting Date & Time into two seperate columns
    By mccrimmon in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-29-2008, 08:42 AM

Tags for this Thread

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