Microsoft Excel – first x characters of a text string but cutting off at full word

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

=LEFT(A1, 80)

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!

Find out more information about our Microsoft Excel training in the London area or our Microsoft Excel training in Wiltshire, Dorset, Somerset and Hampshire.

About jdonbavand

I am a trainer of Microsoft Office, Microsoft Project and Crystal Reports. I have called my blog "If Only I'd Known That...." because I hear it so many times in training sessions. In fact, if only I had a £100 for every time someone says "If only I'd known that." ....
This entry was posted in Excel 365, Microsoft Excel, Microsoft Excel 2010, Microsoft Excel 2013, Microsoft Excel 2016 and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s