Extracting Foreign-Language Characters

Wonderful!

As noted in my first comment below, Excel 2021's UNIQUE function is not case-sensitive. An equivalent using REDUCE, LAMBDA, and VSTACK is case-sensitive, but those functions currently require Excel 365.nMy Excel Toolbox now includes the following function to return unique rows or columns from a cell range (contiguous) or array constant or result of an array function:n    =UniquePlus(RangeArray, [LeftRight], [ExactlyOnce], [HasHeader], n        [CaseSensitive])nThe first three parameters match Excel's UNIQUE function.nWhen HasHeader is TRUE, the first row (LeftRight=FALSE) or first column (LeftRight=TRUE) is returned even if it is not unique.nCase-sensitive comparisons are done when CaseSensitive is TRUE.nThis function does not require Excel 2021 or newer.nSee https://sites.google.com/view/MyExcelToolbox

The Tip's ForeignChars function returns an unsorted string of characters; for examplen    ëÄäéÉËnThe result will be case-sensitive as illustrated in that example if the function is in a module with the defaultn    Option Compare BinarynBut if the module begins with n    Option Compare Textnthe result will not be case-sensitive and the previous example would simply returnn    ëÄénThe following version returns a sorted case-sensitive comma-separated result for that example like thisn    Ä, É, Ë, ä, é, ënregardless of Option Compare:nnFunction ForeignChars2(ByVal Target As Range) As Stringn'ASCII is 0 to 127; non-ASCII is "foreign"n    Dim Chars As New Collection, char As Stringn    Dim cell As Range, n As Integer, k As Integern    Const COMP As Integer = vbBinaryCompare 'case-sensitiven    If Target.Cells.Count > 1 Thenn        Set Target = Application.Intersect(Target.Parent.UsedRange, Target)n    End Ifn    For Each cell In Targetn        If WorksheetFunction.IsText(cell) Thenn            For n = 1 To Len(cell)n                char = Mid(cell, n, 1)n                If AscW(char) > 127 Then 'non-ASCIIn                    If Chars.Count = 0 Thenn                        Chars.Add char 'add firstn                    ElseIf StrComp(char, Chars(Chars.Count), COMP) > 0 Thenn                        Chars.Add char 'add char > Chars(Chars.Count)n                    Elsen                        For k = 1 To Chars.Countn                            Select Case StrComp(char, Chars(k), COMP)n                            Case 0 'already added char = Chars(k)n                                Exit Forn                            Case Is < 0 'add char < Chars(k)n                                Chars.Add char, , k 'before Chars(k)n                                Exit Forn                            End Selectn                        Next kn                    End Ifn                End Ifn            Next nn        End Ifn    Next celln    If Chars.Count = 0 Then ForeignChars2 = "none": Exit Functionn    ForeignChars2 = Chars(1)n    For k = 2 To Chars.Countn        ForeignChars2 = ForeignChars2 & ", " & Chars(k)n    Next knEnd Function

The Tip's first formula is very clever:n    =LET(wl, A:A, cwl, TEXTJOIN(, TRUE, wl), cl, MID(cwl, SEQUENCE(LEN(cwl)) ,1), SORT(UNIQUE(FILTER( cl, CODE(cl)>127))))nHere are some nit-picking comments:n1. TEXTJOIN(, TRUE, wl) could be replaced by CONCAT(wl) or CONCAT(A:A) for the same result. Using the latter means the wl parameter is unnecessary:n    =LET(cwl, CONCAT(A:A), cl, MID(cwl, SEQUENCE(LEN(cwl)), 1), SORT(UNIQUE(FILTER( cl, CODE(cl)>127))))n2. The Tip's formula returns a vertical array with N rows and 1 column, where N is the number of unique "foreign-language" (non-ASCII) characters. Lucie might prefer a comma separated list in a single cell:n    =LET(cwl, CONCAT(A:A), cl, MID(cwl, SEQUENCE(LEN(cwl)) ,1), TEXTJOIN(", ", TRUE, SORT(UNIQUE(FILTER(cl, CODE(cl)>127)))))n3. Unless specifically stated, most Excel functions are not case-sensitive when applied to text. Neither is the UNIQUE function; therefore, n    UNIQUE({"a";"A";"B";"b"}) returns {"a";"B"}na mixed-case array. Lucie might prefer all lower-case characters for uniform appearance:n    =LET(cwl, CONCAT(A:A), cl, MID(cwl, SEQUENCE(LEN(cwl)) ,1), TEXTJOIN(", ", TRUE, SORT(UNIQUE(LOWER(FILTER(cl, CODE(cl)>127))))))n4. Here is the same formula with the UNIQUE(...) part isolated for further discussion:n    =LET(cwl, CONCAT(A:A), cl, MID(cwl, SEQUENCE(LEN(cwl)),1), uniq, UNIQUE(LOWER(FILTER(cl, CODE(cl)>127))), TEXTJOIN(", ", TRUE, SORT(uniq)))n5. Lucie might want to list both upper-case and lower-case characters if both appear in the names. The following article describes a case-sensitive equivalent of Excel's UNIQUE function:nhttps://exceljet.net/formulas/unique-values-case-sensitivenThis version of the previous formula includes each unique case-sensitive "foreign-language" character:n    =LET(cwl, CONCAT(A:A), cl, MID(cwl, SEQUENCE(LEN(cwl)), 1), uniq, REDUCE(, FILTER(cl, CODE(cl)>127), LAMBDA(a, v, IF(SUM(--EXACT(a, v)), a, VSTACK(a, v)))), TEXTJOIN(", ", TRUE, SORT(uniq)))n6. Like UNIQUE, the SORT function is not case-sensitive. But the SortPlus function in My Excel Toolbox has a case-sensitive option:n    =LET(cwl, CONCAT(A:A), cl, MID(cwl, SEQUENCE(LEN(cwl)), 1), uniq, REDUCE(, FILTER(cl, CODE(cl)>127), LAMBDA(a, v, IF(SUM(--EXACT(a, v)), a, VSTACK(a, v)))), TEXTJOIN(", ", TRUE, SortPlus(uniq, , , , , TRUE)))n7. SortPlus also has an option for ANSI/ASCII/Unicode order instead of Excel order; that option sorts upper-case before lower-case:n    =LET(cwl, CONCAT(A:A), cl, MID(cwl, SEQUENCE(LEN(cwl)), 1), uniq, REDUCE(, FILTER(cl, CODE(cl)>127), LAMBDA(a, v, IF(SUM(--EXACT(a, v)), a, VSTACK(a, v)))), TEXTJOIN(", ", TRUE, SortPlus(uniq, , , , , TRUE, TRUE)))nEach of these formulas can be copy/pasted into a worksheet for testing.nFor more on SortPlus, see https://excelribbon.tips.net/T012575nand https://sites.google.com/view/MyExcelToolbox