Concatenating text to numbers

K

Ken

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
 
K

K Dales

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'
 
C

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top