# Error checking of digits / integers to right of decimal point.

#### BillTony

##### Board Regular
Hi there,

I coding a project and having a problem with "counting" the integers to the right of the decimal point in a given cell.

In a nutshell...

1. Blank = Error.
2. na = Correct.
3. 1.5 = Error. Must have between 2 and 4 (inclusive) digits to the right of the decimal point.
4. 7.62 = Correct.
5. 5.96317 = Error. Too many digits to right.

I have no limit to the number of digits to the left of the decimal point (e.g., 9999999999999.965 would be correct, while 9999999999999.7 would be an error).

Thanks in advance!

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

#### Fennek

##### Active Member
Hallo,

try something like this:

Code:
``````d = val(mid(cells(i,j).text, instr(1,cells(i,j),".")))
if d > 1 and d < 5 msgbox "ok"``````

regards

#### Joe4

##### MrExcel MVP, Junior Admin
Are you looking for a VBA or a formulaic solution?
Using a helper column, you can use this formulaic solution.
For an entry in cell A1, put this formula in B1 to count the number of characters after the decimal point:
Code:
``=IF(ISNUMBER(FIND(".",A1)),LEN(A1)-FIND(".",A1),0)``
and then put this formula in C1 to get what you want:
Code:
``=IF(A1="na","Correct",IF(AND(B1>=2,B1<=4),"Correct","ERROR"))``

BTW, you could wrap it all up in one nasty-looking formula (without a helper columns) like this:
Code:
``=IF(A1="na","Correct",IF(AND(IF(ISNUMBER(FIND(".",A1)),LEN(A1)-FIND(".",A1),0)>=2,IF(ISNUMBER(FIND(".",A1)),LEN(A1)-FIND(".",A1),0)<=4),"Correct","ERROR"))``

#### BillTony

##### Board Regular
VBA, unfortunately...

A little background is that this will be integrated into an ongoing or adaptive dataset used for multiple projects with varying requirements - coded in VBA - which, unfortunately change radically over time.

Helper columns, while obviously useful, will simply increase the size of a file which is already unwieldy!

Any help is ALWAYS appreciated.

#### Joe4

##### MrExcel MVP, Junior Admin
OK. I did also give you a solution WITHOUT helper columns, and Fennek gave you a VBA solution.
Does either of those work for you?

#### BillTony

##### Board Regular
Yes, thanks.

Your solution worked quite well!

#### Rick Rothstein

##### MrExcel MVP
BTW, you could wrap it all up in one nasty-looking formula (without a helper columns) like this:
Code:
``=IF(A1="na","Correct",IF(AND(IF(ISNUMBER(FIND(".",A1)),LEN(A1)-FIND(".",A1),0)>=2,IF(ISNUMBER(FIND(".",A1)),LEN(A1)-FIND(".",A1),0)<=4),"Correct","ERROR"))``
I think this shorter, slightly less nasty-looking formula will produce the same results...

=IF(A1="na","Correct",IF(ABS(LEN(0+A1)-LEN(INT(0+A1))-4)<=1,"Correct","ERROR"))

Its main relationship test is built around this generalized relationship for testing whether a value X lies within or on a range of values starting at value A and ending at value B...

ABS(X - ((A + B) / 2)) < ABS(B - A) / 2

where LEN(0+A1)-LEN(INT(0+A1))-1 is what X equals in the generalized relationship, A equals 2 and B equals 4.

Last edited:

Replies
3
Views
273
Replies
5
Views
240
Replies
2
Views
207
Replies
0
Views
506
Replies
3
Views
234

Threads
1,186,139
Messages
5,956,124
Members
438,234
Latest member
Sp922

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back