I was working with a client recently who had long descriptions of products within cells in Excel. To upload the descriptions to the web site, they could only use 80 characters but wanted to ensure that there wasn’t part of a word at the end of the string.
I have to confess I ended up doing this in two stages – there may be an easier way but it works!
I have input some text as shown above.
In cell B1, I have found the first 80 characters by using the LEFT function
I then copy down the formula for as many rows as there are in my worksheet.
The formula in cell C1 to cut off at the end of the last full word is rather more complicated:
=LEFT(B1,FIND(“/”,SUBSTITUTE(B1,” “,”/”,80-LEN(SUBSTITUTE(B1,” “,””))),1))
The SUBSTITUTE part of this looks for spaces and substitutes this with a / but only at the last instance of the space.
LEN looks at the length of a string.
The LEFT function then takes B1 and cuts it off at the /.
FIND returns the starting position of one text string within another text string, so in this instance looks for how many characters there are to the / then the LEFT function returns the characters up to that position.
Not terribly easy I admit but it does work!