I need to figure out a formula that will count how many different letters appear in a cell. I have a cell that has a quote it, though it is programmed to change quotes every time the page resets so I cannot enter an exact word to search. I have tried search functions mixed with trims and lengths, I just cannot figure out something that works.
Try =LEN(SUBSTITUTE(A1," ",""))
Obviously replace the A1 with the cell you are counting
Yes I tried that one before, it gives the total number of letters in the cell.
For example:
I go to the store
My answer would need to be 8 because there are 8 different letters in that sentence, as opposed to 13 total letters.
Hello, try this :
In A1 : your string
in B1 : =LEN(A1)
in C1 : =IF(LEN(A1)=LEN(SUBSTITUTE(A1, " ","")),0,1)
in D1 : =SUM(IF(FREQUENCY(MATCH(MID(SUBSTITUTE(A1," ", ""), ROW(INDIRECT("1:" & B1)),1),MID(SUBSTITUTE(A1, " ", ""), ROW(INDIRECT("1:"&B1)),1),0),ROW(INDIRECT("1:"&B1)))>=1,1,0))-C1 as an array formula, ie validate using CTRL+SHIFT+ENTER
GC Excel
If this post helps, then click the star icon (*) in the bottom left-hand corner of my post to Add reputation.
I just answered a similar question a few minutes ago:
https://www.excelforum.com/showthread.php?t=1077675
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Try this one
=SUM(COUNTIF(A1,"*"&{"a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v","w","x","y","z"}&"*"))
it will count unique letters only
Row\Col I go to the store Yes I tried that one before I just cannot figure out something that works
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks