Extract a fixed number of characters from a cell but just full words -Formula needed

[samst]

I looked for an answer to this but didn't find what I was looking for.

I need to extract up to the first 100 characters in a cell but only up to the last full word. So if the 101th character is in the middle of a word it shouldn't extract any of that word or anything after it.

I've been trying to mess around with this formula but haven't gotten it to work right

=TRIM(LEFT(SUBSTITUTE(A1," ",REPT(" ",50)),100))

Any help would be awesome

Thanks

 

[ukmikeb]

Hi

Try :-

=LEFT(A1,FIND("##",SUBSTITUTE(A1," ","##",101-LEN(SUBSTITUTE(LEFT(A1,101)," ",""))))-1)

hth

 

[Rick Rothstein]

Give this array-entered** formula a try...

=LEFT(A1,MAX(IF(MID(A1&" ",ROW($1:$101),1)=" ",ROW($1:$101)))-1)

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

 

[samst]

This worked perfectly THANKS!!

 

[Rick Rothstein]

This worked perfectly THANKS!!

You were offered two formula solutions... we cannot tell which one you are referring to.