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

Mydako

New Member
Joined
Apr 19, 2018
Messages
26
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
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Bo_Ry

Board Regular
Joined
Oct 27, 2018
Messages
128
Office Version
  1. 365
Platform
  1. Windows
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)
 
Solution

Mydako

New Member
Joined
Apr 19, 2018
Messages
26
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

Board Regular
Joined
Oct 27, 2018
Messages
128
Office Version
  1. 365
Platform
  1. Windows
ROW($A$1:$A$16) to get a sequence of No from 1-16
 

Mydako

New Member
Joined
Apr 19, 2018
Messages
26
Okay thanks, that's what I thought but wasn't 100%. Thanks again for your help.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,134
Messages
5,570,364
Members
412,320
Latest member
sixnine0312
Top