Counting a single value in a cell?
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. |
Counting a single value in a cell?
=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. |
Counting a single value in a cell?
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. |
Counting a single value in a cell?
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. |
Counting a single value in a cell?
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. |
Counting a single value in a cell?
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. |
Counting a single value in a cell?
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. |
Counting a single value in a cell?
Garys Student and T. Valko...THANKS FOR ALL THE HELP!!!
VIRTUAL BEERS FOR THE TWO OF YOU. CHEERS!! Garys Student and T. Valko "T. Valko" wrote: 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. |
Counting a single value in a cell?
Sounds like a winner to me.
You're welcome! -- Biff Microsoft Excel MVP "Greg" wrote in message ... 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" wrote: 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. |
All times are GMT +1. The time now is 11:37 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com