# Can't find function

#### ScotTFO

##### Board Regular
I am not sure if something like this even exists.

I am trying to add a formula that's pretty extensive and the end result comes out looking like this SOMETIMES.

"545.0000003 KB"

Is there a function I can add to the formula to trim the numbers down to 2 decimal places?

I guess I could use the LEFT function but I was just wondering if there was a better wya that would do rounding too.

Last edited:

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### Jonmo1

##### MrExcel MVP
I"m assuming your formula is a concatenate of some sort...

the part of the formula that produces the NUMBER, you can use TEXT to set the dicimal places..

=CONCATENATE(TEXT(formula that produces the number,"0.00")," KB")

Note that the number will be ROUNDED...

#### RoryA

##### MrExcel MVP, Moderator
When you are building up the value, use the ROUND function on the numbers before you add the "KB" (or MB or GB) part.
=ROUND(1080/1024,2)
for example.

#### Mike Blackman

##### Well-known Member
Hi Scot,

Maybe Something like this?

=ROUND(SUBSTITUTE(A1,RIGHT(A1,2),""),2)&" "&RIGHT(A1,2)

#### ScotTFO

##### Board Regular
I tested them all and I think for my needs this one turned out to be the best solution but they all do work and I appreciate the help!

Yeah it's a bit of a beast of a code, and originally I did it all in VBA and it worked except now they are asking to have the cells update realtime so I am trying to replace all the VBA coding with these formulas so I am ending up with beasts, like this...

Code:
``=IF(F16-D16>=1073741824,TEXT((F16-D16)/1024/1024/1024,"0.00")&" GB",IF(F16-D16>=1048576,TEXT((F16-D16)/1024/1024,"0.00")&" MB",IF(F16-D16>=1024,TEXT((F16-D16)/1024,"0.00")&" KB",IF(F16-D16>=-1024,TEXT((F16-D16),"0.00"),IF(F16-D16>=-1048576,TEXT((F16-D16)/1024,"0.00")&" KB",IF(F16-D16>=-1073741824,TEXT((F16-D16)/1024/1024,"0.00")&" MB",IF(F16-D16<1073741824,TEXT((F16-D16)/1024/1024/1024,"0.00")&" GB")))))))``
The code is taking 2 cells that contain the byte sizes of folders that are pulled using VBA and the FileSystemObject. This formula is doing the math and finding out how much a user is over their alloted space and then formatting it into a common file size format that someone using this can understand.

Thanks again guys!

Replies
3
Views
374
Replies
5
Views
3K
Replies
1
Views
772
Replies
7
Views
454
Replies
17
Views
2K

1,190,894
Messages
5,983,430
Members
439,843
Latest member
PlanetFitness

### 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.

### Which adblocker are you using?

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

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