Character Length in a column

[noreagaxl]

I have a column where every cell must have 14 characters.
Lets say have the name Angel. I need 9 more charcters. Next cell down
have the word cake. I need 10 more charcters.How do I automatciall
insert the spaces I need to have 14 charcters. HELP PLEASE!!!!! GOIN
NUTS

 

[J.E. McGimpsey]

One way:

Do you care about what characters are added? I'll assume spaces
(CHAR(32)). Preceding the existing text or trailing? I'll assume
trailing.

Put this event macro in your worksheet code module (right-click on
the worksheet tab and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(.Cells, Range("A:A")) Is Nothing Then
Application.EnableEvents = False
.NumberFormat = "@"
.Value = Left(.Text & Space(14), 14)
Application.EnableEvents = True
End If
End With
End Sub

 

[noreagaxl]

J.E. thanks for your help

Im an excel idiot but thanks for you help. the formula worked but
need it to 14 charcaters in column I and 9 characters in column J. You
help is appreciated

 

[J.E. McGimpsey]

One way:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim nWidth As Integer
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(.Cells, Range("I:J")) Is Nothing Then
nWidth = IIf(.Column = 9, 14, 9)
Application.EnableEvents = False
.NumberFormat = "@"
.Value = Left(.Text & Space(nWidth), nWidth)
Application.EnableEvents = True
End If
End With
End Sub

 

[noreagaxl]

PERFECT!!! YOU NAILED IT MY FRIEND! MANY THANK YOU BUDDY!!!
MAY GOD BLESS YOU AND YOUR FAMILY

 

[noreagaxl]

I got one more for you JE!

Lets say I have an exisiting worksheet and I want to apply the code yo
gave me to the worksheet. I copied the code to an exsisting one and
didnt do the desired function. It worked on a blank one. If you kno
what to do your knowledge would be greatly appreciated

 

[J.E. McGimpsey]

The code I provided was for automatically increasing the number of
characters. If you want a macro that will work on existing sheets,
this will do:

Public Sub AddSpaces()
Dim vResult As Variant
Dim rCell As Range
Do
vResult = Application.InputBox( _
Prompt:="Number of Spaces: ", _
Title:="Add Spaces", _
Default:=9, _
Type:=1)
If vResult = False Then Exit Sub 'clicked Cancel
Loop Until vResult > 0 And vResult <= 32000
Application.EnableEvents = False
On Error Resume Next
For Each rCell In Selection.SpecialCells(xlCellTypeConstants)
With rCell
.NumberFormat = "@"
.Value = Left(.Text & Space(vResult), vResult)
End With
Next rCell
End Sub

OTOH, you could do it without a macro using a "helper column". Say
you wanted to convert Column I of an existing sheet. In an empty
column, say column Z, put:

Z1: =LEFT(I1 & REPT(" ",9),9)

and copy down as far as necessary. Select column Z. Copy it. Select
column I. Choose Edit/Paste Special, selecting the Values radio
button.

 

[J.E. McGimpsey]

Oops - forgot to reset EnableEvents:

Put

Application.EnableEvents = True

just after

Next rCell

 

[Dana DeLouis]

If it's spaces, this might be another technique. Don't know if it would be
any faster though.

Sub Demo()
Dim s9 As String * 9
Dim s14 As String * 14

LSet s14 = [I1]
[I1] = s14

LSet s9 = [J1]
[J1] = s9
End Sub