Split into smaller Shorter Descriptions.

[[email protected]]

Good day, Gurus.

See attached sample workbook. In real life I have several hundred rows of these long descriptions, and they need to be broken down to 69 characters or less because that is the character limit in the software I am importing the descriptions into. I have 3 fields available that I am importing into, but each field must be less than 69 characters.

I need to take this one column and split it into 3 columns of no more than 69 characters each. However, the complicated part is I’d like to be able to do so where the split occurs as close to but not more than 69 characters, while at the same time not cutting a word in half. Is there a formula that would do this?

[NBVC]

Try:

In B2:

Please Login or Register  to view this content.

in C2:

Please Login or Register  to view this content.

in D2:

Please Login or Register  to view this content.

each copied down.

[TMS]

One way:

B2: =LEFT(A2,FIND(" ",A2,LEN(A2)/3))
C2: =MID(A2,FIND(" ",A2,LEN(A2)/3)+1,FIND(" ",A2,2*(LEN(A2)/3))-FIND(" ",A2,LEN(A2)/3))
D2: =MID(A2,FIND(" ",A2,2*LEN(A2)/3)+1,LEN(A2)/3)

and copy down.

This breaks the text into roughly thirds based on the length of the original text. Otherwise, it's a cas of taking an arbitrary number less than 69 and looking for a space ... which may or may not work.

Regards, TMS

[[email protected]]

It's been a long time NBVC. I hope all is going well for you.

This works beautifully. You are a genius!

This is SOLVED. Have a good one.