Home > Mobile >  How can I Countunique only first word when formular doesn't accept Æ, Ø, Å
How can I Countunique only first word when formular doesn't accept Æ, Ø, Å

Time:03-09

In this sheet I'm trying to count different destination. It seems to be problematic when it comes to Æ, Ø and Å.

The formula

=COUNTA(B3:B12) & " travels to " & COUNTUNIQUE(ARRAYFORMULA(TRIM(IF(LEN(B3:B12);IFERROR(REGEXEXTRACT(LOWER(B3:B12);"^([\w\-] )");B3:B12);))))&" differenct countries"

How can I make the formula accept these characters?

CodePudding user response:

the TRIM(IF(LEN(B3:B14);IFERROR( is unnecessary in your formula.

the formula can be contracted to:

=COUNTA(B3:B14) & " travels to " & COUNTUNIQUE(ARRAYFORMULA(REGEXEXTRACT(LOWER(B3:B14);"\w ")))&" different countries"

EDIT:

if there will be any blanks in the data, then TRIM(IF(LEN(B3:B14); is unnecessary, and the formula can be contracted to:

=COUNTA(B3:B14) & " travels to " & COUNTUNIQUE(ARRAYFORMULA(IFERROR(REGEXEXTRACT(LOWER(B3:B14);"\w ");)))&" different countries"
  • Related