+ Reply to Thread
Results 1 to 13 of 13

Splitting multiple entries in single cell into multiple columns

  1. #1
    Registered User
    Join Date
    01-15-2013
    Location
    Newcastle Upon Tyne, England
    MS-Off Ver
    Excel 2003
    Posts
    8

    Post Splitting multiple entries in single cell into multiple columns

    Good afternoon all,

    This is my first post as I am new to the forum

    I am looking to split multiple different entries in a single cell into multiple columns and repeat this for all rows

    Example (I have the below in a single Cell as column headers)
    NCM Server Mgmt VLAN Site ID

    Next Line down is the data (Each row in a single cell)
    Enabled 10.10.10.0 50 TEST SITE 1
    Enabled 10.10.20.0 50 TEST SITE 2
    Disabled 10.10.30.0 50 TEST SITE 3


    I have very little experience with Excel and VBA as this is not my normal job (Network Engineer) but this would help me out loads if I could acheive this as I have a number of projects where this would become useful

    I know you can use delimeters but with spaces between the values I just can't fathom a way forward


    Any assistance appreciated


    Thanks
    David

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,726

    Re: Splitting multiple entries in single cell into multiple columns

    Does the file attached work?
    Attached Files Attached Files

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    23,534

    Re: Splitting multiple entries in single cell into multiple columns

    Highlight the column with the data in (A ?) by clicking on the column identifier. Then do CTRL-H (Find and Replace) and:

    Find What: SITE<space>
    Replace with: SITE_
    Click Replace All

    Then CTRL-H again, and:

    Find What: TEST<space>
    Replace with: TEST_
    Click Replace All

    Then click on Data | Text to Columns, then choose delimited, Next, Space, Next, then Finish.

    Finally, you can highlight column D and use CTRL-H to replace the underscores with a <space>. Your header might be slightly misaligned because of "Server Mgmt".

    Hope this helps.

    Pete

  4. #4
    Registered User
    Join Date
    01-15-2013
    Location
    Newcastle Upon Tyne, England
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Splitting multiple entries in single cell into multiple columns

    Thanks Very much Bebo, this does work and is definitely on the lines of what I am aiming for, however some of the results are not quite as expected:

    My apologies. Where I have TEST SITE 1 this needs to be in the same cell for example, Newcastle Road

    Also, I didn't want to put all of the column headers in my initial posting, but there are roughly the same amount of column headers again. Would I be able to amend your formulae for use with the additional column headers easy enough?


    Many thanks for all your help

    David

  5. #5
    Registered User
    Join Date
    01-15-2013
    Location
    Newcastle Upon Tyne, England
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Splitting multiple entries in single cell into multiple columns

    My apologies Pete, I got an email alert from Bebo and didn't see your reply.

    I have used your method and it has worked nicely for a single row i.e. the column headers,but what I ned to do now is split the data under each of these column headers into the individual cells under the said column header. The values are alldifferent with spaces withing the data within the cells.

    Any ideas how this could be achieved? I am looking for something genericideally so that I can apply a formula no matter what the data is within the Cells. I'm probably being optimistic


    Thanks again

    David

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    23,534

    Re: Splitting multiple entries in single cell into multiple columns

    Hi David,

    it would help if you could attach a workbook with some test data in, so that we can see the variations in data that you encounter. The FAQ describes how to attach a workbook to one of your posts - basically, click on Go Advanced while in Edit mode, then scroll down to Manage Attachments, then Add Files, Select Files, (navigate to your file and double-click it), Upload Files, then Done and Submit Reply.

    Hope this helps.

    Pete

  7. #7
    Registered User
    Join Date
    01-15-2013
    Location
    Newcastle Upon Tyne, England
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Splitting multiple entries in single cell into multiple columns

    Thanks Pete, the data really is random.

    I have attached a dummy workbook with around half of the column headers and some random default data representing what I am actually looking at.


    Thanks for your help

    David
    Attached Files Attached Files

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    23,534

    Re: Splitting multiple entries in single cell into multiple columns

    It looks like the data has already been split out into separate columns on that file - can you send an example where it is all in one cell per row?

    Cheers,

    Pete

  9. #9
    Registered User
    Join Date
    01-15-2013
    Location
    Newcastle Upon Tyne, England
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Splitting multiple entries in single cell into multiple columns

    Apologies, that's how it is supposed to look when

    Please see attached, thanks
    Attached Files Attached Files

  10. #10
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    23,534

    Re: Splitting multiple entries in single cell into multiple columns

    How many different sites do you have? Those are the things which will cause the problems as they have extra spaces in them which we need to change to something else. It is possible to list the addresses in a lookup table and then get them changed to something not containing spaces, eg

    1 High Street, Manchester

    gets changed to:

    1_High_Street,_Manchester

    so that we can then use <space> as the delimiter.

    Hope this helps.

    Pete

  11. #11
    Registered User
    Join Date
    01-15-2013
    Location
    Newcastle Upon Tyne, England
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Splitting multiple entries in single cell into multiple columns

    Hi Pete,

    I have no problem with us changing the values from 1 High Street, Manchester to 1_High_Street,_Manchester but there are hundreds of unique site locations and other columns have multiple words with spaces also.

    Cheers,
    David

  12. #12
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    23,534

    Re: Splitting multiple entries in single cell into multiple columns

    I think in that case your real data is much more complex than the example you set out in Post #1, so Data | Text to Columns after Find & Replace a few times is probably not the appropriate method to use.

    Pete

  13. #13
    Registered User
    Join Date
    01-15-2013
    Location
    Newcastle Upon Tyne, England
    MS-Off Ver
    Excel 2003
    Posts
    8

    Re: Splitting multiple entries in single cell into multiple columns

    Thanks for your help anyway Pete, I may be able to extract the information from the original backend database which will retain the spacing and columns. Thanks again for the effort

    David

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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