Counting a single value in a cell?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Greg - 30 May 2008 16:29 GMT

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.

Reply to this Message

Gary''s Student - 30 May 2008 16:58 GMT

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

for data in B10

Signature

Gary''s Student - gsnu200789

> 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
[quoted text clipped - 3 lines]
>
> Thank you.

Reply to this Message

Greg - 30 May 2008 17:41 GMT

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

> =LEN(B10)-LEN(SUBSTITUTE(B10,"-",""))
>
[quoted text clipped - 7 lines]
> >
> > Thank you.

Reply to this Message

Greg - 30 May 2008 18:04 GMT

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

> =LEN(B10)-LEN(SUBSTITUTE(B10,"-",""))
>
[quoted text clipped - 7 lines]
> >
> > Thank you.

Reply to this Message

Gary''s Student - 30 May 2008 19:05 GMT

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

Signature

Gary''s Student - gsnu200789

> 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
[quoted text clipped - 16 lines]
> > >
> > > Thank you.

Reply to this Message

Greg - 30 May 2008 19:31 GMT

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

Thanks,
Greg

> A tiny trick:
>
[quoted text clipped - 23 lines]
> > > >
> > > > Thank you.

Reply to this Message

T. Valko - 30 May 2008 21:46 GMT

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.

Signature

Biff
Microsoft Excel MVP

> Last question, I understand everything you've said but I have over 1200
> rows,
[quoted text clipped - 35 lines]
>> > > >
>> > > > Thank you.

Reply to this Message

Greg - 30 May 2008 23:21 GMT

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

> Assuming your data is in the range A1:A1200 and there are no empty cells
> within this range.
[quoted text clipped - 49 lines]
> >> > > >
> >> > > > Thank you.

Reply to this Message

T. Valko - 31 May 2008 04:24 GMT

Sounds like a winner to me.

You're welcome!

Signature

Biff
Microsoft Excel MVP

> Gary's Student and T. Valko...THANKS FOR ALL THE HELP!!!
>
[quoted text clipped - 64 lines]
>> >> > > >
>> >> > > > Thank you.

Reply to this Message