the two languages I use most are Spanish and Catalan.

Spanish uses accents on vowels: á, é, í, ó, ú Catalan uses still some differents accents: à, ...

Many people forget to use accents properly, so that, in practical terms "a"="á" and "a"="à", for example.

Excel is case-insensitive, so that "A"="a" However, Excel is accent-sensitive, so that "a"<>"á"

I need to make comparisons among texts, as if accents were not there. For example, when comparing "común" to "comun", I want the result to be TRUE. I have looked-up in all the excel functions and I have not found any of them that allows this type of comparisons.

I know that, somehow, I need to use the SUBSTITUTE function in order to solve my problem, but I cannot exactly find out how.

Any hints are much appreciated

CodePudding user response：

Create a two-column table, named Table1, for which the first and second columns comprise the accented and corresponding non-accented letters respectively, for example:

After which you can use the following formula, assuming the entries to be compared are in A1 and B1:

=LET(
    α, A1:B1,
    ζ, MID(LOWER(α), SEQUENCE(MAX(LEN(α))), 1),
    ξ, IFNA(XLOOKUP(ζ, Table1[Accented], Table1[Non-Accented]), ζ),
    AND(INDEX(ξ, , 1) = INDEX(ξ, , 2))
)

which, for example, will return TRUE if A1 and B1 contain 'cómúni' and 'COMUNI' respectively.

This can either be copied down to give similar results for entries in A2:B2, A3:B3, etc., or be modified so as to spill down automatically for other entries.