Help!

How to count commas in a cell?

 
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> Worksheet Functions RSS
Next:  Need help with a function - help 3  
Author Message
LinLin
External


Since: Jan 28, 2009
Posts: 4



PostPosted: Wed Jan 28, 2009 10:43 pm    Post subject: How to count commas in a cell?
Archived from groups: microsoft>public>excel>worksheet>functions (more info?)

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!
Back to top
Max
External


Since: Mar 17, 2004
Posts: 1796



PostPosted: Wed Jan 28, 2009 10:58 pm    Post subject: Re: How to count commas in a cell? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

> .. 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 ",")
Back to top
T. Valko
External


Since: Nov 24, 2006
Posts: 3649



PostPosted: Thu Jan 29, 2009 1:56 am    Post subject: Re: How to count commas in a cell? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

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!
Back to top
Rick Rothstein
External


Since: Jan 15, 2009
Posts: 127



PostPosted: Thu Jan 29, 2009 2:02 am    Post subject: Re: How to count commas in a cell? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

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!
Back to top
LinLin
External


Since: Jan 28, 2009
Posts: 4



PostPosted: Thu Jan 29, 2009 2:02 am    Post subject: Re: How to count commas in a cell? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

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!
>
>
Back to top
LinLin
External


Since: Jan 28, 2009
Posts: 4



PostPosted: Thu Jan 29, 2009 3:36 am    Post subject: Re: How to count commas in a cell? [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

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!
>
>
Back to top
Display posts from previous:   
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> Worksheet Functions All times are: Eastern Time (US & Canada)
Page 1 of 1

 
You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum