## Formula or Macro

### Formula or Macro

Hi All,

I need to convert millimeters into feet and inches and round down to the nearest inch. For example: 2500 mm = 8'-2". I would like the cell to acually display the ' and ".

A bit stuck on this one.

Thanks!

Jim
JAR

Posts: 2
Joined: Wed Jan 23, 2008 4:30 pm

### Re: Formula or Macro

This appears to work for Inches to Feet and Inches.
=INT(A1/12)&"' - "&MOD(A1;12)&""""
I don't know the divisor for centimeters.
JohnV
Volunteer

Posts: 1585
Joined: Mon Oct 08, 2007 1:32 am
Location: Kentucky, USA

### Re: Formula or Macro

You can use custom format codes to add units so if A1=2500 then with a cell formula =A1/304.8format of #.0 "Ft" will display 8.2 Ft

However you can't work base 12 and you can't infix so you need a dog of a formula:
=INT(ROUND(A1/25.4)/12)&"'-"&MOD(ROUND(A1/25.4);12)&""""
This will display 8'-2". Note that you put two " characters inside a string to get one displayed.

You need to do the explicit round first because you want 2' 11.888" to display as 3' 0" not 2' 0".
Ubuntu 11.04-x64 + LibreOffice 3 and MS free except the boss's Notebook which runs XP + OOo 3.3.
TerryE
Volunteer

Posts: 1402
Joined: Sat Oct 06, 2007 10:13 pm
Location: UK

### Re: Formula or Macro

A1: value in mm (positive or negative)

[Edit: the ft-portion needs to be cut, not rounded]
C1: =INT(ABS(B1))
D1: =(ABS(B1)-C1)
F1: =TEXT(C1*SIGN(B1);"0'")&"-"&TEXT(E1;"0""")
Last edited by Villeroy on Wed Jan 23, 2008 6:32 pm, edited 2 times in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4

Villeroy
Volunteer

Posts: 28851
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

### Re: Formula or Macro

You need to do the explicit round first because you want 2' 11.888" to display as 3' 0" not 2' 0".

Actually 2' 11.888" I need to display as 2'-11"

So this should work ????

=INT(ROUND(A1/25.4)/12)&"'-"&MOD(ROUND(A1/25.4);12)&""""

Thanks!
JAR

Posts: 2
Joined: Wed Jan 23, 2008 4:30 pm

### Re: Formula or Macro

huw
Volunteer

Posts: 417
Joined: Wed Nov 21, 2007 1:57 pm

### Re: Formula or Macro

JAR wrote:Actually 2' 11.888" I need to display as 2'-11"
Bugger, I missed the round down bit. As long as the values are positive then JohnV's formula is more like it: =INT(A1/304.8)&"'-"&MOD(INT(A1/25.4);12)&""""
Ubuntu 11.04-x64 + LibreOffice 3 and MS free except the boss's Notebook which runs XP + OOo 3.3.
TerryE
Volunteer

Posts: 1402
Joined: Sat Oct 06, 2007 10:13 pm
Location: UK