Results 1 to 10 of 10

Thread: Split long cell of text into smaller cells without chopping words?

  1. #1

    Split long cell of text into smaller cells without chopping words?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Thanks so much if anyone can help me.

    I have a 5000 line spreadsheet of product descriptions that are 6 to 8 inches long. I need to split the data into cells that are only 3 inches wide and effectively split the text into 2 or three cells depending on text length. I've been able to figure out how to do it but it will chop the words up where it splits.

    Does anyone know how to do this without chopping the words that end up at the split? Meaning, if the entire word cannot fit in the cell then it will be moved to the next cell.

    Thanks again for any help.

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Can you post up some typical varying examples and what the outcome should look like?


  3. #3
    Quote Originally Posted by NBVC View Post
    Can you post up some typical varying examples and what the outcome should look like?
    Hi Thanks for the reply. So I export my product descriptions into csv file which I open in excel and the text will go into one cell. Examples of each cell are:

    "Wiha 10891 ESD Safe Slotted and Phillips Screwdriver Blade Pouch Set"
    "
    Knipex 11-05-160 6.3" Insulation Strippers - Chrome w/ MultiGrip"

    I need to break this text up into smaller chunks while still keeping each word intact so after I split it into multiple cells it might look like:
    "Wiha 10891 ESD Safe Slotted - and Phillips Screwdriver - Blade Pouch Set"
    "
    Knipex 11-05-160 6.3" Insulation - Strippers - Chrome w/ - MultiGrip"

    With each section of text being in its own cell. Currently the only solution I can find id to limit the cell size but that will break up the words that fall at the end of the cell like this
    "Wiha 10891 ESD Safe Slott - ed and Phillips Screwdriver B - lade Pouch Set"

    Thanks so much for any help.


  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Assuming first string is in A1, try these in B1 : D1, respectively:

    =TRIM(LEFT(LEFT(A1,30),FIND("^^",SUBSTITUTE(LEFT(A1,30)," ","^^",LEN(LEFT(A1,30))-LEN(SUBSTITUTE(LEFT(A1,30)," ",""))))))

    =TRIM(LEFT(LEFT(TRIM(SUBSTITUTE(A1,B1,"")),30),FIND("^^",SUBSTITUTE(LEFT(TRIM(SUBSTITUTE(A1,B1,"")),30)," ","^^",LEN(LEFT(TRIM(SUBSTITUTE(A1,B1,"")),30))-LEN(SUBSTITUTE(LEFT(TRIM(SUBSTITUTE(A1,B1,"")),

    =TRIM(SUBSTITUTE(A1,B1&" "&C1,""))

    each copied down... this assumes you won't pass 90 characters....


  5. #5
    WOW! Thats amazing! The first formula worked but this one gives an error "missing a paranthesis"

    =TRIM(LEFT(LEFT(TRIM(SUBSTITUTE(A1,B1,"")),30),FIND("^^",SUBSTITUTE(LEFT(TRIM(SUBSTITUTE(A1,B1,"")),30)," ","^^",LEN(LEFT(TRIM(SUBSTITUTE(A1,B1,"")),30))-LEN(SUBSTITUTE(LEFT(TRIM(SUBSTITUTE(A1,B1,"")),

    Can you double check it please.
    THANK YOU SO MUCH!!!!

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Sorry, I guess I only copied part of the formula:

    =TRIM(LEFT(LEFT(TRIM(SUBSTITUTE(A1,B1,"")),30),FIND("^^",SUBSTITUTE(LEFT(TRIM(SUBSTITUTE(A1,B1,"")),30)," ","^^",LEN(LEFT(TRIM(SUBSTITUTE(A1,B1,"")),30))-LEN(SUBSTITUTE(LEFT(TRIM(SUBSTITUTE(A1,B1,"")),30)," ",""))))))


  7. #7
    I am so impressed! How can I repay you for your help? You really saved me!

  8. #8
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    The gratitude is payment enough. Thanks


  9. #9
    I feel terrible asking for more help but I've discovered the formula is not quite working.

    The issue is when the text in A1 is less than 30 characters.
    In this instance B1 will have the same text as A1 except the last word is missing
    and C1 and D1 have #VALUE!

    Wiha Open End Wrench Wiha Open End #VALUE! #VALUE!

    Thanks again for your help.

  10. #10
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Try changing first formula to:

    =IF(LEN(A1)<=30,A1,TRIM(LEFT(LEFT(A1,30),FIND("^^",SUBSTITUTE(LEFT(A1,30)," ","^^",LEN(LEFT(A1,30))-LEN(SUBSTITUTE(LEFT(A1,30)," ","")))))))

    and second formula to:

    =IF(LEN(TRIM(SUBSTITUTE(A1,B1,"",1)))<=30,TRIM(SUBSTITUTE(A1,B1,"",1)),TRIM(LEFT(LEFT(TRIM(SUBSTITUTE(A1,B1,"",1)),30),FIND("^^",SUBSTITUTE(LEFT(TRIM(SUBSTITUTE(A1,B1,"",1)),30)," ","^^",LEN(LEFT(TRIM(SUBSTITUTE(A1,B1,"",1)),30))-LEN(SUBSTITUTE(LEFT(TRIM(SUBSTITUTE(A1,B1,"",1)),30)," ","")))))))

    third formula remains untouched.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •