Formula to extract text only up to 25 characters

Question

Hello

I just asked a question but it seems that I made a mess of it. Below is another attempt.

I am looking for a formula in cell B1 which can extract the value from cell A1 but only upto 25 characters comprising of complete words.

For example the value in cell A1 is

10 best home based businesses

Now the length is 29 characters. The answer that I would want in cell B1 is

home based businesses

Meaning that the length is 25 or less and only complete words are displayed.

Would prefer to count the 25 characters from right.

I hope that this makes sense.

Answer

Neil,

put this in cell B1 and enter it with Ctrl+shift+enter rather than just Enter since this is an array formula:

=RIGHT(A1,LEN(A1)-SMALL(IF((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=" ")*(IF(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=" ",ROW(INDIRECT("1:"&LEN(A1))))>(LEN(A1)-25)),IF(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=" ",ROW(INDIRECT("1:"&LEN(A1))))),1))

I tested it with your data and it worked for me when entered as an array formula.