Add Leading Zero's To Alphanumeric Field

[tdio]

Hello All,
I know how to pad out a cell with leading zero's if the data in the cell is numeric, but when it's alphanumeric, i'm hitting a brick wall.

Some examples of item numbers we have in column A are;
B-0035
B-12X16PLN
B-210

I need all entries in column A to be 14 characters long, with zeros being the padding ie;
00000000B-0035
0000B-12X16PLN
000000000B-210


Any ideas?

Thanks Tdio

 

[dcamos]

Assuming your first bit of data is in A7 this will make the cell 14 characters long and fill the beginning with leading 0s.

=REPT("0",14-LEN(A7))&A7

 

[CWatts]

You can also use VBA if you actually want to change the data in A. Highlight the cells that you need converted to 14 digits.

Just highlight what you want to convert to 14 digits and then run the macro. Be careful not to select the whole column as the macro will take forever to run.

Sub Digits()
For Each c In Selection
    If Len(c.Value) < 14 And Len(c.Value) <> "" Then
        c.Value = WorksheetFunction.Rept("0", 14 - Len(c.Value)) & c.Value
    End If
Next
End Sub

 

[tdio]

You can also use VBA if you actually want to change the data in A. Highlight the cells that you need converted to 14 digits.

Just highlight what you want to convert to 14 digits and then run the macro. Be careful not to select the whole column as the macro will take forever to run.

Sub Digits()
For Each c In Selection
    If Len(c.Value) < 14 And Len(c.Value) <> "" Then
        c.Value = WorksheetFunction.Rept("0", 14 - Len(c.Value)) & c.Value
    End If
Next
End Sub

Worked like a charm. Thanks for all your help.