How can I trim a Cell without cutting a Word in Half?

[Enigmafish14]

My work uses a program that only allows our product descriptions to be 50 characters. The descriptions we get are from the original manufacturers. My job (among other things) is to trim the OEM descriptions to less than 50 characters.

The catch to this is that I need to keep the "# P/CS PRICE" - where # could be anything from 2 to 9999 - that I append to the description.

Also when the description is trimmed to 50 characters I don't want it to cut a word in half. So basically it has to include all of a word or none of it.

I have a formula that allows me to trim the description down to what I need except it cuts words. If the cell doesn't end in the appended "# P/CS PRICE" the formula will just trim the description from the left. This is what I have (A1 is the description):

=IF(RIGHT(A1,10)="P/CS PRICE",SUBSTITUTE((LEFT(LEFT(A1,(LEN(A1)-16+FIND(" ",RIGHT(A1,16),1))-1),50-17+(FIND(" ",RIGHT(A1,16),1))))&(RIGHT(A1,17-(FIND(" ",RIGHT(A1,16),1))))," "," "),LEFT(A1,50))

A1= 1.5"FLUSH MNT RET/WATER BAR W ORIFICE 250 P/CS PRICE
After formula=1.5"FLUSH MNT RET/WATER BAR W ORIFI 250 P/CS PRICE

It cuts the word "ORIFICE" to "ORIFI". I'd like it to recognize that it can't include the whole word in the description so it should just omit it. Like so:

1.5"FLUSH MNT RET/WATER BAR W 250 P/CS PRICE

I have to edit 1000's of these descriptions in a day so any ideas or help would be very appreciated!

[Ron Coderre]

Not very elegant....but...maybe this?

Please Login or Register  to view this content.

[Enigmafish14]

I'm not too familiar with the INDEX function so I can't really offer advice or point out where this formula goes wrong but it does come close. This is the results of your function

1.5"FLUSH MNT RET/WATER BAR W ORIFICE 250 P/CS

when applied to

1.5"FLUSH MNT RET/WATER BAR W ORIFICE 250 P/CS PRICE

I'd like it to keep "250 P/CS PRICE" at the end and just trim all of the text before it to make the aggregate length of 50 characters (or less) but still only include whole words.

[daddylonglegs]

Try this formula

=TRIM(LEFT(A1,LOOKUP(51-IF(RIGHT(A1,10)="P/CS PRICE",LEN(TRIM(RIGHT(SUBSTITUTE(A1," "," "),25)))+1),FIND(" ",A1&" ",ROW(INDIRECT("1:"&LEN(A1))))))&IF(RIGHT(A1,10)="P/CS PRICE",TRIM(RIGHT(SUBSTITUTE(A1," "," "),25)),""))

Note: my formula doesn't display correctly and might not work as intended if you copy and paste from here, the 2nd space [" "] in each SUBSTITUTE function should be 5 spaces between quotes, see attached file

[Ron Coderre]

Somewhat similar...
If the cell will always end with: " P/CS PRICE"

Please Login or Register  to view this content.

With:
1.5"FLUSH MNT RET/WATER BAR W ORIFICE ABCDEFGHIJKL 250 P/CS PRICE
formula returns:
1.5"FLUSH MNT RET/WATER BAR W ORIFICE 250 P/CS PRICE

With:
1.5"FLUSH MNT RET/WATER BAR W ORIFICEABCDE 250 P/CS PRICE
formula returns:
1.5"FLUSH MNT RET/WATER BAR W 250 P/CS PRICE

[Enigmafish14]

Oh wow! You guys are awesome!

Both of these formulas work, as far as I can tell. The only thing different that I've noticed, is that Daddylongleg's formula has an IF statement that allows it to work with descriptions that don't end in "P/CS PRICE" but I applied one to Ron's formula and it works great too.

Thank you both for your help!

On a side note: Where did you guys learn (or suggest I go to learn) some of the more advanced formula functions like INDEX and INDIRECT?