Concatenating text to numbers

[Guest]

As part of a larger macro we have a column (Column A) formatted as numeric
that we now want to treat as text. We want to add two leading zeros to those
cells that are one digit numbers and add one leading zero to those cells that
are two digit numbers.

How do we need to change this code to do what we want?

TIA.

'Convert CP field to text format.
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), FieldInfo:=Array(1, 2)

'Insert leading zeros into CP number
For ILoop = 1 To NumRowsFMS
If Len(Cells(ILoop, 1)) = 1 Then
Cells(ILoop, 1) = "00" & Cells(ILoop, 1)
End If
If Len(Cells(ILoop, 1)) = 2 Then
Cells(ILoop, 1) = "0" & Cells(ILoop, 1)
End If
Next ILoop

 

[Guest]

There is a pretty easy way to do this in a few steps:

Public Sub NumToText()

Dim CRange As Range

Range("A:A").ClearFormats

For Each CRange In Range("A1").CurrentRegion.Cells

CRange.Value = "'" & Format(CRange.Value, "000")

Next CRange

End Sub

K Dales - another 'Ken'

 

[crispbd]

Dim a As String, i As Long

For i = 1 To UsedRange.Rows.Count

Cells(i, 1).NumberFormat = "@"
a = Trim(Str(Cells(i, 1).Value))
If Len(a) = 2 Then a = "0" & a
If Len(a) = 1 Then a = "00" & a
Cells(i, 1).Value = a

Next