cell lenght MUST be always 30

  • Thread starter Thread starter JABAgdl
  • Start date Start date
J

JABAgdl

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
 
The original string lenght may vary from 1 to 30.

Using a formula...

A1 = some string

B1 = formula:

=A1&REPT(" ",30-LEN(A1))
 
--A formula applied in a cell can reference another cell text and format that
to 30 chars

=A1&REPT(" ",30-LEN(A1))

--VBA solution. Select the sheet tab which you want to work with. Right
click the sheet tab and click on 'View Code'. This will launch VBE. Paste the
below code to the right blank portion. Get back to to workbook and try out.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If Target.Text <> "" Then
Application.EnableEvents = False
Target.Value = Target.Value & Space(30 - Len(Target.Value))
Application.EnableEvents = True
End If
End If
End Sub
 
The following VBA Event-Macro will fulfill your request directly in the given
cell [B3 in this example] without the need of an adjacent cell:
-------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Address = "$B$3" And Len(Target) < 30 Then Target = Target +
Space(30 - Len(Target))
End Sub
 
=LEFT(A1&REPT(" ",30),30)
covers also the case of A1 having more than 30 characters...

Regards,
Bernd
 
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
Click to expand...

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
 
You don't say what you want if there are more than 30 characters in a cell.

Do you have to deal with that?

Or did you mean "at least" 30 characters?


Gord Dibben MS Excel MVP
 
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
Click to expand...

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
 
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.
Click to expand...

Thanks.

I probably would have used the String function, but I didn't think of it :-|

--ron
 
Back
Top