How do I autofill spaces in right justified fields?
 

I've set up fixed length fields. My data is right justified. I want to be
able to save to a text file and retain the data with fixed length fields,
with leading spaces to pad the right justified data so everything lines up
the way I entered it.

How do I autofill spaces in right justified fields?
 

On Jul 17, 10:40*pm, Bob_Balch
wrote:
I've set up fixed length fields. *My data is right justified. *I want to be
able to save to a text file and retain the data with fixed length fields,
with leading spaces to pad the right justified data so everything lines up
the way I entered it.

couldn't find a way to have excel format it, so here are some
solutions I came up with.
in the following, you would still need to decide what to do when the
contents are longer than the fixed width specified... i'll leave that
up to you.
(1) could have a worksheet that references the other and adds the
padding via formulas. Then save that sheet as the text file.
=REPT(" ", $A$1 - LEN(Sheet1!A2)) & Sheet1!A2
$A$1 is the fixed width

(2) vba routine to sweep a specified sheet and make each cell fixed
width

Sub processSheets()
Const FIXED_WIDTH As Long = 10
Dim sh As Worksheet

'could loop for all sheets
Set sh = ActiveSheet
applyFixedWidth FIXED_WIDTH, sh

End Sub

Sub applyFixedWidth(fixedWidth As Integer, sh As Worksheet)

Dim rng As Range
Dim elem As Range
Dim length As Long

If (fixedWidth < 0) Then Exit Sub

For Each elem In sh.UsedRange
length = Len(elem.Value)
If (length < fixedWidth) Then
elem.Value = Space(fixedWidth - length) + elem.Value
Else
'???
End If
Next

End Sub