extract decimal place digit

[craig_100]

I have a large table of data which, based on the SUM formulae I have put in for some columns, apparently have been mis-keyed with 3 decimal places, instead of only 2. I need to find out which values have 3 decimal places so I can correct them. Suggestions? Will a formula, using various functions, work? Thanks.

[CLR]

Assuming your data is in column A, Put this in B1 and copy down............

=IF(LEN(A1-INT(A1))>4,"yes","")

Then Data > Filter > Autofilter on the "yes's"


Vaya con Dios,
Chuck, CABGx3



"craig_100" wrote:

>
> I have a large table of data which, based on the SUM formulae I have put
> in for some columns, apparently have been mis-keyed with 3 decimal
> places, instead of only 2. I need to find out which values have 3
> decimal places so I can correct them. Suggestions? Will a formula,
> using various functions, work? Thanks.
>
>
> --
> craig_100
> ------------------------------------------------------------------------
> craig_100's Profile: http://www.excelforum.com/member.php...o&userid=29187
> View this thread: http://www.excelforum.com/showthread...hreadid=489139
>
>

[Bernie Deitrick]

Craig,

For a number in cell A1:

=IF(LEN(A1)-FIND(".",A1)>2,"Extra digit(s)","")

Change the A1's to be your upper left cell address, then copy to a table the same size as your data
table (same number of columns and rows).

HTH,
Bernie
MS Excel MVP


"craig_100" <[email protected]> wrote in message
news:[email protected]...
>
> I have a large table of data which, based on the SUM formulae I have put
> in for some columns, apparently have been mis-keyed with 3 decimal
> places, instead of only 2. I need to find out which values have 3
> decimal places so I can correct them. Suggestions? Will a formula,
> using various functions, work? Thanks.
>
>
> --
> craig_100
> ------------------------------------------------------------------------
> craig_100's Profile: http://www.excelforum.com/member.php...o&userid=29187
> View this thread: http://www.excelforum.com/showthread...hreadid=489139
>

[craig_100]

Thanks for your excellent solutions. Worked perfectly; problem solved!