Count characters within a string
 

I would like to count how many "1", "x" and "2" I have within the string below:
cell a1, string "1x21x21x21x21xx",
cell b1, 5 ( number of "1")
cell c1, 6 ( number of "x")
cell d1, 4 ( number of "2")

Bear in mind that string could change in size, with more or less characters
on it and column A is full of them, around 5000 rows.
Is there any formula or combination of formulas that allow me to calcualte
this?
Thanks in advance.

Count characters within a string
 

Try this:

B1: =LEN(A1)-LEN(SUBSTITUTE(A1,"1",""))
C1: =LEN(A1)-LEN(SUBSTITUTE(A1,"x",""))

OR....a more flexible solution

B2: 1
C2: x
D2: 2

B1: =LEN($A1)-LEN(SUBSTITUTE($A1,B2,""))
Copy B1 across to the right

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Francisco" wrote:

I would like to count how many "1", "x" and "2" I have within the string below:
cell a1, string "1x21x21x21x21xx",
cell b1, 5 ( number of "1")
cell c1, 6 ( number of "x")
cell d1, 4 ( number of "2")

Bear in mind that string could change in size, with more or less characters
on it and column A is full of them, around 5000 rows.
Is there any formula or combination of formulas that allow me to calcualte
this?
Thanks in advance.

Count characters within a string
 

Try

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

do the same for the other characters


--
Regards,

Peo Sjoblom


"Francisco" wrote in message
...
I would like to count how many "1", "x" and "2" I have within the string
below:
cell a1, string "1x21x21x21x21xx",
cell b1, 5 ( number of "1")
cell c1, 6 ( number of "x")
cell d1, 4 ( number of "2")

Bear in mind that string could change in size, with more or less
characters
on it and column A is full of them, around 5000 rows.
Is there any formula or combination of formulas that allow me to calcualte
this?
Thanks in advance.

Count characters within a string
 

Ron,
I own you a beer.
Thanks a million.

K

"Ron Coderre" wrote:

Try this:

B1: =LEN(A1)-LEN(SUBSTITUTE(A1,"1",""))
C1: =LEN(A1)-LEN(SUBSTITUTE(A1,"x",""))

OR....a more flexible solution

B2: 1
C2: x
D2: 2

B1: =LEN($A1)-LEN(SUBSTITUTE($A1,B2,""))
Copy B1 across to the right

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Francisco" wrote:

I would like to count how many "1", "x" and "2" I have within the string below:
cell a1, string "1x21x21x21x21xx",
cell b1, 5 ( number of "1")
cell c1, 6 ( number of "x")
cell d1, 4 ( number of "2")

Bear in mind that string could change in size, with more or less characters
on it and column A is full of them, around 5000 rows.
Is there any formula or combination of formulas that allow me to calcualte
this?
Thanks in advance.

Count characters within a string
 

I owe you a beer.

Nah.....I'm just glad I could help.
Thanks for the feedback.


***********
Regards,
Ron

XL2002, WinXP


"Francisco" wrote:

Ron,
I own you a beer.
Thanks a million.

K

"Ron Coderre" wrote:

Try this:

B1: =LEN(A1)-LEN(SUBSTITUTE(A1,"1",""))
C1: =LEN(A1)-LEN(SUBSTITUTE(A1,"x",""))

OR....a more flexible solution

B2: 1
C2: x
D2: 2

B1: =LEN($A1)-LEN(SUBSTITUTE($A1,B2,""))
Copy B1 across to the right

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Francisco" wrote:

I would like to count how many "1", "x" and "2" I have within the string below:
cell a1, string "1x21x21x21x21xx",
cell b1, 5 ( number of "1")
cell c1, 6 ( number of "x")
cell d1, 4 ( number of "2")

Bear in mind that string could change in size, with more or less characters
on it and column A is full of them, around 5000 rows.
Is there any formula or combination of formulas that allow me to calcualte
this?
Thanks in advance.