Results 1 to 5 of 5

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

  1. #1
    New Member
    Join Date
    Feb 2006
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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 by malaki1974; Jul 26th, 2019 at 12:51 PM.

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,265
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

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

    How about
    =AND(LEFT(TRIM(C1))=A1,RIGHT(TRIM(C1))=B1)
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Feb 2006
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Perfect! I assume the AND is the IF/BOOL so that is why I didn't need them...

    Thank you!

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,265
    Post Thanks / Like
    Mentioned
    471 Post(s)
    Tagged
    47 Thread(s)

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

    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.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,248
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

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

    Here is another formula that should work...

    =REPLACE(TRIM(C1),2,LEN(TRIM(C1))-2,"")=A$1&B$1
    Last edited by Rick Rothstein; Jul 26th, 2019 at 02:11 PM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •