1

I'm writing a function which needs to look up a code of a certain length in a different range, but not all codes are the correct length. In this range, that is fixed by having extra zeroes in front of it.

So I am trying to write part of a macro that adds the 0's in front of the code until it is exactly 13 characters long (assuming it is not already 13 characters long).

For now, I have this, but I feel like it can be done better. Especially because I don't know if 10 is the shortest possible length.

Function BolS(Barc As Range) As String

Dim BarA As Range

If Len(Barc.value) = 10 Then
    BarA = "000" & Barc.value
End If

If Len(Barc.value) = 11 Then
    BarA = "00" & Barc.value
End If

If Len(Barc.value) = 12 Then
    BarA = "0" & Barc.value
End If

If Len(Barc.value) = 13 Then
    BarA = Barc.value
End If

Maybe something with 13 minus the Len(Barc.value), and then adding that many 0's?

Aevir Denken
  • 57
  • 6

3 Answers3

1

If it's a number then this would work:

Public Function PadMyNumber(Target As Range) As String
    PadMyNumber = Format(Target, "0000000000000")
End Function

The output is a string rather than a number.

Darren Bartrup-Cook
  • 18,362
  • 1
  • 23
  • 45
0

If you already know that you will have strings with 13 characters you can determine their actual length by If Len(Barc.value) = 13 Then BarA = Barc.value End If

If Len(Barc.value) = 13 Then
    BarA = Barc.value
    'Exit Sub
Else
   'a = 13 - Len(Barc.value)
   'BarA = WorksheetFunction.Rept("0", a) & Barc.value
   BarA = WorksheetFunction.Rept("0", 13 - Len(Barc.value)) & Barc.value
End If

This part repeats the "0" character as often as needed to get to a length of 13:

WorksheetFunction.Rept("0", 13 - Len(Barc.value))

If your cell format is not in text format, you must keep an " ' " prior to the number to tell Excel that it's a string and not a number (as all zeros in front of numbers are not shown by default). Your new add up would be the quote sign:

BarA = "'" & WorksheetFunction.Rept("0", 13 - Len(Barc.value)) & Barc.value
Capt.Krusty
  • 597
  • 1
  • 7
  • 26
  • Looks like it will work. Is it very different from: `If Len(Barc.Value) < 13 Then BarA = Right(String(13, "0") & CStr(Barc.Value), Max(13, Len(CStr(Barc.Value)))) End If` – Aevir Denken Aug 06 '21 at 09:44
  • Glad to hear that! If you have further questions, just ask. Else don't forget accepting an answer. – Capt.Krusty Aug 06 '21 at 09:46
  • It didn't work out, but luckily I found out that `BarA = WorksheetFunction.Text(Barc.Value, "0000000000000")` also works. – Aevir Denken Aug 06 '21 at 10:15
0

Here are two examples of adding in the padding, but to pad with 0's you need to either explicitly state it's text by preceding with ' or by setting the cell to a text format. lengths over 13 would likely error and hasn't been accounted for here.

Sub Pad0()
    ActiveCell.Offset(0, 1) = "'" & WorksheetFunction.Rept("0", 13 - Len(ActiveCell)) & ActiveCell
    
    With ActiveCell
        .Offset(0, 2).NumberFormat = "@"
        .Offset(0, 2) = WorksheetFunction.Rept("0", 13 - Len(ActiveCell)) & .Value
    End With
End Sub
Tragamor
  • 3,594
  • 3
  • 15
  • 32