How can i convert feet-inches to just feet in excel
2

Question

How can i convert feet-inches to just feet in excel

Ted87 (Structural)

(OP)

19 May 15 21:26

Hi folks,

I was wondering how do i convert 5'-9" to 5.75 ft in excel. Any help is appreciated.

Ted.

SkipVought (Aerospace) · Answer 1 · 2015-05-19 21:37:00

Hi,

Is this a column of ft-in dimensions?

If so I'd REPLACE the ' & " characters with nothing and then use Data > Text to columns DELIMITED by -, so you have a column for ft and another for in.

Then it's a simple formula.

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}

Ted87 (Structural) · Answer 2 · 2015-05-19 21:48:02

SkipVought,

I need the fee-inches to be displayed as #'-#". Do you know of a formula which can take this and convert to strictly inches or feet.

I know the formula to convert 5.75 to 5'-9" (see below), but was not able to do the reverse.
=INT(A2)&"'-"&TRIM(TEXT(ROUND(MOD(A2,1)*12*16,0)/16,"# ??/??")&"""")

SkipVought (Aerospace) · Answer 3 · 2015-05-19 21:54:28

Quite simple to concatenate values to achieve that format...

=A1&"'-"&B1&""""

where A1 is 5 B1 is 9

Result: 5'-9"

Skip,
_{Just traded in my OLD subtlety...

for a NUance!}

IDS (Civil/Environmental) · Answer 4 · 2015-05-19 22:14:21

The blog post at:
https://newtonexcelbach.wordpress.com/2014/05/31/f...

looks at various ways of converting to and from a text string containing feet and inches, using both on-sheet formulas and VBA user defined functions.
There is a download file including all the formulas:
http://interactiveds.com.au/software/Units4Excel.z...

and also a general unit conversion spreadsheet which will convert a wide range of other units, and also do unit aware evaluation:
http://interactiveds.com.au/software/Units4Excel.z...

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

zelgar (Civil/Environmental) · Answer 5 · 2015-05-20 12:42:03

If you want it done as a formula:

CODE -->

=CONCATENATE(INT(B42),"'-",((B42-INT(B42))*12),"""")

If you want a custom Function ff2fi(x):

CODE -->

Function ff2fi(x As String) As String
Dim c
c = Split(x, ".")
feet = c(0)
inches = c(1) / 100 * 12
ff2fi = feet & "'- " & inches & """"
End Function

Ted87 (Structural) · Answer 6 · 2015-05-20 14:58:23

Zelgar,

I think this formula converts feet to feet-inch, i want to the opposite (convert feet-in to feet).

IRstuff (Aerospace) · Answer 7 · 2015-05-20 15:13:40

thread770-161789: Inches to Feet-Inches
thread724-365451: An Excel conversion question

TTFN
FAQ731-376: Eng-Tips.com Forum Policies
[IMG http://tinyurl.com/7ofakss]
Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers

Of course I can. I can do anything. I can do absolutely anything. I'm an expert!
There is a homework forum hosted by engineering.com: http://www.engineering.com/AskForum/aff/32.aspx

zelgar (Civil/Environmental) · Answer 8 · 2015-05-20 16:34:00

Sorry.... I was going the opposite decimal feet to feet & inches....
Cell formula:

CODE -->

=LEFT(D46,FIND("'",D46)-1)+LEFT(RIGHT(D46,LEN(D46)-FIND("-",D46)),LEN(D46)-FIND("-",D46)-1)/12

Function

CODE -->

Function fi2ff(x As String) as Single
Dim d
d = Split(x, "-")
feet = (Left(d(0), Len(d(0)) - 1))
decft = (Left(d(1), Len(d(1)) - 1)) / 12
fi2ff = feet + decft
End Function

Ted87 (Structural) · Answer 9 · 2015-05-20 16:44:25

Perfect that works!!

Thanks you all for all your help, really appreciate it.

Come Join Us!

Posting Guidelines

Related Projects

Jobs

How can i convert feet-inches to just feet in excel
2