Count characters within a string

G

Guest

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.
 
G

Guest

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
 
G

Guest

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

K

Ron Coderre said:
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
Click to expand...
 
G

Guest

I owe you a beer.

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


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

XL2002, WinXP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

find and replace numeric strings in larger text strings 8
count no. of commas (,) in a string in a cell 3
Count occurrences of text within a cell 3
formulas to count dates 2
Cell Range Split into Multiple Cells 3
How to count the number of occurrence within string? 2
Extracting certain characters from a long string 4
sum numbers in cell string array 6

Top