Fixed length cells

[Guest]

I would like to fix the length of column B to 10 characters. if actual vale
is less than 10 characters, I would like to pad rightmost characters with
spaces. Any help would be appreciated.

Prema

 

[Guest]

Try this small event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("B:B"), Target) Is Nothing Then
t = Target.Value
n = Len(t)
If n > 10 Then
t = Right(t, 10)
Application.EnableEvents = False
Target.Value = t
Application.EnableEvents = True
Exit Sub
End If
If n < 10 Then
t = t & Application.Rept(" ", 10 - n)
Application.EnableEvents = False
Target.Value = t
Application.EnableEvents = True
Exit Sub
End If
End If
End Sub



If the input is less than 10 characters, the padding is applied.
iIf the input is greater than 10 characters, the result is truncated.

 

[Guest]

Thank you.
Prema