Excel custom format

Factual Questions
1

I have a column of numbers that look like this:

29D01-1011-FC-1
29D02-1102-MC-12795
29D02-1205-MC-2159
29D02-1204-MC-4893

The format of the number is always the same as far as letters and numbers.

I need to change them to always be exactly 20 characters long, but I need to add zeros after the last hyphen and before the last number(s). So the above column would look like this:

29D01-1011-FC-000001
29D02-1102-MC-012795
29D02-1205-MC-002159
29D02-1204-MC-004893

I am already running some VBA code so that would probably be the best solution.

Thanks.

2

If the first three parts are always the same length, you could use the Mid() and Len() functions to select the last section and add the appropriate number of 0s when they’re too short. (Or you can use regex if you prefer)

In pseudocode:

for every cell in column a
lastsection=mid(celltext, 15, len(celltext))
for i = 1 to (6-len(lastsection))
insert 0 before lastsection

3

This VBA should do the trick. You’ll need to replace the parts in red with, respectively, the starting and ending row numbers, and the letter of the column that holds the data.

Note that this procedure assumes that the last segment is the only one whose length varies — if you need it to work on strings like, say, 202-125-MC-2159, it’ll add another step. Let me know if this is the case.

Sub TwentyChar()

Dim CellText As Variant, x As Integer, y As Integer
Dim NewText As String

For x = **1** To **50**
    NewText = ""
    CellText = Split(Cells(x, "**A**").Text, "-", -1, vbTextCompare)
    Do While Len(CellText(UBound(CellText))) < 6
        CellText(UBound(CellText)) = "0" + CellText(UBound(CellText))
    Loop
    For y = 0 To UBound(CellText)
        If y = UBound(CellText) Then
            NewText = NewText + CellText(y)
        Else
            NewText = NewText + CellText(y) + "-"
        End If
    Next y
    Cells(x, "**A**").FormulaR1C1 = NewText
Next x

End Sub

4

I know you said you prefer a VBA solution, but for reference here is an in-cell formula that will do it:

=LEFT(A1,14)&REPT(“0”,20-LEN(A1))&RIGHT(A1,LEN(A1)-14)

5

Thanks to everyone who responded. Problem solved. I hope you can find your way over to my other Excel OP. I’m having a bad Excel day.