On Friday, February 8, 2008 at 3:35:40 PM UTC-5, Rick Rothstein (MVP - VB) wrote:
I would like to be able to input 11 3 1/2 and have the cell display
as 11'-3 1/2" Is this possible?

Right-click the sheet tab for the sheet you want this functionality on and
select View Code on the popup menu that appears. Copy/Paste the following
into the code window that opened up in the VBA editor...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long
Dim TargetPattern As String
TargetPattern = Target.Value
For X = 1 To Len(Target.Value)
If InStr(" /", Mid$(TargetPattern, X, 1)) = 0 Then
Mid$(TargetPattern, X, 1) = "#"
End If
Next
On Error GoTo Whoops
Application.EnableEvents = False
If Target.Value Like TargetPattern Then
Target.Value = Replace(Target.Value, " ", "'-", , 1) & """"""
End If
Whoops:
Application.EnableEvents = True
End Sub

If you enter a number in the format you showed (with only single spaces
between the numbers), then it will be reformatted the way you want.

Rick

Have you tried custom formatting and using
A1 = 157.75 inches

set A2 custom format to - # ?/??
A2 = A1
A2 should now be 157 3/4

or if you want you can build your result directly in the cell
A1 = 157.75 inches
A2=INT(A1/12)&"' "&INT(12*(A1/12-INT(A1/12))) & "-" & TEXT(INT(MOD(12*(A1/12-INT(A1/12)),1)*16),"#")&"/16"&CHAR(34)
A2 will display 13' 1-12/16"

A1 = 157.75 inches
A2 = A1 &" is equal to "&INT(A1/12)&" feet and "&INT(12*(A1/12-INT(A1/12))) & " " & TEXT(INT(MOD(12*(A1/12-INT(A1/12)),1)*16),"#")&"/16 of an inch"&CHAR(34)
A2 should now say "157.75 is equal to 13 feet and 1 12/16 of an inch"

try them all
Cheers, Patrick