Aligning decimals with varying decimal places


I need to align a column of study numbers that vary in the number of decimal places, e.g., 







As these refer to study numbers, the numbers after the decimal point need to remain the same, meaning there can't be trailing zeroes added (17.1 must stay as 17.1 and not become 17.10 which is an entirely different study). Nor can the numbers be truncated as in 17.10 becoming 17.1 which is again a different study.



You will need VBA to achieve that result, meaning a macro-enabled workbook. The workflow would be that you enter a number and VBA formats it for you as you move to the next cell. Therefore it must be known which cells to format and which not. You probably have a column in which these numbers appear.
Note that the result will be strings which it is not straight forward to do calculations with. If you need to do any calculations with these numbers it must be known beforehand.
I notice that your last example (102.1) doesn't have the decimal point aligned with the others. Is that intentional? Once you start using code to do the job it can be done whichever way you want.
Variatus (rep: 813) Sep 20, '17 at 9:43 pm
I can use VBA but I wouldn't know how to approach the problem. The alignment in my question is a coincidence of the entering format but the desired outcome in practice is a 'centered' list with decimals aligned regardless of the number of digits or decimal places. When I say 'centered' I really mean the decimals should be centered or alternatively there could be a variable indent depending on the number of digits. I will not need to perform any calculations, this is a list of study numbers.
begreen12 (rep: 2) Sep 20, '17 at 10:57 pm
Add to Discussion


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.



Oh so close. From my question, " Nor can the numbers be truncated as in 17.10 becoming 17.1 which is again a different study." The code works for everything except 17.10 which is being truncated to 17.1 (third in the copied list) and similarly for 102.10 (added on the trial worksheet; last in the copied list). I like the way it works on the numbers as they are entered. The copied cells all were in alignment to the decimal point in excel.
 101.1   17.1   17.1   17.19   17.9  102.1  102.1
begreen12 (rep: 2) Sep 21, '17 at 9:04 am
I see that I can go back to the 17.10 which became 17.1 and add the zero manually. Same for 102.10. Adding a digit manually doesn't interfere with the code continuing to work! Great job!
begreen12 (rep: 2) Sep 21, '17 at 9:17 am
I think you can avoid this behaviour by formatting the cell as text before you enter anything. I tried to get control of this by moving the various formatting declarations within the code and thought I had succeeded :-)
Basically, if the cell's format was "General" and you enter 17.10 the display will be 17.1. But if you enter 17.10 as text the display will be 17.10. So, at least, after the code has run on a cell once its format will be text, meaning you can add the zero or retype the entire number with same effect.
I have modified the code to do the formatting when you select an eligible cell and modify the entered number only when you make a change. Please replace the code you have with the new one above.
Variatus (rep: 813) Sep 21, '17 at 9:50 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login