Formula to count the number of different letters in a word

Question

In sheets, suppose cell A1 contains the word CABBAGE.

I want to put a formula into, say, cell B1 that will count the number of different letters. So the output should be 5, because there are 5 different letters in CABBAGE.

I have a formula that will do this, however it is cumbersome and I bet there is a way to do it more concisely.

The formula that works is:

=if(isnumber(search("A",A1)),1,0) + if(isnumber(search("B",A1)),1,0) + ... + if(isnumber(search("Z",A1)),1,0)

The formula adds 1 each time a letter is present in the word. I'm just not sure of the best way to consolidate the 26 terms into something more manageable.

Answer 1

If you need to process only one cell, you can use this in B2 (supposing that cell is A2):

=ArrayFormula(SUM(IF(IFERROR(SEARCH(CHAR(64+SEQUENCE(1,26)),A2))>0,1)))

If you need to process an entire range of cells (say, A2:A), you can use this in B2 of a previously empty B2:B range:

=ArrayFormula(IF(A2:A="",,MMULT(IF(ISNUMBER(SEARCH(CHAR(64+SEQUENCE(1,26)),A2:A)),1,0),SEQUENCE(26,1,1,0))))

In short, these formulas try to SEARCH all 26 letters of the alphabet in the target cell(s), using an ASCII list generated by CHAR and SEQUENCE. Since the ASCII value of 'A' is 65, we can run the entire uppercase alphabet this way. And since SEARCH is case agnostic, we can search both uppercase and lowercase at the same time. SEARCH also stops searching at the first instance of a letter that it finds; so it won't count multiples of the letter. That is, if it is found at all, the condition is satisfied.

Answer 2

Please use the following formula

=COUNTUNIQUE(SPLIT(REGEXREPLACE(REGEXREPLACE(LOWER(S1),"[\W|\d]",""),"(.)","$1 ")," "))

Functions used:

• COUNTUNIQUE
• SPLIT
• REGEXREPLACE
• LOWER