Counting within a cell
 

Is there a formula which would allow you to count the number of characters
within a cell which match a certain criteria? FIND will tell you the first
instance of a character or group of characters. LEN will tell you the count
of all characters in a cell.

What I need is something like this:
FORMULA("Betty Crocker Fruit By The Foot Assorted Fruit Roll Fruit Snact 1ct
0.5oz", " ") = 12, because there are 12 spaces in the text.

Alternatively, is there some way to make FIND work backwards and instead of
telling you the first instance of a character, have it tell you the last
instance?

My problem is I am trying to get the "1ct" out of the above example, but the
character count and number of spaces varies with each cell.

Thanks for the advice,
Jane

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

will give you the number of lookup_char in cell A1.

To get the last instance of say a space, you use

=FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))

but you need this and the last but one to get your 1ct, so you use

=MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))-1))+1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))-FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))-1)))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jane" wrote in message
...
Is there a formula which would allow you to count the number of characters
within a cell which match a certain criteria? FIND will tell you the
first
instance of a character or group of characters. LEN will tell you the
count
of all characters in a cell.

What I need is something like this:
FORMULA("Betty Crocker Fruit By The Foot Assorted Fruit Roll Fruit Snact
1ct
0.5oz", " ") = 12, because there are 12 spaces in the text.

Alternatively, is there some way to make FIND work backwards and instead
of
telling you the first instance of a character, have it tell you the last
instance?

My problem is I am trying to get the "1ct" out of the above example, but
the
character count and number of spaces varies with each cell.

Thanks for the advice,
Jane

Thanks so much. I would have never thought about coming at it from that angle.

Jane

"Bob Phillips" wrote:

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

will give you the number of lookup_char in cell A1.

To get the last instance of say a space, you use

=FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))

but you need this and the last but one to get your 1ct, so you use

=MID(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))-1))+1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))))-FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1,"
",""))-1)))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jane" wrote in message
...
Is there a formula which would allow you to count the number of characters
within a cell which match a certain criteria? FIND will tell you the
first
instance of a character or group of characters. LEN will tell you the
count
of all characters in a cell.

What I need is something like this:
FORMULA("Betty Crocker Fruit By The Foot Assorted Fruit Roll Fruit Snact
1ct
0.5oz", " ") = 12, because there are 12 spaces in the text.

Alternatively, is there some way to make FIND work backwards and instead
of
telling you the first instance of a character, have it tell you the last
instance?

My problem is I am trying to get the "1ct" out of the above example, but
the
character count and number of spaces varies with each cell.

Thanks for the advice,
Jane