How to count numbers in cells where several numbers are separated by /

What is it you are trying to count? How many numbers in 4682? Or you want to SUM 4682 from A1 and 2136 from A2?

Alkey is right. You have not explained your requirement clearly.

So see if this helps.
Assuming your string is in A1, then

If you just want to return the third number in the string, try this in B1

Please Login or Register  to view this content.

If you want B1 to show the number of digits in the third number of the string, try this in B1

Please Login or Register  to view this content.

If you show an example with data and the expected output it would become even more clear.

Assuming there will always be 4 delimited numbers per cell...

Data Range

	A	B
1	6541/345/4862/13	7198
2	1235/489/2136/59
3	41887/20/200/100

This array formula** entered in B1:

=SUM(--TRIM(LEFT(RIGHT(SUBSTITUTE(A1:A3,"/",REPT(" ",100)),200),100)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Originally Posted by Tony Valko

This array formula** entered in B1:

=SUM(--TRIM(LEFT(RIGHT(SUBSTITUTE(A1:A3,"/",REPT(" ",100)),200),100)))

Just a point, but that'll work equally well non-CSE with SUMPRODUCT.

Cheers.

Yes, so let's use this opportunity to look at the myth that array formulas are slower than normally entered formulas.

Tested these 2 formulas:

Array entered**:

=SUM(--TRIM(LEFT(RIGHT(SUBSTITUTE(A1:An,"/",REPT(" ",100)),200),100)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Normally entered:

=SUMPRODUCT(--TRIM(LEFT(RIGHT(SUBSTITUTE(A1:An,"/",REPT(" ",100)),200),100)))

Here are the calculation times (in seconds):

Data Range

	C	D	E	F	G	H	I
1	100 rows	Test1	Test2	Test3	Test4	Test5	Average
2	SUM array	0.00070	0.00071	0.00070	0.00071	0.00070	0.00070
3	SUMPRODUCT	0.00071	0.00070	0.00070	0.00070	0.00070	0.00070
4
5	1000 rows	Test1	Test2	Test3	Test4	Test5	Average
6	SUM array	0.00535	0.00541	0.00551	0.00536	0.00536	0.00540
7	SUMPRODUCT	0.00547	0.00541	0.00537	0.00537	0.00541	0.00541

Used Charles Williams' range timer code which can be found here:

http://msdn.microsoft.com/en-us/library/aa730921.aspx

I don't think that the speed is an issue. It is the system resources that required to process array calculations which in turn may slow the entire calculation process down. What is more interesting is that Microsoft's recommendation to use SUMPRODUCT function when possible because "it is slightly faster than the equivalent array formula".

http://msdn.microsoft.com/en-us/libr...rraySumProduct

Originally Posted by AlKey

It is the system resources that required to process array calculations which in turn may slow the entire calculation process down.

Which is why the results you get on your machine may be different than the results I get on my machine.

use SUMPRODUCT function when possible because "it is slightly faster than the equivalent array formula".

Well, that wasn't the case in this application.

Actually I tend to prefer SUMPRODUCT when possible, not because I believe it is necessarily "faster" than the equivalent CSE version, but simply as it avoids potential issues re the OP coming back having obviously failed to heed the large, emboldened, asterisked instructions re array formulas that I include!

Cheers

Can you post a SMALL sample that shows us what results you expect?

A SMALL file is one where we don't have to scroll to see the data!