Excel Question

[Scav]

This might be pretty extreme but I am trying to put together something and I need some help with formula writing.

Lets say that Cell A1 has the value of "1,3,5,6"

I want to create a formula where A2 will equal 4, for the number of values within that specific cell....Any help with this would be great.

I know the easier way to do this is to separate the values, but that will be pretty much impossible in what I am trying to do....

Thank you in advance for any help

[dutchboy]

May need a better example of what you are trying to do. If A1 has a value of 3 or another number do you want A2 to always be 4. Not sure what the comma's in the string of numbers "1,3,5,6" are for.



This might be pretty extreme but I am trying to put together something and I need some help with formula writing.

Lets say that Cell A1 has the value of "1,3,5,6"

I want to create a formula where A2 will equal 4, for the number of values within that specific cell....Any help with this would be great.

I know the easier way to do this is to separate the values, but that will be pretty much impossible in what I am trying to do....

Thank you in advance for any help

[Scav]

I want it to count how many numbers are in that cell....So if the cell has "1,4,6,7,8" then it has 5 numbers. If it has "1,2,3" then it has 3 numbers. So it is a count feature essentially but I can't get it to count WITHIN the cell, just the actual cell itself

[Cheap Speed]

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

[Scav]

My friend actually recommended this a couple minutes ago, IT WORKS. Thank you very much, appreciate it greatly

[cj]

A little simpler:

=LEN(SUBSTITUTE(A1,",",""))

[Cheap Speed]

1,2,3,10

would give 5 for above under CJ's formula & would give error for any string if you have #'s with more than one digit like 10,105, etc

[equicom]

Your problem is that you can't normally use comma separated numbers in a cell. If you use the "len" method, it counts all the characters in the string, which will be out if you have any number above 9.

If you have each value in its own cell (in which case you have to know the maximum number of values possible) then you can get away with (for example):

=Count(a1:t1)

That would be for a maximum of 20 items. For a maximum of 5 items, you'd use:

=count(a1:e1)

[equicom]

The complicated way to do the operation, if you don't want individual cells for each item in the array would be:

myString = "1,3,5,6" 'Instead of the test string, set it to get val of cell in real ver
myPos=0
myCount=0
myPos=Instr(1, MyString, ",")
myCount=myCount+1
if myPos >0 then
For x = myPos to Len(myString)
myPos=instr(x,myString, ",")
if myPos > 0 then
myCount=myCount +1
x = myPos
end if
Next x
EndIf