+ Reply to Thread
Results 1 to 8 of 8

Splitting a cell into two, dependant on number of characters in cell

  1. #1
    Registered User
    Join Date
    02-14-2009
    Location
    Wicklow, Ireland
    MS-Off Ver
    Excel 2007
    Posts
    4

    Splitting a cell into two, dependant on number of characters in cell

    Hi All
    I hope that my following query will be clear. But if not, please excuse me I am new to Excel (well relatively!) and I'll try to be as straight forward as I can.

    I have a column which contains 2500 cells. In each of these cells lies a description of an item. Each cell contains a max of 50 Characters (including spaces etc). 90% of these cells have less than 50, and 10% have exactly 50. I've just been told that instead of 1 cell of 50 characters, I should have 2 cells, each containing a max of 25 characters.

    Now my problem is, I've already spent a huge amount of time editing the cells already and I don't want to have to go down through each cell and re-editing them in order to create two cells!

    Also I tried the "Text To Column" Function. And while in theory this works, it means that it separates the cell at exactly 25 characters and anything beyond these 25 characters in put into the second cell. My problem with this is that it also chops my words up.


    So i want to be able to create two cells, while also preseving entire words. So if that means that one cell must have 20 characters, adn the other 25, then so be it. As each will still contain less than or equal to 25...and most importantly the words are intact.

    I hope I'm making sense.

    I just think that there must be something that can be done to make Excel do this for me, rather than me having to manually edit 2500 cells of data.

    Also if I can Excel to do it for 90% of the cells, i don't mind having to go in and edit 10% myself. I would except there'll be a few cells that it can't be done in, if there are the full 50 characters used, it would be lucky to have them separated at exactly 25 characters, and not breaking up any words.

    Any help / suggestions/ prayers are appreciated!!


  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Splitting a cell into two, dependant on number of characters in cell

    This is quick and dirty but should help.

    Assumes text in A1:A10.
    Information outputed will be in B1:B10 and C1:C10
    If the second part of the split is longer than 25 characters nothing is put out.

    Please Login or Register  to view this content.
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    02-14-2009
    Location
    Wicklow, Ireland
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Splitting a cell into two, dependant on number of characters in cell

    Hi Andy
    thanks for your reply. Where exactly do I put that code?

    Sorry, seems like a dumb question, but I've no idea. thanks

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Splitting a cell into two, dependant on number of characters in cell

    with your workbook open,
    ALT+F11 will take you to the VBE (visual basic editor)
    You should have a project window displayed with your workbook listed in it, probably called VBProject.
    Open that and then via the menu insert a code module.
    You can then post that code in the code module.

    To run you can use the normal macro dialog to select the routine and run it.

  5. #5
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Splitting a cell into two, dependant on number of characters in cell

    if 25 characters from 50 is mid word then if you split at end of word or beggining of word

    one or other must have more than 25 characters
    take
    "this is just a test but what if this is split out"
    splitting this maintining whole words is either 23/26 or 28/21

  6. #6
    Registered User
    Join Date
    02-14-2009
    Location
    Wicklow, Ireland
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Splitting a cell into two, dependant on number of characters in cell

    hi Andy
    F11 brings me to 'charts'

    Is it different for Excel 07?
    Thanks

  7. #7
    Registered User
    Join Date
    02-14-2009
    Location
    Wicklow, Ireland
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Splitting a cell into two, dependant on number of characters in cell

    its ok, i found the 'devlopers' tab..i had to add it using excel options though, wasn't there before. ok i'm gonna give your code a shot now.
    tks

  8. #8
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Splitting a cell into two, dependant on number of characters in cell

    F11 did and does create a chart sheet, which is why I said ALT+F11

+ 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