PDA

View Full Version : convert to feet and inches



CloudenL
10-24-2011, 10:48 AM
I am running excel 2007. I have figured out the convert function to convert 42 and make it 3.5. but i want it to show 3'5" not 3.5. is there a way to do that in the format cells section or do i have to do this manually?

convert(42,"in","ft")

p45cal
10-24-2011, 11:15 AM
If you go on like this, you kitchen cabinets won't fit properly - hopefully you want to show it as 3' 6", 42 inches being 3 1/2 feet, 3 feet and 6 inches, not 3 feet and 5 inches!

Anyway, I haven't found formatting that can do it, but can give you some formulae to do it:
The value 42 is in E10:
=INT(E10/12) &"' "& MOD(E10,12)&""""
or if you want to use convert:
=INT(CONVERT(E10,"in","ft"))&"' "&MOD(E10,12)&""""

The problem here is that ther result is a string and you can't do maths on it. I'll do a little more research.

edit: OK, I've done some research and it seems formatting won't do it, but there were some good solutions out there:
http://www.mrexcel.com/forum/showthread.php?t=60665
http://www.mrexcel.com/forum/showthread.php?t=29377
http://www.mrexcel.com/forum/showthread.php?t=29333
http://www.mrexcel.com/articles/excel-feet-to-inches.php (a macro solution)
http://www.mrexcel.com/forum/showthread.php?t=72114

CloudenL
10-24-2011, 12:22 PM
Thank you, the formula worked perfectly. Luckily im not trying to hang kitchen cabinets :) but i see where the convert did me wrong in just making it 3.5. I dont think i need to do any math off of these values, mostly for a drop down. But if i do, i'll be sure to check the solutions you listed above.