Count Instances of a Specific Character

  • Thread starter Thread starter Commish
  • Start date Start date
C

Commish

here's what I am trying to do - I am trying to split up a cell based
on it's contents. While the data is in the same order in the column
and it is delimited by a space, it doesn't work to delimit it by
spaces or by number of charatcers.

The problem is that between the delimiters there may be 1 or 2 words -
separated by a comma and the data may be of variable length.

Is there a way to Count the Instances of a Specific Character - for
example, is there a function to count the number of spaces in this
sentence? Or the number of x's?
 
=len(a1)-len(substitute(a1,"x",""))
will count the number of lowercase x's in A1.
(=substitute() is case sensitive)

If you wanted the total number of x's (upper or lower):
=len(a1)-len(substitute(upper(a1),"X","")
or
=len(a1)-len(substitute(upper(a1),upper("x"),"")

(I'm always afraid that I'll forget to make that "X" upper case, so I like to
use the =upper() function to remind me.)

And if you have lots of spaces that you want to get rid of:
=trim(a1)
will remove leading, trailing and change multiple internal spaces to a single
space:

---qwer----asdf-qwer---qwer------- (- = space)
will become
qwer asdf qwer qwer
after =trim()
 
here's what I am trying to do - I am trying to split up a cell based
on it's contents. While the data is in the same order in the column
and it is delimited by a space, it doesn't work to delimit it by
spaces or by number of charatcers.

The problem is that between the delimiters there may be 1 or 2 words -
separated by a comma and the data may be of variable length.

Is there a way to Count the Instances of a Specific Character - for
example, is there a function to count the number of spaces in this
sentence? Or the number of x's?
Click to expand...


Try this formula:

=SUMPRODUCT(--(MID(A1,ROW(OFFSET($A$1,,,LEN(A1))),1)="x"))

The result should be the number of x's in cell A1.

Hope this helps / Lars-Åke
 
Back
Top