Count the number of time a character appears in a string

[AndyD]

Hi can supply me with formula to count the number of time a character appears in a string

eg if cell A1 contains "Hello there my friend"...what is formula to count number of e's for example, thanks
Andy

 

[AC]

Aladin, thanks that looks like it will do it :D

 

[AndyD]

count no of dots in character string

hi i need formula for counting number of dots in a character string....eg how many dots there are in cell A1 = "........hello"

thanks
A

 

[Andrew Poulsom]

Did you try this?

=LEN(A1)-LEN(SUBSTITUTE(A1,".",""))

 

[AndyD]

hi andrew
either im doing something wrong or is not working..
?
thanks
Andy

 

[Andrew Poulsom]

For me it returned 8. What did you get?

 

[AndyD]

i got 8 too...but if you then overtype it with something else it doesnt count it correctly....

eg try ....something else, ...........................word etc

any ideas?
thanks
Andy

ps an alternative way of doing what we actually want would be to allow user to enter the number of dots they want in cell A1 (eg they put number 12 in), enter a character string in cell A2 (eg company name) and then macro creates this in cell A3

cell A3 = "............company name"

is this something you could help with?

 

[Aladin Akyurek]

Re: count no of dots in character string

hi i need formula for counting number of dots in a character string....eg how many dots there are in cell A1 = "........hello"

thanks
A

To count the frequency of occurrence of a single char in a string in A1...

=LEN(A1)-LEN(SUBSTITUTE(A1,D1,""))

where D1 houses a char like a dot.

For a word in D1, that is, when LEN(D1) > 1, you would use...

=(LEN(" "&A1&" ")-LEN(SUBSTITUTE(" "&A1&" "," "&D1&" ","")))/LEN(" "&D1&" ")

These 2 formulas can even be combined in a single IF formula.

 

[Richie(UK)]

Hi Guys,

Sorry to butt in, but this is weird.

I entered "...test" (three full stops followed by the word test) and copied in the formula. It showed an answer of 0 (nil). I removed the three stops and it still showed 0 (as expected). Then when I re-entered the stops the result shows as 3 (Hooray!).

(Similarly, just doing Len(A1) with the same string gives an answer of 5!)

Aladin - your formula seems to have the same consequences. That is, 0 initially but then the correct count after editing.

 

[Andrew Poulsom]

i got 8 too...but if you then overtype it with something else it doesnt count it correctly....

eg try ....something else, ...........................word etc

any ideas?
thanks
Andy

ps an alternative way of doing what we actually want would be to allow user to enter the number of dots they want in cell A1 (eg they put number 12 in), enter a character string in cell A2 (eg company name) and then macro creates this in cell A3

cell A3 = "............company name"

is this something you could help with?

For ....something else, ...........................word etc I got 31. If calculation is set to manual you will need to press F9 to refresh the formula.

This formula in A3:

=REPT(".",A2)&A1

will precede what is in A1 with the number of dots in cell A2.

 

[Andrew Poulsom]

Hi Guys,

Sorry to butt in, but this is weird.

I entered "...test" (three full stops followed by the word test) and copied in the formula. It showed an answer of 0 (nil). I removed the three stops and it still showed 0 (as expected). Then when I re-entered the stops the result shows as 3 (Hooray!).

(Similarly, just doing Len(A1) with the same string gives an answer of 5!)

Aladin - your formula seems to have the same consequences. That is, 0 initially but then the correct count after editing.

Excel thinks the three dots are the elipsis character. If you type ...test in a cell then press F2 HOME you only need to press Delete once to remove all three dots.

This seems to work:

=(LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(133),"")))*3+(LEN(A1)-LEN(SUBSTITUTE(A1,".","")))

but it's pretty long-winded.