Ron,
Very nice, I am going to keep it.
I've never used the "!" in the Format function to process from the left side.
FWIW, I would guess the vba String$ function would be faster than the worksheet Rept function.
Thanks.
--
Jim Cone
Portland, Oregon USA
"Ron Rosenfeld" <
[email protected]>
wrote in message
eHello:
I need to always have 30 char in a given cell, if the original string is
less than 30 chars, lets say 23 then I need to fill in at the right of the
string with 7 blank spaces. The original string lenght may vary from 1 to 30.
Is there a formula or VBA code to do this?
Any and all help would be highly appreciated!
PLK
Here's a macro you can run after selecting your range of cells to be padded to
30 characters.
To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.
To use this Macro (Sub), select the range of cells to be padded.
Then <alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.
=======================================
Option Explicit
Sub Pad30()
Dim rg As Range, c As Range
Set rg = Selection
For Each c In rg
c.NumberFormat = "@"
c.Value = Format(c.Text, "!" & _
WorksheetFunction.Rept("@", 30))
Next c
End Sub
=========================
--ron