Converting a column of data with varying digits

[McDan]

I have a column of numbers that constitute a code. The code is used instead of using a company's name. However, to run this code into a program I use for work, I need to make sure the code is in a certain format - it must have AA in front of it.

There is only one problem. The code must be 8 characters at the end. If the original code is 123456 we simply stick AA in the beginning - (AA123456). However, if the code is shorter we must input the correct amount of zeros in to compensate - so if it were only 1234 we would need to type AA001234.

What formula can I use to automatically convert this huge column of code into the correct AA format every time?

Thank You!

 

[cstimart]

Like this...?

Sub test()
Dim LR As Long
Dim cell As Range

LR = Range("A" & Rows.Count).End(xlUp).Row
For Each cell In Range("A1:A" & LR)
    cell.NumberFormat = "AA000000"
Next cell
End Sub

 

[cstimart]

This might be better...

Sub test()
Dim i As Long
Dim j As Integer
Dim temp As String

For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
    If Len(Range("A" & i)) <= 6 Then
        temp = "AA"
        For j = 1 To 6 - Len(Range("A" & i))
            temp = temp & "0"
        Next j
        Range("A" & i) = temp & Range("A" & i)
    End If
Next i
End Sub

 

[McDan]

Those are both really good suggestions but I was just shown another I thought I'd share - = ("AA" & Rept(0,6-len(A1))&A1)