How can I format a cell so that when I enter 12.5 it shows 12'6"? I am
trying to do this so in another cell I can multiply the length by another
number.

You can use this formula to convert the value 12'6"" to the numeric value
12.6 in another cell...

=--SUBSTITUTE(SUBSTITUTE(A1,"'","."),"""","")

or you can use this...

SUBSTITUTE(SUBSTITUTE(A1,"'","."),"""","")

directly in a calculation involving other numeric values (no double unary is
required under this circumstance). You don't show any spaces between the
foot-mark and the number of inches; however, if that could occur, then you
would need to remove it. That can be done by adding another SUBSTITUTE
function call...

=--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"'","."),"""","")," ","")

--
Rick (MVP - Excel)


"Ted M." <Ted M.@discussions.microsoft.com> wrote in message
news:ADDD42BA-37AD-4CC4-AC77-(E-Mail Removed)...
> How can I format a cell so that when I enter 12.5 it shows 12'6"? I am
> trying to do this so in another cell I can multiply the length by another
> number.