+ Reply to Thread
Results 1 to 5 of 5

Moving string of numbers with commas to separate columns

  1. #1
    Forum Contributor
    Join Date
    08-16-2009
    Location
    Sheffield
    MS-Off Ver
    Excel 2010
    Posts
    109

    Moving string of numbers with commas to separate columns

    Hi,

    I have a column of data in Excel that is shown as below lets say from cells A2:A10, I have tried a simple 'IF' formula but failed as I wanted to have 14 columns to the right of A1 starting at B2 that had the 14 headers ( 1 -14) and then if A2 had 1,2,3,6,7,11 I wanted B2, C2, D2, G2, H2, L2 to show 'True' or 'x' if no match.

    1,2,3,6,7,11
    3,13
    1,2,3,5,6,7,11,12
    1,2,3,8
    1,8
    1,2,3,7
    1
    11
    14

    Many thanks in advance
    Garry

  2. #2
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Moving string of numbers with commas to separate columns

    This would be pain to do using just the regular formula.

    Since you'll need a way to differentiate between "1," & "11," etc.
    Both string contain "1," but only "1," should be true for check against B1.

    I can't think of ways to do it in single cell nested formula off top of my head.

    If you have access to PowerQuery/Get&Transform. This could be done using Text.Split() and List.Contains().

    Alternately, you could write UDF using VBA (Either comparison using RegExp pattern matching or Split() and iteration).
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  3. #3
    Forum Contributor
    Join Date
    10-30-2003
    Location
    Singapore
    MS-Off Ver
    Excel 2019
    Posts
    197

    Re: Moving string of numbers with commas to separate columns

    Try,

    In B2, copied across to O2 and all copied down :

    =IF(ISNUMBER(FIND(","&B$1&",",","&$A2&",")),"x","")

    Regards

  4. #4
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Moving string of numbers with commas to separate columns

    Ah! Didn't think to concatenate to start and end of existing string. Great way to deal with partial match issue.

  5. #5
    Forum Contributor
    Join Date
    08-16-2009
    Location
    Sheffield
    MS-Off Ver
    Excel 2010
    Posts
    109

    Thumbs up Re: Moving string of numbers with commas to separate columns

    Hi Bosco,

    Brilliant, this works great and many thanks.

    Garry

+ 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. Replies: 2
    Last Post: 04-16-2019, 07:55 AM
  2. Macro to separate long string of characters and several spaces into separate columns
    By taylorsm in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-31-2017, 07:37 PM
  3. [SOLVED] Formula to separate Values by Commas in different columns
    By prkhan56 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 12-19-2015, 01:34 PM
  4. [SOLVED] Hi, I need the formula to separate the numbers with commas to other columns as shows below
    By binoos123 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-21-2013, 05:36 AM
  5. [SOLVED] 2 separate columns with numbers separated by commas and single numbers not, need sum
    By Slothbob in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-22-2013, 04:33 PM
  6. [SOLVED] Values separated by Commas - want in separate columns, cells from original cell
    By schmidt62 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-03-2012, 02:50 PM
  7. Separate string by commas
    By antoni in forum Excel General
    Replies: 2
    Last Post: 10-03-2009, 01:51 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