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

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,542
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
35,542
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,647
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:

Forum statistics

Threads
1,089,277
Messages
5,407,343
Members
403,135
Latest member
becca_832313

This Week's Hot Topics

Top