Combining Left/Right and Find (?) - Extracting Text Before the Last Occurrence of a Character Within a Character Limit

[Mydako]

Hello all,

Hopefully someone's bigger brain can help with my current challenge.

I'm looking for a way to return a text value under a certain character limit, but I don't want it to chop it off just anywhere, I'd like it to stop at the first space before the character limit.

Example:
Cell C1 contains : Once upon a midnight dreary

Now I have a character limit of 15 characters, which if I use =left(C1,15) returns : Once upon a mid

Instead of that I'd like it to stop at the last space before that 15 character limit, so it doesn't cut a word in half and would return : Once upon a

Any insight or advice would be greatly appreciated.

ETA: Bonus points if you know a way to get it to work within a VLOOKUP, so in this example if the VLOOKUP would return C1 (Once upon a midnight dreary) it would return Once upon a

 

[Bo_Ry]

Please try
=LEFT(C1,MATCH(2,INDEX(1/(MID(C1,ROW($A$1:$A$16),1)=" "),))-1)

ETA
=LEFT(Vlookup() ,MATCH(2,INDEX(1/(MID(Vlookup() ,ROW($A$1:$A$16),1)=" "),))-1)

 

[Mydako]

Thank you Bo_Ry, that works perfectly!

Can you briefly explain what the $A$1:$A$16 is there for? It doesn't look like having anything in A1:A16 causes an issue, but I'm not sure on why it's there. My guess is it has to do with the 15 character limit because it's 1-16, then minus 1 at the end of the formula.

 

[Bo_Ry]

ROW($A$1:$A$16) to get a sequence of No from 1-16

 

[Mydako]

Okay thanks, that's what I thought but wasn't 100%. Thanks again for your help.