Selected Answer
Please install this code in the code sheet of the worksheet on which you wish to have the action (for example "Sheet1 (Sheet1)"). Double-click to open the sheet in the right portion of the VB Editor's window) and paste the code there.
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' 21 Sep 2017
If IsTarget(Target) Then
With Target
.HorizontalAlignment = xlLeft
.Font.Name = "Consolas"
' "Courier New" will give a better alignment
.NumberFormat = "@"
End With
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
' 22 Sep 2017
Dim Num As Variant
Dim Out As String
Dim n As Integer
If IsTarget(Target) Then
With Target
Num = Trim(.Value)
If IsNumeric(Num) Then
Out = String(4, Chr(32))
n = InStr(Num, ".")
If Int(Num) Or n > 1 Then Out = Right(Out & Str(Int(Num)), 4)
If n Then
Out = Out & "."
If Num > Int(Num) Then Out = Out & Mid(Num, InStr(Num, ".") + 1)
End If
.Value = Out
End If
End With
End If
End Sub
Private Function IsTarget(Target As Range) As Boolean
' 22 Sep 2017
Dim Rng As Range
Set Target = Target.Cells(1)
' A specifies the column where you have your numbers
' 2 specifies the first row in which a number may occur
' Row 1 may not be blank!
' +1 means that the macro will run when you enter a number
' in the first blank cell below the last used cell (in column "A")
Set Rng = Range(Cells(2, "A"), Cells(Cells(65536, "A").End(xlUp).Row + 1, "A"))
IsTarget = Not (Application.Intersect(Target, Rng) Is Nothing)
End Function
As it is the code will run when you make a change in any cell in column A except row 1 and not more than 1 row below the last used cell in that column. If you have entered a number that number will be formatted and returned to the cell in the format you have requested. You can change the column yourself, or the first action row. If you have blanks in that column the code will need to be changed: that is the part of the code which determines when to do the formatting. It may be possible to simple look for the last row in another column than the one in which the action occurs. ("Cells(65536, "A").End(xlUp).Row + 1" looks for the last row in column A.
The formatting requires that a fixed space font is used, meaning one where all numbers (1 as well as 9) take up the same space. "Courier" or "Courier New" is such a font. I have used "Consolas" which isn't perfect but I felt good enough. You might change the font name in the code to try others.