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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Bo_Ry

Board Regular
Joined
Oct 27, 2018
Messages
179
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
179
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,800
Messages
5,772,305
Members
425,752
Latest member
mmurphy13

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top