CODEWITHSUNDEEP
excel
Mark T
Mark T

Reputation: 155

How to format a four-digit number + special decimals in Excel

I have a dataset in Excel with numbers like this:

3254539306640620
325.403.955.078.125
3.254.041015625

etc..

What I need is to format the numbers to four digits + 2 decimals rounded to .00, .25, .50, .75

In the end the format should look like this, according to the numbers in the example:

3254.50
3254.25
3254.00

Any Ideas how to accomplish this in Excel? Thank you!

Upvotes: 0

Views: 192

Answers (1)

Scott Craner
Scott Craner

Reputation: 152525

First remove all the . with SUBSTITUTE. Then use REPLACE to put one . in the 5th position. Then use MROUND to round:

=MROUND(REPLACE(SUBSTITUTE(A1,".",""),5,0,"."),0.25)

enter image description here

If . is not your decimal separator then use this:

=REPLACE(MROUND(LEFT(SUBSTITUTE(A1;".";"");6);25);5;0;".")

Upvotes: 2

Related Questions