How to filter symmetric words from a column of data?

user_12838019 0
Viewed 47

I have a list of words and I would like to find all symmetric words and some how put value 1 for each of them (see the picture).

enter image description here

excel
3 Answers

This does the trick:

=1*(A1=CONCAT(MID(A1,LEN(A1)-SEQUENCE(1,LEN(A1),0),1)))

It reads the string in a cell backwards using MID and SEQUENCE, and compares the CONCAT result with the original to see if it is the same, i.e. the string is symmetric.

Multiplying by 1 forces the Boolean into an integer.

symmetric

user_43619990

With Microsoft365, try:

enter image description here

Formula in B1:

=EXACT(A1,CONCAT(MID(A1,SEQUENCE(LEN(A1),,LEN(A1),-1),1)))

Formula in C1:

=--EXACT(A1,CONCAT(MID(A1,SEQUENCE(LEN(A1),,LEN(A1),-1),1)))

If you are working in a version without CONCAT() it will get significatly more verbose, but still possible:

=SUMPRODUCT(--EXACT(MID(A1,ROW(A$1:INDEX(A:A,LEN(A1))),1),MID(A1,(LEN(A1)+1)-ROW(A$1:INDEX(A:A,LEN(A1))),1)))=LEN(A1)

This, again, can be wrapped to return either 1 or 0 if you prefer that over the boolean results:

=--(=SUMPRODUCT(--EXACT(MID(A1,ROW(A$1:INDEX(A:A,LEN(A1))),1),MID(A1,(LEN(A1)+1)-ROW(A$1:INDEX(A:A,LEN(A1))),1)))=LEN(A1))
user_97581940

With VBA. This assumes that a single character is symmetric:

Public Function Sym(s As String) As Long
    Dim L As Long, L2 As Long
    Dim p1 As String, p2 As String
    
    L = Len(s)
    L2 = Int(L / 2)
    Sym = 0
    
    If L Mod 2 = 0 Then
    '   even
        p1 = Mid(s, 1, L2)
        p2 = StrReverse(Mid(s, L2 + 1))
        If p1 = p2 Then
            Sym = 1
        End If
    Else
    '   odd
        p1 = Mid(s, 1, L2)
        p2 = StrReverse(Mid(s, L2 + 2))
        If p1 = p2 Then
            Sym = 1
        End If
    End If
End Function

This will handle both an even or odd number of characters.

EDIT#1:

Simply:

Public Function Sym(s As String) As Long
    Sym = 0
    If s = StrReverse(s) Then Sym = 1
End
user_24746560
Related