Find last three digits after . ( including zero)

LearnerExcel · #1 02-06-2018, 12:53 AM

How to get last 3 digits after "." (Decimal) including zero ? The following formula doesn't work?

Code:

 =RIGHT(A1,3)

Example: 88,485.90 ----------- The result is 5.9 (But I need here .90 inculding last ZERO)
88,485.95 ----------- The result is .95 (Here it is okay as it gets last 3 digits after Decimal)

How to modify the formula?

LearnerExcel · #2 02-06-2018, 01:11 AM

Code:

 
=A1-TRUNC(A1)

Problem resolved.

Alsadius · #3 02-06-2018, 06:18 AM

Quote:

Originally Posted by LearnerExcel

Code:

 
=A1-TRUNC(A1)

Problem resolved.

That'll get him all decimals, not just the last three.

Assume we're dealing with 123.456789.

=A1-TRUNC(A1)
will result in 0.456789

=ROUND(A1-TRUNC(A1), 3)
will result in 0.457 (it rounds it off to three decimal places)

=(TRUNC((A1-TRUNC(A1))*1000))/1000
will result in 0.456 (this is a nasty formula, but it basically shifts the decimal three places and re-truncates)

=RIGHT(A1-TRUNC(A1), 3)
should result in 789 (though this one gave me a floating-point error when I tried it, which may mean it's bug-prone in practice and needs caution)

Which one you want depends on exactly what you're trying to do, but hopefully that should cover all the bases.

ArviLaanemets · #4 02-06-2018, 09:35 AM

Code:

=rounddown(a1,3)-trunc(a1)

jolivanes · #5 02-08-2018, 10:49 AM

Code:

 
=text(right(a1, 3),"0.00")

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Having number's digits together	mohsen.amiri	Word	0	06-23-2017 01:20 AM
Digits are not found in my document!	reza8615953	Word	3	04-18-2017 10:48 AM
Macro for find/replace (including headers and footers) for multiple documents	jpb103	Word VBA	2	05-16-2014 04:59 AM
Multi-Doc Find/Replace, Including Headers/Footers & Textboxes	konopca	Word VBA	3	05-05-2014 03:18 PM
Problems merging in last 4 digits of an account higher than 16 digits	Glynda	Mail Merge	1	04-08-2011 12:17 AM