+ Reply to Thread
Results 1 to 13 of 13

Split delimeted text to columns Excel 2013

  1. #1
    Registered User
    Join Date
    06-21-2022
    Location
    USA
    MS-Off Ver
    2013
    Posts
    5

    Split delimeted text to columns Excel 2013

    Hello; I know this is discussed in many places, but I am just having a tough time wrapping my head around all that is involved, I am posting a real sample of the data I am trying to split.

    Using Excel 2013

    What I am looking to do is:
    Cell I4 contains "Mashed Potato, Corn Bread, Steamed Carrot, Garlic Bread"
    Need to use formula to split this into 4 separate column cell (Comma Delimited) I5, I6, I7, I8
    If there are only 3 in the Delimited text, then the forth column cell (I8) would remain blank.
    The Delimited text could be anywhere from 1 - 4 items
    If I4 only has a single item, then no comma would exist and value would go to I5.
    If I4 has 2 items then I5 & I6 would contain the 2 (I7 & I8 would be left blank)
    and so on.

    Thank you
    Elso
    Last edited by Elso1959; 06-29-2022 at 09:48 AM. Reason: forgot to mention version of Excel using

  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: Split delimeted text to columns Excel 2013

    Hello!

    Since you are using 2013... you can't work with Dynamic Arrays... so:

    Using Excel 2013:
    Please Login or Register  to view this content.
    And drag down

    Using Excel 365 (2019+):
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    06-21-2022
    Location
    USA
    MS-Off Ver
    2013
    Posts
    5

    Re: Split delimeted text to columns Excel 2013

    Thank you

    Guess I didn't do well explaining. What I need this formula to do is exactly what the "Text to columns" wizard does. All items "Mashed Potato, Corn Bread, Steamed Carrot, Garlic Bread" need to remain in the same row, I just need this string broken apart so that each value goes to a separate cell in the same row.

    I know this will be utilizing LEFT, MID, RIGHT, SEARCH, and LEN to make it happen (maybe others as well) I also suspect there would need to be error handling as well since not every record would contain all 4 items.

    Here's the whole story. I am using data validation to create a dropdown list where a choice of upto 4 items from the list (multi select). I found some VBA code that will strings together the selections made separating each with a comma (i.e. "Mashed Potato, Corn Bread, Steamed Carrot, Garlic Bread") to populate the drop-down cell. The column J, every cell in the column contains the same dropdown and is populated with up to 4 selections.

    Next thing I need to do is count the number of times Mash Potato was selected, number of times corn bread was selected and so on. I can search this column directly using COUNTIF together with wild cards which works great. But in a scenario where the list may contain both "corn" and "corn bread" searching "corn" with wild cards would also count the "corn bread" so COUNTIF with wild cards won't work.

    By breaking the string down and splitting so each choice goes to a single cell I think I can then group those 4 columns into a range and then perform the COUNT on whole cell values only.

    Hope this makes sense, I think it would be pretty much straight forward, but I can also see that it could become quite complex because we need to account for any where from zero to four selection made.

    I know there are similar threads here on the subject, I just haven't found a post that hits all the points so I can follow suit by example.

    Thank you
    Elso

  4. #4
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.56 for Mac (home)
    Posts
    7,597

    Re: Split delimeted text to columns Excel 2013

    I recommend you upload a sample workbook with what you have AND what you want (follow the directions in the yellow banner at the top of the post).
    The reason you should upload a sample workbook is because in your first post you note:

    Cell I4 contains "Mashed Potato, Corn Bread, Steamed Carrot, Garlic Bread"
    Need to use formula to split this into 4 separate column cell (Comma Delimited) I5, I6, I7, I8
    This indicates you want the results to be going down one column (column I).

    then your second post says...

    What I need this formula to do is exactly what the "Text to columns" wizard does. All items "Mashed Potato, Corn Bread, Steamed Carrot, Garlic Bread" need to remain in the same row, I just need this string broken apart so that each value goes to a separate cell in the same row.
    Which contradicts the going down the column. AND if you need it to split them across the same row, why can't you use text to columns?
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  5. #5
    Registered User
    Join Date
    06-21-2022
    Location
    USA
    MS-Off Ver
    2013
    Posts
    5

    Re: Split delimeted text to columns Excel 2013

    Yes, I see how that could have sounded confusing.
    This site seemed to have been down for a short bit so I did post my question on 2 other sites as well, only to learn about a cross post rule.
    For the record the 2 other site posts:
    excelguru can't post the URL

    and here: ozgrid can't post the URL

    I should have said "break text to cells"

    I just uploaded a file name "text split test.xlsx" but I don't see where it went when uploaded using the manage attachments link below.
    but here is a link to my upload: can't post the URL


    This is a very small piece to a rather large work book project.
    I am a chef in a retirement community and the object here is to collect and tally the resident meal choices.
    This piece of the puzzle, each resident populates a single row.
    Each resident had a data validation list of the 3 or 4 side dished for the given meal.
    Using some VBA allows this data validation to be a multi select dropdown and populated the cell with a delimited test string like in my sample.
    The next step in the project is to count how many mash potatoes, steamed carrots, corn bread...etc.
    I could count all instances in the column using wild cards, this would work unless
    the side choices included "corn" & "corn bread" This would give an inaccurate count for the corn.
    I am now thinking that the easiest error free method would be add 4 helper cells that the selections can get split to, then I retrieve my counts using absolute criteria.
    In the end, these 4 helper cells/columns would be hidden.

    Thank you for trying to understand what I'm looking for.

    Elso
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,442

    Re: Split delimeted text to columns Excel 2013

    Try this in E1:

    =TRIM(MID(SUBSTITUTE($A1,",", REPT(" ",255)),COLUMNS($E1:E1)*255-254,255))

  7. #7
    Valued Forum Contributor
    Join Date
    12-09-2014
    Location
    Trakai, Lithuania
    MS-Off Ver
    2016
    Posts
    782

    Re: Split delimeted text to columns Excel 2013

    Merge&Center-delete
    Text to Columns
    Attached Files Attached Files

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,051

    Re: Split delimeted text to columns Excel 2013

    The OP has specifically asked for a formula to replicate what Text to Columns does - see post #3.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  9. #9
    Registered User
    Join Date
    06-21-2022
    Location
    USA
    MS-Off Ver
    2013
    Posts
    5

    Cool Re: Split delimeted text to columns Excel 2013

    Hello again

    I finally figured out how to do what I was looking for in the Split text to column/cell.
    Since I had such a rough time trying to find a method/formula scenario close enough for me to adapt,
    I am uploading my sample test page again that is doing exactly what I wanted to possibly help someone else looking for a similar solution.

    Of course if someone has a cleaner ore simpler means to perform the splitting tasks my ears are open as I am no expert with Excel.

    Thanks
    Elso

    From my stand point this question can be considered solved
    Attached Files Attached Files

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,051

    Re: Split delimeted text to columns Excel 2013

    Next time you cross pist, you need to tell us: https://www.excelguru.ca/forums/show...olumns-formula

    Please read the forum rules before posting here again.

  11. #11
    Registered User
    Join Date
    06-21-2022
    Location
    USA
    MS-Off Ver
    2013
    Posts
    5

    Re: Split delimeted text to columns Excel 2013

    Sorry it would not allow me to enter URLs because I have too few postings, I tried

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,051

    Re: Split delimeted text to columns Excel 2013

    Here is the rule (which you clearly have NOT read along with the rules of all the other forums where you have done the same thing):

    3. Questions that are cross-posted to other web forums must contain links to those posts on those forums or a comment to tell us where else the question has been asked.
    There is NO excuse for your failing to tell us.

  13. #13
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,821

    Re: Split delimeted text to columns Excel 2013

    Once you get the idea of it... FILTERXML is great at parsing. Very versatile.


    =IFERROR(FILTERXML("<A><B>"&SUBSTITUTE($A1,",","</B><B>")&"</B></A>","//B["&COLUMNS($E1:E1)&"]"),"")

    copied across and down.
    Attached Files Attached Files
    Glenn



+ 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: 19
    Last Post: 02-17-2022, 04:51 PM
  2. Replies: 1
    Last Post: 02-15-2022, 06:35 PM
  3. Replies: 1
    Last Post: 05-06-2020, 07:42 PM
  4. Formula(s) to Split Text String Across Columns Using Columns as Seperator
    By WaylettChris in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-24-2016, 01:13 PM
  5. Replies: 2
    Last Post: 04-09-2014, 11:48 AM
  6. Replies: 4
    Last Post: 07-25-2013, 05:28 AM
  7. Saving an excel in tab delimeted text adding two new columns in the .txt file
    By princesouvik in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-08-2009, 02:34 AM

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