CODEWITHSUNDEEP
arraysexcelstringexcel-formulasubstring
user42459
user42459

Reputation: 915

Marking 1 if word ends with one of the suffixes

Let's say I have this.

Words             Mark                 Suffix
Happily                                ly
Emotional                              dom                              
Emotionally
Surfdom

I want to mark 1 if the word ends with some suffix in the suffix list, and 0 otherwise.

Words             Mark                 Suffix
Happily            1                   ly
Emotional          0                   dom                              
Emotionally        1
Surfdom            1

I tried lookup and vlookup but they are incomplete. How can I do this?

Upvotes: 2

Views: 241

Answers (4)

JvdV
JvdV

Reputation: 75900

I believe you are looking for COUNTIF with OR construct in itself through wildcards:

=SUMPRODUCT(COUNTIF(A2,"*"&C$2:C$3))

Or if you have O365, =SUM() instead of =SUMPRODUCT(). If you don't need to refer to column C per se, we can create our own array:

=SUM(COUNTIF(A2,{"*ly","*dom"}))

This would always give you either 1 or 0 since a string can only have one ending =)

Upvotes: 3

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

Here's an approach which shall help you extend what @NareshBhople has suggested here.

=IFERROR(LOOKUP(2,1/(RIGHT(A2,LEN($C$2:$C$3))=$C$2:$C$3)),0)

  1. Adjust the range in C column to suit criteria.

  2. Do not select whole column and

  3. Make sure that the criteria range is without blanks.

Upvotes: 3

Variatus
Variatus

Reputation: 14383

I didn't find a worksheet function to do this job but here is a UDF (User-Defined Function) that works like one.

Function Suffixed(Cell As Range) As Integer

    Dim Suff        As Variant          ' list of Suffixes
    Dim Word        As String           ' the word extracted from 'Cell'
    Dim L           As Integer          ' length of suffix string
    Dim R           As Long             ' row counter

    Word = Cell.Value
    If Len(Word) Then                   ' skip if blank (return 0)
        ' set the range in column D, starting in row 2 to the column's end
        Suff = Range(Cells(2, "D"), Cells(Rows.Count, "D").End(xlUp)).Value
        For R = 1 To UBound(Suff)
            L = Len(Suff(R, 1))
            If StrComp(Right(Word, L), Suff(R, 1), vbTextCompare) = 0 Then
                Suffixed = 1
                Exit For
            End If
        Next R
    End If
End Function

Install the code in a standard code module. That is none of the code modules that Excel sets up. You must insert it and its default name will be Module1. After that you can call the function from the worksheet with the kind of syntax you are accustomed to. For example,

=Suffixed(A2)

The function will return 1 if the word ends on a suffix in your list, else zero. You may modify the code to move the list of suffixes to where you want it. Change two instances of the column letter and one instance of the number 2 which specifies the first row of your list. 

    ' set the range in column D, starting in row 2 to the column's end
    Suff = Range(Cells(2, "D"), Cells(Rows.Count, "D").End(xlUp)).Value

The end of the list is dynamic and the code will find it. Don't leave blanks in the list.

Upvotes: 2

Naresh
Naresh

Reputation: 3034

If those three columns are A,B & C then B2 = =IF(OR(RIGHT(A2,LEN($C$2))=$C$2,RIGHT(A2,LEN($C$3))=$C$3),1,0)

Above formula gives flexibility, to put any values in C2 and C3 and mark them.

You don't have to refer to column C =IF(OR(RIGHT(A2,2)="ly",RIGHT(A2,3)="dom"),1,0)

This is not suitable if you have a large data in column C. In that case, you may need a VBA solution.

Upvotes: 3

Related Questions