Count only numbers within a cell, excluding letters & other characters

[ziphem]

I'm absolutely strugling here with this and I've been at it for more than a few hours now. Unfortunately I cannot use VBA, so have to stick to formulas!

I have cell A1 that needs to have only the numbers counted. The numbers are separated by by commas. So, let's say we have 1, 4, 5, 6. I'd like A2 to show "4". That's not a problem, I did:

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

But I'm also trying to make this more reliable, so that if people make mistakes and enter, say, double commas, or letters, that those are not counted. I have no idea, then, how to count just the numbers and nothing else. So, if someone entered: 1, 2,,3, 4 - they would get a result of 5.

I did come across a formula which confused the heck out of me, but gave me a glimmer of hope:

=SUMPRODUCT(MID(0&H9,LARGE(INDEX(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN($A$1))),1))*
ROW(INDIRECT("1:"&LEN($A$1))),0),ROW(INDIRECT("1:"&LEN($A$1))))+1,1)*10^ROW(INDIRECT("1:"&LEN($A$1)))/10)

but that just displays the numbers, one next to the other. If I could only count that output...

I'd greatly appreciate any help!!

Thanks!!!!

 

[hiker95]

ziphem,

How about this:

Excel 2007
	A
1	1,, 4,, 5,, 6, a, @
2	4
Sheet1

Cell Formulas
Range	Formula
A2	=SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9,0},"")))

 

[ziphem]

Thanks a ton for the quick feedback. The formula is great, but the problem is that it only counts 0-9, so any double digit number, e.g., 10, will be counted as 2 seperate numbers. I did look at what you did and use it as a base. What do you think about something like:

=SUM(--(FREQUENCY(COLUMN(A:IV),COLUMN(A:IV)*ISERR(-MID("."&A1&".",COLUMN(A:IV),1)))>1))

Thanks again.

 

[hiker95]

ziphem,

Thanks for the feedback. You are very welcome.

So, let's say we have 1, 4, 5, 6. I'd like A2 to show "4".

The formula I posted was correct for your original request.

so any double digit number, e.g., 10

I am not sure how to create a formula to count numbers that are 2, 3, 4 digits long.

I can do it within a macro or a User Defined Function, but, that is not what you require.


Click on the Reply to Thread button, and just put the word BUMP in the post. Then, click on the Post Quick Reply button, and someone else will assist you.

 

[ziphem]

I'm sorry, it was my mistake, I guess I was staring at my numbers the whole day so I forgot that no one else knew what I was looking at. You're right, the solution you posted is just right for what I wrote.

I do need to count numbers that go up to 80. I think that other formula I posted will work for double digit numbers; I found that other formula because of what you wrote in your response. I did try out that other formula and it seems to work ok, but thought I would post it here to get feedback on it. But it seems that for both scenarious, counting 0-9, and counting 10+, I'm now covered.

Thanks!

 

[Rick Rothstein]

Thanks a ton for the quick feedback. The formula is great, but the problem is that it only counts 0-9, so any double digit number, e.g., 10, will be counted as 2 seperate numbers. I did look at what you did and use it as a base. What do you think about something like:

=SUM(--(FREQUENCY(COLUMN(A:IV),COLUMN(A:IV)*ISERR(-MID("."&A1&".",COLUMN(A:IV),1)))>1))

Thanks again.

Since I developed it, I figured I might as well post it - this formula also seems to work...

=SUMPRODUCT(--ISNUMBER(-TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",99)),ROW(1:65536)*99-98,99))))

 

[Eisasuarez]

Since I developed it, I figured I might as well post it - this formula also seems to work...

=SUMPRODUCT(--ISNUMBER(-TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",99)),ROW(1:65536)*99-98,99))))

Hi Rick, is there any chance you can break down how this formula works so i can understand it. This will come very handy with a project im workin on at present

Would really appreciate if you could break this down for me in steps

thank you