Display feet inches and fraction
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
|