# Removing Whole Numbers

#### DIB

##### New Member
Hi,

I'm trying to create a formula that will take into account the following:

I'm converting a site chainage (in metres) into miles and yards.

For example 268022 metres into 166 miles 950 yards - easily done into decimel [multiply 268022 by 1.0936, then divide by 1760 to give 166.539]. I then want to be able to calculate the decimel places (.539) into yards [by multiplying by 1760].

However my problem is how to remove the whole number (166) from the equation in order to be able to convert the decimel places?!

Any suggestions?

DIB

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
You could use this formula

=INT(A1/1609.3635)&" miles "&TEXT(MOD(A1*1.0936,1760),"0")&" yards"

where A1 contains the distance in metres

Note: excel has it's own CONVERT function available with Analysis ToolPak

you could use that in a similar manner, e.g.

=INT(CONVERT(A1,"m","mi"))&" miles "&TEXT(MOD(CONVERT(A1,"m","yd"),1760),"0")&" yards"

When I tested these given your example I got slightly different results

Superb! Thats perfect for my needs.

Many thanks!

Replies
0
Views
798
Replies
2
Views
352
Replies
1
Views
4K

1,207,256
Messages
6,077,319
Members
446,278
Latest member
hoangquan2310

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