CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
Cornelius
Cornelius

Reputation: 1027

Excel formula to convert number to both feet and inches

I have a spreadsheet (excel 2010) where in column A I have a metric value (2413mm) and in column B I need a formula that will convert 2413mm to show the feet/inches in the following format (7' 11")

I can do this if I use 3 or 4 columns to first convert the 2413 to 7.9', then extract the feet and inches into two separate columns and finally concatenate the separate feet and inches into a final cell.

I'd really like to be able to do all this within one cell.

Upvotes: 4

Views: 26880

Answers (4)

Novicaine
Novicaine

Reputation: 133

I know this is a really late answer, but I was searching for this and tried some of the suggested answers using a formula as the solution, which didn't work for me, because the resulting cell can no longer be used in calculations.

So I converted from meters to decimal feet in one cell, then used a Format (not formula) to display decimal feet as "feet and inches" by using a Custom Format:

A Custom Format of # 0/12 on the cells will show 6.00001 (6 feet) as 6 0/12 and 6.500 (6 feet 6 inches) as 6 6/12.

A Custom Format of # #/12 will show 6.00001 as simply 6 and 6.500 as 6 6/12.

This technique also handles negative numbers perfectly.

(The inches are shown as inches/12 instead of inches" but for my use, that was fine.)

Upvotes: 0

Dan Ashby
Dan Ashby

Reputation: 1

I wrote a User Defined Function to deal with feet and inches once and for all. I've shared the code and a downloadable VBA module here:

https://engineerstoolkit.wordpress.com/2017/03/05/dealing-with-feet-and-inches-in-excel/

If you install that, you can display the result (to any precision you desire) using the formula: =FtToString(A1/25.4/12) Dividing by 25.4 converts the millimeters to inches, and dividing again by 12 converts it to feet (which is the assumed input for my formula).

Upvotes: 0

barry houdini
barry houdini

Reputation: 46371

I notice that Gary's Student's answer will give you decimals in the inches - if you want the answer to the nearest inch try this version

=INT(ROUND(A1*0.03937,0)/12)&"' "&MOD(ROUND(A1*0.03937,0),12)&""""

Upvotes: 6

Gary's Student
Gary's Student

Reputation: 96773

With data in A1, in B1 enter:

=INT(CONVERT(A1,"mm","ft")) & " '" &  12*(CONVERT(A1,"mm","ft")-INT(CONVERT(A1,"mm","ft"))) & """"

Upvotes: 2

Related Questions