+ Reply to Thread
Results 1 to 13 of 13

Splitting comma delimited numbers into new columns

  1. #1
    Forum Contributor
    Join Date
    04-19-2009
    Location
    Toulouse, France
    MS-Off Ver
    Excel 2010
    Posts
    130

    Splitting comma delimited numbers into new columns

    Bit of a newbie and therefore am not familiar with many terms so I hope this is clear.

    I am using excel at the moment with a card playing program. using the excel sheet they provided the details of what cards are dealt are exported to the worksheet and there is a simple table like so

    Player Cards
    ............................................................
    Player 1/ 24, 27, 16
    Player2/ 1, 5

    The information is fed through one number at a time as the cards are dealt for a total of three rounds sometimes it is only two rounds and are delimited by a comma all in the same column. I would like if possible to have these numbers appear in separate columns. that is

    Card 1 / Cards 2 / Card 3
    Player 1
    Player 2

    IS this possible. briefly i want this to happen so I can use the Vlookup function as the numbers that come through each stand for a card value but using Vlookup only the first number works and the following return an NA value as it is impossible as far as I know to have every possible combination represented in a table . If there is a way of tweaking Vlookup so it recognises the comma delimiter and in the vlookup column it will show all converted numbers then i'm all ears otherwise any help on how to split would be much appreciated. Quickly I did try using the text to columns function when i did this however in the new destination it showed only the first number and discontinued showing the others in the original as well. Additionally in this function the 'preview of selected data' does not show selected data but some sort of link =programme_name_card_gamecard_1 somethig like that. Sorry for the long one. Any advice would be greatly appreciated.

    Thanks
    Dan

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    HOME: Excel 2007 - WORK: Excel 2013
    Posts
    7,007

    Re: Splitting comma delimited numbers into new columns

    Text To Columns with a comma seperator sounds good to me.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Contributor
    Join Date
    04-19-2009
    Location
    Toulouse, France
    MS-Off Ver
    Excel 2010
    Posts
    130
    Hi special k unfortunatley that hasn't worked for me do you think it is because of the way the data is fed. In my post I stated that I tried that function without any success. When I press on that fucntion the steps I select are obviously text to columns then i check the delimited box then next then delimited with comma. should I have the box to the left 'treat consecutive delimiters as one' checked? not quite sure what this means and what is the 'text qualifier'? When I select the destination how should i do this as I want three indivdual columns one for card 1 one for card two and one for card three. when I use this function only the first card number for each player is fed through and that number appears in the original column and the newly created one the second or third cards are nowhere to be seen. Not sure what I am doing wrong. To be clear though perhaps this function doesn't work because all three numbers don't appear in the column instantly one is given then a round passes then another and another if needed. Advice on how to proceed will be appreciated.

    Thanks
    Dan

  4. #4
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Splitting comma delimited numbers into new columns

    Cicada,

    See the attached workbook "Arranging external data delimited by comma - SPLIT - Cicada - SDG.xls", with a command button "Split Text".


    If this does not work correctly, then post your workbook - scroll down and see "Manage Attachments".
    Have a great day,
    Stan

    Windows 10, Excel 2007, on a PC.

    If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

  5. #5
    Forum Contributor
    Join Date
    04-19-2009
    Location
    Toulouse, France
    MS-Off Ver
    Excel 2010
    Posts
    130

    Re: Splitting comma delimited numbers into new columns

    That looks great but being a newbie i can't figure out how to apply the macros to my worksheet any clues would save me a lot of time. thanks again.

    Dan

  6. #6
    Forum Contributor
    Join Date
    04-19-2009
    Location
    Toulouse, France
    MS-Off Ver
    Excel 2010
    Posts
    130

    Re: Splitting comma delimited numbers into new columns

    Further to my previous post what I am after is once the first value appears for Player 1 for it to automatically appear in another cell and the first for player 2 in separate cell and when the second value appears for it to appear in another cell (separate for each player and so on for the third. Hope i'm not asking to much.

    Regards
    Dan

  7. #7
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Splitting comma delimited numbers into new columns

    Cicada,


    Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

    Press and hold down the 'ALT' key, and press the 'F11' key.

    On the 'Insert' menu, click 'Module'.

    Copy the below code, and paste it into the Module (on the right pane).


    Please Login or Register  to view this content.

    Then run the "SplitText" macro.

  8. #8
    Forum Contributor
    Join Date
    04-19-2009
    Location
    Toulouse, France
    MS-Off Ver
    Excel 2010
    Posts
    130

    Re: Splitting comma delimited numbers into new columns

    Hi Stanley

    Sorry to bother you again. it isn't working. I'm sure it is something i am doing. I followed the instructions to the letter. The only possible deviation is that I pasted the code you provided in the pane that comes up when I clicked on add module. Not sure what right pane means. and as I'm not able to read macro code I'm not sure whether it is important where the table on my worksheet is. Player numbers come up in cell J7 and for Banker they come up in J8. Also the numbers that appear in these cells are infinitely variable although always delimited by a comma. thanks again for your time and trouble.

    Regards
    Dan

  9. #9
    Forum Contributor
    Join Date
    04-19-2009
    Location
    Toulouse, France
    MS-Off Ver
    Excel 2010
    Posts
    130

    Re: Splitting comma delimited numbers into new columns

    Just one more thing

    Additionally when i click on J7 in the fx field it says =XFeeder|BACCARAT_STANDARD!BACCARAT_STANDARD_Cards1
    in J8
    =XFeeder|BACCARAT_STANDARD!BACCARAT_STANDARD_Cards2

    Thanks Stan.

    Dan

  10. #10
    Forum Contributor
    Join Date
    04-19-2009
    Location
    Toulouse, France
    MS-Off Ver
    Excel 2010
    Posts
    130

    Re: Splitting comma delimited numbers into new columns

    If it is a worksheet of the program perhaps I need admin permission to change it. Does adding new macro usually require this.

    Regards
    Dan

  11. #11
    Forum Contributor
    Join Date
    04-19-2009
    Location
    Toulouse, France
    MS-Off Ver
    Excel 2010
    Posts
    130

    Re: Splitting comma delimited numbers into new columns

    If I run it it separates the values that are there but it discontinues refreshing and just shows those separated numbers for the remainder and doesn't display new ones in the following rounds. I must be doing something wrong.

    Dan

  12. #12
    Forum Contributor
    Join Date
    04-19-2009
    Location
    Toulouse, France
    MS-Off Ver
    Excel 2010
    Posts
    130

    Re: Splitting comma delimited numbers into new columns

    Using the split function I would like to separate comma delimited numbers in cells j7 and j8 would that change the above macro

    Thanks
    Dan

  13. #13
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Splitting comma delimited numbers into new columns

    Cicada,

    Post your workbook - scroll down and see "Manage Attachments".

+ 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