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))

--
Biff
Microsoft Excel MVP


"JABAgdl" 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

=LEFT(A1&REPT(" ",30),30)
covers also the case of A1 having more than 30 characters...

Regards,
Bernd

--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


--
Jacob


"JABAgdl" wrote:

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

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

On Thu, 18 Feb 2010 20:30:01 -0800, JABAgdl
wrote:

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