Counting character occurrences (Excel) Solved

Question

Hello,

In Excel 2003, I want to count the number of occurrences of a string of characters in a given cell.
I tried to do it with SEARCH, but that’s clearly not the right solution.
Could someone please help me?

Thank you!

Configuration: Windows XP Internet Explorer 7.0

Mike-31 · Accepted Answer

Hello,

I’m not sure if I understood everything correctly,

If you want to count the number of characters in cell A1, the formula is

=LEN(A1)

To count the number of R’s for example, the formula is

=SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(A1,"R",""))))

Or to count the characters different from R

=LEN(SUBSTITUTE(A1,"R",""))

A+

identifiant-comment · Answer

Thank you!  =SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(A1,"R","")))) gets me close to the result.  But how come it always gives me double the number of occurrences that I actually have?

identifiant-comment · Answer

And here it is:
Contents of a cell:
RFD-02
RFD-03
RFD-04
RFD-05 = RG-SYN-SIN4
RFD-06
RFD-08
RFD-30
RFD-07

Current formula:
=(LEN(L19)-LEN(SUBSTITUTE(L19,"FD","")))/2

Expected result: 8.

You will notice the subtle "/2" which is the only way I have found to get accurate results...
Thank you!

Mike-31 · Answer

Re  Look with this formula to count the FD from the range A1 to A50  =COUNTIF(A1:A50,"*FD*")  A+

identifiant-comment · Answer

Hi,  Your formula works, but for a range of cells. What I'm looking for is to count the occurrences of characters within a single cell...

Mike-31 · Answer

Re,  It's the same, you identify your cell A1 for example and the result is 1 if the cell contains RFD-02 and 0 for RFCD-07 for example  =COUNTIF(A1,"*FD*")  A+

Mike-31 · Answer

Hi,  you also have something more complex, this formula  =SUM(LEN(E31)-LEN(SUBSTITUTE(E31,"FD","")))/LEN("DF")  Best regards,

identifiant-comment · Answer

Youhou !!  We made it, it's the right formula !!  I really thank you. Have a good evening!

Coco_Gilou · Answer

Number of times "x" in A2 =LEN(A2)-LEN(SUBSTITUTE(A2,"x",""))

woalith · Answer

it's normal to divide by 2
with each occurrence you remove 2 characters
so the difference in the number of characters between the initial and after the substitution is 2 times too large
the result should not be divided by 2 but by the number of characters you remove

chlolads12 · Answer

Hello,

I would like to continue this conversation because I cannot solve the problem I am facing (despite desperately searching for hours...) :-/

Here is my data range (A1 to A5):

Too many offers are proposed
The description of the offers is not clear enough
The scrolling on the homepage is too fast
The offers are not interesting
Others

I want to know how many times the word "offers" appears in my list.

I wrote this formula:
=SUM(LEN(A2:A5)-LEN(SUBSTITUTE(A2:A5,"offers","")))/LEN("offers")

but I get this error "#VALUE!" in my cell.

Can you help me? :-/

sylvled · Answer

instead of dividing by the length of the searched word, it is enough to replace the searched word not with an empty string but with a single arbitrary character....

=SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(A1,"R","x"))))

Counting character occurrences (Excel)

12 réponses

Texture issues in enshrouded

Old lullaby

Unable to connect to freebox os

Scam site trombi.com

Laptop keyboard issue uiopjklm

Fortnite-a2f is not activating on my ps4 account.

How can i tell if i caught a virus on my phone?

Record a video from m6 replay to my pc

Tamagotchi familitchi: put it on pause

Waiting times for free phone delivery