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.

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

for data in B10
--
Gary''s Student - gsnu200789


"Greg" wrote:

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.

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

"Gary''s Student" wrote:

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

for data in B10
--
Gary''s Student - gsnu200789


"Greg" wrote:

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.

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" wrote:

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

for data in B10
--
Gary''s Student - gsnu200789


"Greg" wrote:

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.

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
--
Gary''s Student - gsnu200789


"Greg" wrote:

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" wrote:

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

for data in B10
--
Gary''s Student - gsnu200789


"Greg" wrote:

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.

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

Thanks,
Greg

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200789


"Greg" wrote:

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" wrote:

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

for data in B10
--
Gary''s Student - gsnu200789


"Greg" wrote:

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.

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.


--
Biff
Microsoft Excel MVP


"Greg" wrote in message
...
Last question, I understand everything you've said but I have over 1200
rows,
do I have to edit each of them?

Thanks,
Greg

"Gary''s Student" wrote:

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
--
Gary''s Student - gsnu200789


"Greg" wrote:

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" wrote:

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

for data in B10
--
Gary''s Student - gsnu200789


"Greg" wrote:

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.