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

[Ted87]

Hi folks,

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

Ted.

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Ted87]

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]

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

[tt]
=A1&"'-"&B1&""""
[/tt]
where A1 is 5 B1 is 9

Result: 5'-9"


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[IDS]

The blog post at:

https://newtonexcelbach.wordpress.com/2014/05/31/feet-and-inches-and-other-units/

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.zip

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.zip

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[zelgar]

If you want it done as a formula:

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

If you want a custom Function ff2fi(x):

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]

Zelgar,

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

 

[IRstuff]

thread770-161789
thread724-365451

TTFN
faq731-376

Need help writing a question or understanding a reply? forum1529


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]

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

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

Function

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]

Perfect that works!!


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