Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to count commas in a cell?
Hi Everyone
I have cells with lists of people in them (ie as a list of names, divided by commas. I need to count the total number of people in all the cells. For instance, A1 = Tom, John, Bob A2 = Kate, Uma, Deidre I figure if I can count the commas and add 1 to each cell, I'll have the solution. But I can't get the count functions to work on a comma (not even as ",") Could someone point me in the right direction? Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to count commas in a cell?
Try this:
=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1 Assumes the cell(s) will not be empty. -- Biff Microsoft Excel MVP "LinLin" wrote in message ... Hi Everyone I have cells with lists of people in them (ie as a list of names, divided by commas. I need to count the total number of people in all the cells. For instance, A1 = Tom, John, Bob A2 = Kate, Uma, Deidre I figure if I can count the commas and add 1 to each cell, I'll have the solution. But I can't get the count functions to work on a comma (not even as ",") Could someone point me in the right direction? Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to count commas in a cell?
.. can count the commas
One way In B1: =LEN(A1)-LEN(SUBSTITUTE(A1,",","")) Adapt it further to suit your intents, eg: "+1" to the expression to get the count of names -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "LinLin" wrote: Hi Everyone I have cells with lists of people in them (ie as a list of names, divided by commas. I need to count the total number of people in all the cells. For instance, A1 = Tom, John, Bob A2 = Kate, Uma, Deidre I figure if I can count the commas and add 1 to each cell, I'll have the solution. But I can't get the count functions to work on a comma (not even as ",") |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to count commas in a cell?
This should give you the sum you are after...
=SUMPRODUCT((LEN(A1:A1000)-LEN(SUBSTITUTE(A1:A1000,",",""))+1)*(A1:A1000<"") ) Adjust the ranges to cover the maximum number of rows you expect to have data in (and make sure you use the same range for each). -- Rick (MVP - Excel) "LinLin" wrote in message ... Hi Everyone I have cells with lists of people in them (ie as a list of names, divided by commas. I need to count the total number of people in all the cells. For instance, A1 = Tom, John, Bob A2 = Kate, Uma, Deidre I figure if I can count the commas and add 1 to each cell, I'll have the solution. But I can't get the count functions to work on a comma (not even as ",") Could someone point me in the right direction? Thanks! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to count commas in a cell?
Thanks Rick - that's saved me a heap of time and I would never have come up
with that! "Rick Rothstein" wrote: This should give you the sum you are after... =SUMPRODUCT((LEN(A1:A1000)-LEN(SUBSTITUTE(A1:A1000,",",""))+1)*(A1:A1000<"") ) Adjust the ranges to cover the maximum number of rows you expect to have data in (and make sure you use the same range for each). -- Rick (MVP - Excel) "LinLin" wrote in message ... Hi Everyone I have cells with lists of people in them (ie as a list of names, divided by commas. I need to count the total number of people in all the cells. For instance, A1 = Tom, John, Bob A2 = Kate, Uma, Deidre I figure if I can count the commas and add 1 to each cell, I'll have the solution. But I can't get the count functions to work on a comma (not even as ",") Could someone point me in the right direction? Thanks! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to count commas in a cell?
Thanks Rick - that worked extremely well!
"Rick Rothstein" wrote: This should give you the sum you are after... =SUMPRODUCT((LEN(A1:A1000)-LEN(SUBSTITUTE(A1:A1000,",",""))+1)*(A1:A1000<"") ) Adjust the ranges to cover the maximum number of rows you expect to have data in (and make sure you use the same range for each). -- Rick (MVP - Excel) "LinLin" wrote in message ... Hi Everyone I have cells with lists of people in them (ie as a list of names, divided by commas. I need to count the total number of people in all the cells. For instance, A1 = Tom, John, Bob A2 = Kate, Uma, Deidre I figure if I can count the commas and add 1 to each cell, I'll have the solution. But I can't get the count functions to work on a comma (not even as ",") Could someone point me in the right direction? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i add commas in a single cell? | Excel Worksheet Functions | |||
Count the number of Commas found in cell A2 | Excel Worksheet Functions | |||
formula to count commas | Excel Discussion (Misc queries) | |||
Count values between commas | Excel Discussion (Misc queries) | |||
Count Commas in Cells | Excel Discussion (Misc queries) |