#Excel Identifying if a String is the Anagram of Another String Using the TEXTJOIN Function by David Hager

Using Excel native formulas to identify anagrams is quite rare. I could find only one example.

https://excelxor.com/2014/08/30/advanced-formula-challenge-2-results-and-discussion/

The methodology for comparing strings as anagrams starts with these formulas, which use B3 and C3 as the locations of the strings.

CodeString1=CODE(MID(UPPER(Sheet1!$B$3),ROW(INDIRECT(“1:”&LEN(Sheet1!$B$3))),1))

CodeString2=CODE(MID(UPPER(Sheet1!$C$3),ROW(INDIRECT(“1:”&LEN(Sheet1!$C$3))),1))

Each of these formulas returns an array of numbers corresponding to the character code of the letters in the string (converted to all UPPER). Why make an array of numbers? So that they can be sorted. So, the next formulas:

CodeStr1Sorted=SMALL(CodeString1,ROW(INDIRECT(“1:”&LEN(B3))))

CodeStr2Sorted=SMALL(CodeString1,ROW(INDIRECT(“1:”&LEN(C3))))

create an array of numbers that are sorted from smallest to largest. So, if both strings contain the same number and frequency of letters, the arrays will be identical. The TEXTJOIN function is used here to create strings from those arrays for comparison.

NumberStr1=TEXTJOIN(“”,,CodeStr1Sorted)

NumberStr2=TEXTJOIN(“”,,CodeStr2Sorted)

The formula indicate that the string in C3 is an anagram of the string in B3 is shown below.

=EXACT(NumberStr1,NumberStr2)

Note that this solution unfortunately does not include an Excel native formula or grouping of formulas to determine whether a string is an actual word (not possible). So, although it is not required, a VBA worksheet function is included here as part of the solution. You can look at the code here. WordPress.com will not allow me to publish an .xlsm file. So, please add a module in the VBE (Alt-F11) and copy/paste this function procedure into the module. When you save it, you will have to save it as an .xlsm file.

Public Function IsWord(ByRef Text As String) As Boolean

Dim wd As Object

Application.Volatile True

On Error Resume Next

Set wd = GetObject(, “Word.Application”)

If Err.Number <> 0 Then

Set wd = CreateObject(“Word.Application”)

If Err.Number <> 0 Then

Set wd = GetObject(, “Word.Application”)

End If

End If

IsWord = wd.CheckSpelling(Text)

Set wd = Nothing

End Function

 

Here is the resulting formula to determine whether both strings are words and anagrams.

=AND(IsWord(B3),IsWord(C3),EXACT(NumberStr1,NumberStr2))

Remember, you need the correct version of Excel 2016 (Office 365) in order to use the TEXTJOIN function.

You can download the file here.

Textjoin_Anagram