Counting a single value in a cell?

[Greg]

I want to count how often a single value (the dash symbol) occurs in a
single cell than rank it. Showing two examples, AUS-IAH/PTY-IAH/AUS is in
cell A1 and
AUS-ORD-LHR/WAW-LHR-ORD/AUS is cell A2. Cell A1 has 2 dashes and A2 has 4
dashes. Is there a way to do this?

Thank you.

 

[Gary''s Student]

=LEN(B10)-LEN(SUBSTITUTE(B10,"-",""))

for data in B10

 

[Greg]

Thank you for the assist, it worked perfectly. Is there a way to lock this
function, so I can copy it?

 

[Greg]

I'm sorry, I should have stated my question more clearly. Is there a way for
me to copy the formula where I would be able to get the results for each row
thereafter? When I added =LEN($B$10)-LEN(SUBSTITUTE($B$10,"-","")) then
copied it to another cell, the result was the same but it should be a
different number.


Thanks,
Greg

 

[Gary''s Student]

A tiny trick:

1. select the cell you want the formula to be in
2. copy the formula from the webiste
3. paste the formula in the FORMULA BAR, not the cell
4. edit the formula in the bar

 

[Greg]

Last question, I understand everything you've said but I have over 1200 rows,
do I have to edit each of them?

Thanks,
Greg

 

[T. Valko]

Assuming your data is in the range A1:A1200 and there are no empty cells
within this range.

Enter the formula in cell B1 using relative references. (no $ signs)

=LEN(B1)-LEN(SUBSTITUTE(B1,"-",""))

With cell B1 selected double click the fill handle. The fill handle is the
little black square on the lower right side of the selected cell. Hover your
mouse over the fill handle until it changes from a fat plus sign to a skinny
plus sign. When it changes to a skinny plus sign double click and the
formula in B1 will be copied down to B1200 with the references changing
automatically.

 

[Greg]

Gary’s Student and T. Valko...THANKS FOR ALL THE HELP!!!

VIRTUAL BEERS FOR THE TWO OF YOU.

CHEERS!!


Gary’s Student and T. Valko

 

[T. Valko]

Sounds like a winner to me.

You're welcome!