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 cell....Any help with this would be great.

[SentToStud]

use spaces, not commas. also enter the four numbers into the cell like this:

'1 3 5 6

then try this:
=IF(LEN(TRIM(A1))=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1)

[TheSpyder]

=Count(1,3,5,6)

You have to rewrite the numbers. Maybe someone knows another way

[SentToStud]

Originally Posted by SentToStud

use spaces, not commas. also enter the four numbers into the cell like this:

'1 3 5 6

then try this:
=IF(LEN(TRIM(A1))=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1)

Scav, it should work with commas in the cell also.

[Scav]

Bruce's way works but it is some serious formulas....Gonna have to lock the cell so people don't mess with it. It is for P6 writing, for all to use when formulating tickets.

FYI Bruce,it works without that ' in front of the numbers, I appreciate it

[Scav]

BRUCE>>>>>>

It works. Can't wait to reveal this for all at DT...I hope it will allow me to post the file for people...

I loves excel.

Can do so much stuff with it.
Loaded with little presents if
you use it a lot.