Add Space Padding

[Scott]

I'm trying to "pad" or add a blank space in a column of cell values. My code
below is running a "Do While" loop based on values in column A which
contains date values. The function then is testing the length of the value
in column D and entering just the numeric value of the column D cell into
the column I cell.

The code does add just the numeric value, but I need single digit values
like "8" to be entered as " 8". I need to "pad" the single digit values for
correct sorting when I graph the data.

I've tried using the SPACE() function below, but Excel will not add the
extra blank space preceeding single digit values like the "8" value below.

Any ideas?


SAMPLE DATA:
*******************************

A D I
----------------------------------------------
1 3/1/2009 20' 20
2 3/2/2009 8' 8



CODE:
************************

Sub InsertFormulaPadLength()
Dim c As Range
Set c = Worksheets("database").Range("A2")

Do While c <> ""
'set formula 1 column to right of c

If Len(c.Offset(0, 3).Value) = 3 Then

c.Offset(0, 8).Value = Left(c.Offset(0, 3).Value,
(Len(c.Offset(0, 3).Value) - 1))

ElseIf Len(c.Offset(0, 3).Value) = 2 Then

c.Offset(0, 8).Value = Space(1) & Left(c.Offset(0, 3).Value, 1)

End If

'set c to the next cell down
Set c = c.Offset(1, 0)
Loop

End Sub

 

[Rick Rothstein]

I'm confused a little by your code. Can you have 3-digit values? It looks
like you are chopping off the left character of 3-digit values... am I
reading that right? If so, can you clarify the rules if you have 1, 2 and 3
digit values in the column?

 

[Scott]

i finally got it. My code was working, but I just needed to format the
column as text.