Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dollar sign left justified - data right justified - large space | Excel Discussion (Misc queries) | |||
Custom Fields with spaces | Excel Discussion (Misc queries) | |||
by default text is left justified, numbers are right justified - a specific reason for this? | Excel Discussion (Misc queries) | |||
rows top-justified & bottom-justified in same sheet looks bad | Excel Worksheet Functions | |||
Add spaces for specific fields to paste in | Excel Discussion (Misc queries) |