Wikipedia:Reference desk/Archives/Computing/2022 September 11

Welcome to the Wikipedia Computing Reference Desk Archives
The page you are currently viewing is a transcluded archive page. While you can leave answers for any questions shown below, please ask new questions on one of the current reference desk pages.

September 11

Counting letters (and numbers) in a text

Is there a formula I can use in an Excel spreadsheet that takes a short text and counts the occurrences of each letter (and number or special character) it contains? -- Jack of Oz ^{[pleasantries]} 20:18, 11 September 2022 (UTC)

I doubt it's practical using traditional excel formulae. You could use Visual Basic for Applications. 41.23.55.195 (talk) 13:25, 12 September 2022 (UTC)

For each character you are interested in, use length of original text - length of text with "character of interest" substituted with "". But you would need a cell per character. -- SGBailey (talk) 18:32, 12 September 2022 (UTC)

Yeah, SUBSTITUTE will do that. Let's say you have text in cell A2. In cell B1 though cell AA1 (or longer) you've got the letters of the alphabet arranged horizontally. In cell B2 you'd put in this formula: =LEN($A$2)-LEN(SUBSTITUTE($A$2,B1,"")) So what that does is it takes the current length of the text (which will include spaces, periods, etc.) and then compares it to the same text if we perform the substitution. In cell B2 it's referencing cell B1, which has the "A" in it, but if you drag the formula along to grab the other letters and characters it will follow along correctly. The formula will work for punctuation and spaces, etc. It even works on stuff like * which surprised me a bit. Matt Deres (talk) 19:34, 12 September 2022 (UTC)

Thanks, Matt. With a little fiddling to: =LEN($A2)-LEN(SUBSTITUTE($A2,B$1,"")), I got it to work perfectly. EXCEPT, it counts upper and lower case letters separately. How do I get it to ignore cases? -- Jack of Oz ^{[pleasantries]} 21:33, 12 September 2022 (UTC)

I've worked out how to do it, but it involves counting upper and lower case totals separately and adding them together. Is there a more elegant way? -- Jack of Oz ^{[pleasantries]} 22:22, 12 September 2022 (UTC)

You should be able to convert to lower (or upper) case... SUBSTITUTE(LOWER($A2),LOWER(B$1), ... -- zzuuzz ^(talk) 22:36, 12 September 2022 (UTC)

Or alter the text being read first by switching it entirely to UPPER or LOWER. For example, if you're entering text in column A, have column B use a formula like =UPPER(A2) (or whatever row you're on) and then have the formulas point to B. Matt Deres (talk) 00:19, 13 September 2022 (UTC)

Great. Thanks a million. -- Jack of Oz ^{[pleasantries]} 01:02, 14 September 2022 (UTC)

Resolved