asked on

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.

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Martin Liss

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

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.