Can I (or How can I) format cells so that I can input my information in feet
and inches? Maybe something like:

5'3"

Everything would be in whole inch increments, so I don't need to bother with
fractions. My information is coming to me in feet and inches, and it's a real
pain to have to get it all into decimal feet so I can use it. I need a format
- not a formula.

Can this be done?
How?
TIA!

On Nov 26, 1:06 pm, Minx wrote:
Can I (or How can I) format cells so that I can input my information in feet
and inches? Maybe something like:

5'3"

Everything would be in whole inch increments, so I don't need to bother with
fractions. My information is coming to me in feet and inches, and it's a real
pain to have to get it all into decimal feet so I can use it. I need a format
- not a formula.

Can this be done?
How?
TIA!

To enter the custom function,

start the VB editor with alt-F11.
InsertModule.
InsertProcedure.
Type feet as the name of the procedure and indicate it is a function.
Then, copy the following code:

Public Function feet(LenString As String)
Dim FootSign As Integer
Dim InchSign As Integer
Dim SpaceSign As Integer
Dim FracSign As Integer
Dim InchString As String
Dim Word2 As String
' Copyright 1999, 2005 MrExcel.com
LenString = Application.WorksheetFunction.Trim(LenString)
'The find function returns an error when the target is not found
'Resume Next will prevent VBA from halting execution.
On Error Resume Next
FootSign = Application.WorksheetFunction.Find("'", LenString)
If IsEmpty(FootSign) Or FootSign = 0 Then
' There are no feet in this expression
feet = 0
FootSign = 0
Else
feet = Val(Left(LenString, FootSign - 1))
End If

' Handle the case where the foot sign is the last character
If Len(LenString) = FootSign Then Exit Function
' Isolate the inch portion of the string
InchString = Application.WorksheetFunction.Trim(Mid(LenString,
FootSign + 1))
' Strip off the inch sign, if there is one
InchSign = Application.WorksheetFunction.Find("""", InchString)
If Not IsEmpty(InchSign) Or InchSign = 0 Then
InchString =
Application.WorksheetFunction.Trim(Left(InchString , InchSign - 1))
End If

' Do we have two words left, or one?
SpaceSign = Application.WorksheetFunction.Find(" ", InchString)
If IsEmpty(SpaceSign) Or SpaceSign = 0 Then
' There is only one word here. Is it inches or a fraction?
FracSign = Application.WorksheetFunction.Find("/", InchString)
If IsEmpty(FracSign) Or FracSign = 0 Then
'This word is inches
feet = feet + Val(InchString) / 12
Else
' This word is fractional inches
feet = feet + (Val(Left(InchString, FracSign - 1)) /
Val(Mid(InchString, FracSign + 1))) / 12
End If
Else
' There are two words here. First word is inches
feet = feet + Val(Left(InchString, SpaceSign - 1)) / 12
' Second word is fractional inches
Word2 = Mid(InchString, SpaceSign + 1)
FracSign = Application.WorksheetFunction.Find("/", Word2)
If IsEmpty(FracSign) Or FracSign = 0 Then
' Return an error
feet = "VALUE!"
Else
If FracSign = 0 Then
feet = "VALUE!"
Else
feet = feet + (Val(Left(Word2, FracSign - 1)) /
Val(Mid(Word2, FracSign + 1))) / 12
End If
End If
End If
End Function
This is a handy one that I got from Mr. Excel
Jay

Looks like a great formula, but I'm not sure how I can use that as a
cell/number format. Can you help me understand a little better? My VBA isn't
the best, and I just don't know how to use a formula as a format.

I don't need anything where "put this formula in A1 and your number in B2"
because that isn't what the powers that be are asking me to do. They want me
(and everyone else) to be able to type in 6'2" and have excel "understand"
that means 6.1666667 so that all our calculations come out right.

As a quick workaround use Custom format # ??/12
Now you can type 5 3/12 which is just as quick as typing 5' 3" (quicker
maybe)
And you can still do math on it (calculate areas, etc)

If you must use 5'3" see
http://www.cpearson.com/excel/FeetInches.htm
To get started with VBA see David McRitchie's site
http://www.mvps.org/dmcritchie/excel/getstarted.htm

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Minx" wrote in message
...
Can I (or How can I) format cells so that I can input my information in
feet
and inches? Maybe something like:

5'3"

Everything would be in whole inch increments, so I don't need to bother
with
fractions. My information is coming to me in feet and inches, and it's a
real
pain to have to get it all into decimal feet so I can use it. I need a
format
- not a formula.

Can this be done?
How?
TIA!

So...the answer is "No", there isn't actually a way to format cells where
5'3" can be used numerically?

I may end up using the shortcut format for my own work. I'll just have to
tell everyone that we just can't do what they really want. I like the VBA
(which might get used for my personal calculations), but it won't set the
spreadsheets up the way the client is used to seeing things.

*sigh*
Strange - I wouldn't think that this is such an unusual idea. I'm surprised
that Microsoft won't allow it.

Thanks all for the help!