Excel cell counting question

[Asian Fury]

I've got a giant spreadsheet full of data and I'm trying to generate some statistics on it.

Is there anyway to count comma's within an individual cell?

Situation:
some cells have 1 piece of data and no commas
e.g. data1

some cells have 2 pieces of data separated by a comma
e.g. data1, data2

some cells have 3 pieces of data separated by commas
e.g. data1, data2, data3

is there a function that will count the number of commas within a single cell so I can determine instances of singles, doubles and triples?

Are there any other ways to count the number of data pieces within a cell besides my hypothetical method?


Thanks in advance for any help!

D

 

[arkamw]

Googling around on this one turned up:

=LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1

from here. More info here from MS.

LEN counts the characters in a cell and SUBSTITUTE replaces all the occurrences of given character with another character. By nesting SUBSTITUTE within LEN, Excel counts the characters left over AFTER substituting a comma with a blank space. Thus, the (total length of characters in a cell) - (total length of characters in a cell after removing the commas) = (number of commas in the cell). You add one in this case to signify the extra data after the comma.

Didn't know about the SUBSTITUTE function so I learned something today. I'm unaware if this applies to Office 2010.

 

[Asian Fury]

Thanks for researching it arkamw! I didn't know about the substitute function either, very interesting.

I played around with the different functions and ultimately figured out a way to count a specified numbers of commas. I used:

=COUNTIF(Master!L2:L170,"*,*") to count cells with multiple pieces of data
=COUNTIF(Master!L2:L170,"*,* *,*") to count triples
and subtracted the two to get the number of doubles.

 

[Asian Fury]

Another question for all you excel people out there.

If column A has data and column B has associated dates, is there a function to read the data in column A and if a certain data comes up, copy the column b value into a new cell/worksheet?

Situation:
Worksheet 1
Column A Column B
data1 1/1/11
data2 1/2/11
data3 12/15/10
data1 12/25/10
data1 2/28/11

I'd like worksheet 2 to end up looking like this:
Column A Column B
data1 1/1/11
data1 12/25/10
data1 2/28/11

Any tips or advice would be greatly appreciated! Thanks in advance!

D


*** After looking through Excel help, I'm basically looking to retrieve multiple entries, similar to the DGET function but returning multiple entries. Can this be done?

 

[arkamw]

Man, that's a little more difficult. Personally, I'd go at it with a macro, but this website might help you out.

You'll have to do some playing around to get the sheets correct, but it looks like pretty much what you need.

 

[michael b]

First, let me apologize for replying to a semi-dated thread. This did help me out with my project that I am working on. What I am looking to accomplish is a basic income/debt chart. I do not think the "goal seek" function will work for me. Does anyone have a list of excel functions that may help with the basics? What I want to be displayed is the running lists of our bills offset by my income. My income changes weekly based on OT and side free-lancing work. I want to be able to see my total debt dwindle away. It is a work in progress. Thanks in advance...

 

[Oldie]

Please don't bump old threads, it's fine to start a new thread if you want to discuss your issue.