Nice use of Len Roos1. The function I have placed above could also be used in a formula as it simply returns an array of 26 elements (1 for each letter) representing the count of each letter. By using the INDEX function you can return a particular element (ie letter) within the array. The index number will represent the position of the letter in the alphabet (eg B=2, J=10 etc).
So say your target cell is A1 and you want to count number of B's ...
Count # J's=INDEX(COUNTLETTER(A1),2)
Count # Z's=INDEX(COUNTLETTER(A1),10)
You can also place number of each letter in cells automatically by selecting the number of cells for which you want to find letters then array entering the COUNTLETTER function.=INDEX(COUNTLETTER(A1),26)
eg
1. Select cells A1:Z1
2. Enter Formula =COUNTLETTER("Count number for each letter in this string")
3. Press CTRL+SHIFT+ENTER to confirm the formula
A1:Z1 will be populated with the count for each letter.
Example workbook attached.
Reply With Quote