How can I add 0's to a string based on string length difference compared to target length?-CodePudding

I need to make sure all cells in each column have that column's target length. When the target length is larger than the actual length of the string value in the given cell, x amount of 0s must be added in front in order to make the string's length be equal to the target.

So far I have this code:

Dim row_counter1 As Long
Dim char1 As Integer
Dim char_dif1 As Integer
Dim char_targ1 As Integer
    char_targ1 = 2
For row_counter1 = 2 To last_row_index(output, 1)
    char1 = Len(output.Cells(row_counter1, 1))
    char_dif1 = char_targ1 - char1
    Debug.Print char_dif1
Next row_counter1

last_row_index is a simple UDF using the End.xlUp method to find last continuous row index. The loop works well, giving me the difference in Integer format.

Taking that integer and turning it into 0s that go in front of the cell.value is the issue I am having trouble solving. I have seen solutions to similar problems for Python, but I am very new to coding and I am having trouble understanding the process.

CodePudding user response：

in the cell like this, e.g. for 10 digits of length:

=RIGHT(“0000000000000”&A1,10)

CodePudding user response：

Please, try the next function:

Function addZero(rng As Range, strLength As Long) As Variant
    rng.NumberFormat = "@"
    addZero = Evaluate("RIGHT(""" & String(strLength, "0") & """&" & rng.Address & "," & strLength & ")")
End Function

It can be called from your (adapted) code in the next way:

Sub PlaceZero()
 Dim sh As Worksheet, rng As Range, lastR As Long

 Dim char_targ1 As Long: char_targ1 = 5
 Set sh = ActiveSheet
 lastR = sh.Range("A" & sh.rows.count).End(xlUp).row
 Set rng = sh.Range("A2:A" & lastR)
 
 rng.value = addZero(rng, char_targ1)
End Sub

No iteration needed using Evaluate...

But if you will try setting char_targ1 to a length lower than the existing string, it will be truncated to the char_targ1 length, of course.

To previously check if the non zero characters in each cell are less than 'char_targ1, the next way can be used (directly in the calling Sub`):

 Dim arr, MTCH
 arr = Evaluate("TRANSPOSE(IF(LEN(SUBSTITUTE(" & rng.Address(0, 0) & ",""0"",""""))<" & char_targ1 & ",""x"",""""))")
 MTCH = Application.match("x", arr, 0)
 If IsNumeric(MTCH) Then MsgBox "There are strings with less characters (except zero) than :" & char_targ1 & "...": Exit Sub

It should be placed before the code line:

 rng.value = addZero(rng, char_targ1)

But it counts also the existing zero characters inside the original string. I mean, not only the leading zero characters...

CodePudding user response：

REPT(text, number_times)

Example

application.WorksheetFunction.Rept(0,10)