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

#### Mydako

##### New Member
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

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
=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

##### New Member
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
ROW(\$A\$1:\$A\$16) to get a sequence of No from 1-16

#### Mydako

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

Replies
14
Views
1K
Replies
1
Views
793
Replies
1
Views
4K
Replies
2
Views
637
Replies
6
Views
467

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.

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.

### Which adblocker are you using?

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

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