VBA code to count anything number over 5 digits per row

[Kingchaos64]

Hello

I would like a code that would count all numbers over 4 digits per cell, per row and that total count per row would show up in that rows C Column.

I have a formula that kinda works but it gets easily messed up and since this will be used by other people I need something that can't be accidentally deleted or changed.

 

[Rick Rothstein]

What type of data is in your cells... pure numbers or numbers mixed with text?

If numbers mixed with text, do you want to count all the digits whether they are all next to each other or not... or only 5 or more digits that are together?

You thread title says "over 5 digits" but your description says "over 4 digits"... which is it over 4 or 5 digits?

 

[Kingchaos64]

What type of data is in your cells... pure numbers or numbers mixed with text?

If numbers mixed with text, do you want to count all the digits whether they are all next to each other or not... or only 5 or more digits that are together?

You thread title says "over 5 digits" but your description says "over 4 digits"... which is it over 4 or 5 digits?

Cells will vary between text and numbers but the cells I would like count will only have numbers in them. Sorry about that, it would be over 4 numerical digits per cell.

 

[kweaver]

Here's one way I thought of using math:

Book1
	A	B	C
1	6923	945	1
2	text	655
3	1727	3705	2
4	990	50	0
5	5489	4567	2
6	25	60	0
7	7467	70	1
8	5058	text12345
9	16500	1250	2
10	921	2535	1
Sheet1

Cell Formulas
Range	Formula
C1:C10	C1	=IFERROR((1*(TRUNC(LOG(A1)+1)>=4))+1*(TRUNC(LOG(B1)+1)>=4),"")

 

[Kingchaos64]

Here's one way I thought of using math:

Book1
	A	B	C
1	6923	945	1
2	text	655
3	1727	3705	2
4	990	50	0
5	5489	4567	2
6	25	60	0
7	7467	70	1
8	5058	text12345
9	16500	1250	2
10	921	2535	1
Sheet1

Cell Formulas
Range	Formula
C1:C10	C1	=IFERROR((1*(TRUNC(LOG(A1)+1)>=4))+1*(TRUNC(LOG(B1)+1)>=4),"")

Wow that's some code lol.. I'll try it and see if it works for me. Thank you

 

[Kingchaos64]

Here's one way I thought of using math:

Book1
	A	B	C
1	6923	945	1
2	text	655
3	1727	3705	2
4	990	50	0
5	5489	4567	2
6	25	60	0
7	7467	70	1
8	5058	text12345
9	16500	1250	2
10	921	2535	1
Sheet1

Cell Formulas
Range	Formula
C1:C10	C1	=IFERROR((1*(TRUNC(LOG(A1)+1)>=4))+1*(TRUNC(LOG(B1)+1)>=4),"")

Wait is this a formula or vba code?

 

[kweaver]

It's a formula in C1 and filled down.

If, in my example, you want C2 to be 0 and C8 to be 1, you can use

=IFERROR((1*(TRUNC(LOG(A1)+1)>=4)),0)+IFERROR(1*(TRUNC(LOG(B1)+1)>=4),0)[code]

 

[Rick Rothstein]

It's a formula in C1 and filled down.

If, in my example, you want C2 to be 0 and C8 to be 1, you can use

=IFERROR((1*(TRUNC(LOG(A1)+1)>=4)),0)+IFERROR(1*(TRUNC(LOG(B1)+1)>=4),0)[code]

Here is an array-entered** formula that will also work...

=IF(ISNUMBER(A1:B1),COUNTIF(A1:B1,">999"),0)

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

 

[jtakw]

Hi,

May be I'm missing something...can we just use COUNTIF...

Assuming you mean Whole numbers, otherwise, just a slight modification needed for the formulas.
Also, you did say Over 4 digits, so use D1 formula, if 4 digits are to be included, than use C1 formula.

Book3.xlsx
	A	B	C	D
1	6923	945	1	0
2	text	655	0	0
3	1727	3705	2	0
4	990	50	0	0
5	5489	4567	2	0
6	25	60	0	0
7	7467	70	1	0
8	5058	text12345	1	0
9	16500	1250	2	1
10	921	2535	1	0
11	999.2	999.9	0	0
12	1000.2	1000.9	2	0
13	9999.9	9999.1	2	0
14	10000.1	10000.9	2	2
15			4 digits +	5 digits +
Sheet760

Cell Formulas
Range	Formula
C1:C14	C1	=COUNTIF(A1:B1,">=1000")
D1:D14	D1	=COUNTIF(A1:B1,">=10000")

 

[Kingchaos64]

Thank you for all the posts. I'm trying to stay clear of formulas. Would rather have a vba code if possible.