Share via

Like operator in Excel

Anonymous
2011-03-16T21:32:15+00:00

Hi - I am trying to translate an Access Expression with .... LIKE " *" .... into Excel.  For example. 

 =iif(RIGHT([FULLNM],2) LIKE " *","TRUE","FALSE").  in english it is if Right 2 chars of fullnm contains a blank and any letter then write true esle write false. 

Can this be done in Excel function with islike or something ?? please help?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2011-03-16T21:52:01+00:00

Hi - I am trying to translate an Access Expression with .... LIKE " *" .... into Excel.  For example. 

 =iif(RIGHT([FULLNM],2) LIKE " *","TRUE","FALSE").  in english it is if Right 2 chars of fullnm contains a blank and any letter then write true esle write false. 

Can this be done in Excel function with islike or something ?? please help?

Try this formula:

=IF(LEFT(RIGHT(A1,2))=" ","TRUE","FALSE")

Hope this helps / Lars-Åke

Was this answer helpful?

3 people found this answer helpful.
0 comments

Answer accepted by question author

Anonymous
2011-03-16T21:59:05+00:00

This assumes that the last character is only a letter:

=IF(AND(LEFT(RIGHT(A1,2),1)=" ",RIGHT(A1,1)<>" "),TRUE,FALSE)

Was this answer helpful?

1 person found this answer helpful.
0 comments

10 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2011-03-16T23:02:07+00:00

    Hi,

    Try this

    =IF(AND(MID(D6,LEN(D6)-1,1)=" ",ISERROR(1*RIGHT(D6,1))),TRUE,FALSE)

    D6 contains FULLNM

    Was this answer helpful?

    0 comments
  2. Anonymous
    2011-03-16T22:19:45+00:00

    This assumes that the last character is only a letter:

     

    =IF(AND(LEFT(RIGHT(A1,2),1)=" ",RIGHT(A1,1)<>" "),TRUE,FALSE)

    On my system, that returns TRUE for the string "te 2"  where the last character is a non-letter.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2011-03-16T22:17:47+00:00

    I'm surprised the wild-card syntax is so different in Access.

    In Excel, and in VBA, the "*" means any number of any type of character; and not just a single letter.  But since you want a space, and then a single letter, there is no "easy" Excel translation.

    In VBA, your formula would be something like: 

       IIf(Right(IFULLNM, 2) Like " [A-Za-z]", True, False)

    In Excel, with IFULLNM in A1, something like the following should work:

    =AND(UPPER(RIGHT(A1,1))>="A",UPPER(RIGHT(A1,1))<="Z",ISNUMBER(SEARCH(" ?",RIGHT(A1,2))))

    Was this answer helpful?

    0 comments