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:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,381
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
33,381
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,558
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,084,744
Messages
5,379,581
Members
401,614
Latest member
priokatm

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...
Top