How do I get a right justified column of numbers

I have a column of numbers ranging from about 1 to about 999 (1 to 3 digits). I want these values to appear in a column in a report, right-justified.

<Disclaimer> Yes - I know Excel is not a report writer, and Yes - I know that if I want to think in COBOL I should go back to the dim and distant past </Disclaimer>

BUT

How do I get a column of numbers in a report (a series of strings) to show up right justified?

I've tried all sorts of format codes, and none of them work (they all shift the value to the left). I've worked around this in the past by prepending a string (comprised of spaces) of the maximum length adding the numerical value and then taking the rightmost characters:

a=1

result1=right(string(6," ") & a,6)

a=999

result2=right(string(6," ") & a,6)

t hi s seem slik a pre tty st o o o PID wa yt od ot hi ng s!


Can Excel VBA produce this rather basic request without requiring these contortions?

Tony (Head Against Wall Mode Again)

I am baffled by your request, because numeric values are justified to the right by default.

However, first you said "numbers" then you mentioned "strings." Are you using text strings to represent numbers? That's the first thing to be fixed.

Well, regardless, I am still baffled because you can simply select the column and click the "right justify" format button.

Can you please attach a sample file that illustrates your problem? See yellow banner at the top of the page.

right.jpg

Try

Columns(1).HorizontalAlignment = xlRight

Column A formatted as TEXT

Using a huge font does not make your question easier to understand.

Give us a sample file showing the problem.

I, like it appears all of the other respondents, were assuming that you were talking about writing your data to Excel, then using Excel's formatting to get the data to be right justified in a spreadsheet cell. This latest post suggests to me that you are not wanting to write data to Excel at all. What you are trying to do is create a text output (write to a text file??) and use something that looks like fixed width fields and have the data right justified in those text fields. Is that an accurate description of what you want?

If this is correct, I found this stackoverflow q&a that addresses this: https://stackoverflow.com/questions/...from-excel-vba Note that the strategy is to combine the number (as text) with a suitable number of space characters to get the desired text field (which is similar to what you are doing but without using a Format() function). The responder also mentions that the Print# statement has some toggles that can control how text is positioned within the overall print action.

Before wasting a lot of time chasing this particular wild goose, can you confirm that I have correctly understood the problem?

Originally Posted by tfurnivall

I have a column of numbers ranging from about 1 to about 999 (1 to 3 digits).

Oh, well that should be pretty straightforward....

Originally Posted by tfurnivall

several right-justified (and possibly even decimal point aligned) fields

....oh, wait.

Sub TestFormats()
    Dim i           As Long
    Dim j           As Long
    Dim MaxDigits   As Long
    Dim TestDigits  As Long
    Dim LeftPads   As String
    Dim Str As String
    
    MaxDigits = 7   'min of one space and up to 6 digit num
    LeftPads = WorksheetFunction.Rept(" ", MaxDigits)
    Randomize
    For i = 1 To 4
        j = Int(10 ^ (i - 1) * Rnd())
        TestDigits = Len(CStr(j))
        Str = Left(LeftPads, MaxDigits - TestDigits) & j
        Debug.Print i & ": " & j, Str, Left(LeftPads, MaxDigits - TestDigits) & j
    Next i
End Sub

The answer to this will depend on how you are presenting the resulting text file. Your example shows writing to the Immediate Window. Are you actually writing to an ASCII text file?

Because you said you are printing strings (not numeric values) you can use

Format(s, "@@@")

to insert spaces to the left to pad out the string s to three characters.

Padding with spaces will work only if you are presenting this using a fixed-width font. If you are using a proportional font, this is going to be difficult/impossible.

It seems to me that you should just use RSet

Forgot about RSet and LSet. Can't remember last time I saw them used...