2

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.

0

2 Answers 2

2

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.

2

Please use the following formula

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

Formula to count the number of unique letters in a word

Functions used:

2
  • Please remember that as per site guidelines when an answer addresses your question, you should accept it so others can benefit as well. You can even upvote the answer. Jul 18, 2021 at 2:48
  • Thanks, just what I was looking for. I have not used all of those functions before so I appreciate the links to more info. Jul 18, 2021 at 3:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.