# Return the number to at least 2 decimal places.

#### Qj123

##### New Member
Hi all,

Is there possible by using VBA to convert to number to at least 2 decimal places if the original number is an integer or only 1 decimal place and remain unchanged if more than 2 decimal places.

Some Example:

 Before After 10.99 10.99 15 15.00 65.777 65.777 187.3 187.30 123.3686 123.3686 70 70.00

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Thank a lot.

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### Roderick_E

##### Well-known Member
 Format(210.6, "#,##0.00") would return '210.60' Format(210.6, "Standard") would return '210.60'

<TBODY>
</TBODY>

#### joeu2004

##### Banned user
Is there possible by using VBA to convert to number to at least 2 decimal places if the original number is an integer or only 1 decimal place and remain unchanged if more than 2 decimal places.

There are two separate issue; not sure which you mean.

In VBA:
Format(number,"0.00############")
range.NumberFormat = "0.00############"

In Excel, Custom format 0.00############.

You need to decide the max decimal places. That allows for up to 14 decimal places total.

#### Qj123

##### New Member
There are two separate issue; not sure which you mean.

In VBA:
Format(number,"0.00############")
range.NumberFormat = "0.00############"

In Excel, Custom format 0.00############.

You need to decide the max decimal places. That allows for up to 14 decimal places total.

Yes i asking formula to solve both 3 scenario below:

1) integer to 2 decimal place (ex 1 to 1.00)
2) number with 1 decimal place to 2 decimal place (ex 1.1 to 1.10)
3) number > 2 decimal place remain unchanged (ex 1.1111 to 1.1111)

By using this

Format(number,"0.00############")
range.NumberFormat = "0.00############"

It solve my problem.

Replies
2
Views
408
Replies
16
Views
824
Replies
11
Views
3K
Replies
4
Views
9K
Replies
3
Views
330

1,191,535
Messages
5,987,157
Members
440,082
Latest member
belodelokelo

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