asked on 8/19/2021

Custom format to align multiple values

All,

I am using TEXTJOIN function to concatenate a number of values and related text in a variance analysis summary as below:

=TEXTJOIN(CHAR(10),TRUE,
IF(Q32=0,"",$C$23&": "&TEXT(Q32/10^6," 0.0 ;(0.0)")&" "&R32),
IF(Q46=0,"",$C$37&": "&TEXT(Q46/10^6," 0.0 ;(0.0)")&" "&R46),
IF(Q60=0,"",$C$51&": "&TEXT(Q60/10^6," 0.0 ;(0.0)")&" "&R60),
IF(Q74=0,"",$C$65&": "&TEXT(Q74/10^6," 0.0 ;(0.0)")&" "&R74))

Open in new window

This looks at the values in the various cells in Q and takes Dept ID from C and shows with variance explanation from R; result being like below:

Dept1: (0.0) No Budget - Runs on actuals
Dept2: (0.5) No Budget - Runs on actuals
Dept3: (78.6) No Budget - Runs on actuals
Dept4:   0.1  No Budget - Runs on actuals

Open in new window

All Dept codes are the same length (4 characters) and values can be either positive or negative.

What I am trying to achieve is getting the values to be aligned at the decimal point. I am thinking that I can use the custom format within the TEXT function to pad as required. I only need one decimal point and the integer value should be no more than two digits. The result for above would be:

Dept1: ( 0.0) No Budget - Runs on actuals
Dept2: ( 0.5) No Budget - Runs on actuals
Dept3: (78.6) No Budget - Runs on actuals
Dept4:   0.1  No Budget - Runs on actuals

Open in new window

I am guessing it would be use of # and/or _ within the custom format but don't know what the syntax is for those.

Many thanks.

Microsoft Excel Microsoft Office

Last Comment

Rob Henson

8/20/2021

ASKER CERTIFIED SOLUTION

Martin Liss

8/19/2021

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Martin Liss

8/19/2021

However if you are willing to use a fixed-width font like Courier then this will work. It puts the output in column B. I've attached a sample workbook.
Assumptions:

Data starts in A1
The number to the left of the decimal is no more than 3 characters in length.

This could be turned into a UDF.

Sub AlignAtPoint()
Dim lngRow As Long
Dim lngLastRow As Long
Dim strParts1() As String
Dim strParts2() As String
Dim intNums As Integer
Dim intChar As Integer

With ActiveSheet
    lngLastRow = .UsedRange.Rows.Count
    
    For lngRow = 1 To lngLastRow
        strParts1 = Split(.Cells(lngRow, "A"), ":")
        strParts2 = Split(strParts1(1), ".")
        intNums = 0
        For intChar = 1 To Len(strParts2(0))
            If Asc(Mid(strParts2(0), intChar, 1)) > 46 And Asc(Mid(strParts2(0), intChar, 1)) < 58 Then
                intNums = intNums + 1
            End If
        Next
        If InStr(1, strParts2(0), "(") = 0 Then
            intNums = intNums + 1
        End If
        .Cells(lngRow, "B") = strParts1(0) & ":" & Space(4 - intNums) & strParts2(0) & "." & strParts2(1)
    Next
End With
End Sub

Open in new window

29222736.xlsm

Rob Henson

8/20/2021

ASKER

Hi Martin,

Thanks for suggestion, I'm not looking at VBA for this; I was hoping that I would be able to use a Custom format.

Its only an issue when any of the values go into double figures which doesn't happen often so I will accept that it can't be done.

I have also looked at comparing lengths of the 4 values and using REPT to add extra spaces but it makes the formula very unwieldy.