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

T

tgmind

New member
Joined
Mar 13, 2013
Messages
5
Reaction score
0
Points
0
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.
 
Can you post up some typical varying examples and what the outcome should look like?
 
NBVC said:
Can you post up some typical varying examples and what the outcome should look like?
Click to expand...

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.

 
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....
 
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!!!!
 
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)," ",""))))))
 
I am so impressed! How can I repay you for your help? You really saved me!
 
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 WrenchWiha Open End#VALUE!#VALUE!

Thanks again for your help.
 
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.
 
You must log in or register to reply here.
Back
Top