Check first & last letter from 2 columns against a column of words

malaki1974

New Member
Joined
Feb 14, 2006
Messages
38
Hello,

I'd like to check if a word contains matching first/last letters from 2 cells. I can't quite get it to work. In fact, it doesn't work at all - I over sold 'quite'.

Eventually I will check MANY words for MANY different first/last matches so I want to automate obviously.

Example data, example code below.

A B C
First Letter Last Letter Possible Words
L N Learn
Loon

AND(IF(LEFT(TRIM(C1),1)=A1,IF(RIGHT(TRIM(C1),1)=B1)))

Again, I will eventually run this with different words per a/b like loon, lean, loren, etc. and then c/e for choose, chase etc.

Thank you for the help/direction.
 
Last edited:

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,555
Office Version
365
Platform
Windows
How about
=AND(LEFT(TRIM(C1))=A1,RIGHT(TRIM(C1))=B1)
 

malaki1974

New Member
Joined
Feb 14, 2006
Messages
38
Perfect! I assume the AND is the IF/BOOL so that is why I didn't need them...

Thank you!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,555
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
This LEFT(TRIM(C1))=A1 will return either TRUE or FALSE so no need for the If functions.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,807
Office Version
2010
Platform
Windows
Here is another formula that should work...

=REPLACE(TRIM(C1),2,LEN(TRIM(C1))-2,"")=A$1&B$1
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,095,337
Messages
5,443,895
Members
405,256
Latest member
ukboyme

This Week's Hot Topics

Top