Character Length in a column

  • Thread starter Thread starter noreagaxl
  • Start date Start date
N

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
 
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
 
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
 
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
 
PERFECT!!! YOU NAILED IT MY FRIEND! MANY THANK YOU BUDDY!!!
MAY GOD BLESS YOU AND YOUR FAMILY
 
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
 
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.
 
Oops - forgot to reset EnableEvents:

Put

Application.EnableEvents = True

just after

Next rCell
 
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
 
Back
Top