Limiting column width and not break up words
Posted by Steve C on September 13, 2001 1:24 PM
I have a spreadsheet that contains all of our products descriptons. I am wanting to import it into our system but it limits the description fields to 25 characters, it has two description fields. I figured out how to get the first 25 characters into one column and the rest into another. The problem is that it breaks up words making the description difficult to understand. ex. Desc 1 Desc2
CAULK SILICONE WHITE 10 O Z
In this case it broke up "OZ".. I was wondering if anyone knew of a way to strip off 25 characters but not break up any words... thanks
Posted by IML on September 13, 2001 2:29 PM
Not completely tested, but try this
I'm assuming you have the full name in cell a1. You could concecate your values to get here is needed.
First, enter the numbers 1-25 somewhere in your workbook. Name it "counter" via the name box.
For the first 25 letters at a word break, use the formula
=IF(LEN(A1)<26,A1,LEFT(A1,MAX((MID($A$1,counter,1)=" ")*counter)))
this is an array formula, which means hit enter only when the control and shift key are depressed. For this example, assume this is cell B1.
The last portion could be capture be the formual
=RIGHT(A1,LEN(A1)-LEN(b1))
This will not work if your description is one word over 25 characters
good luck
