Padding a Number String with Zeros to remain as a Multiple of 3's in length (in VBA)

**MohsenAlyafei** · 07-03-2019, 02:49 PM

I would like to have a quick single line VBA code to pad a number in string form to always have a length of a multiples of 3's.
For example:

"12" becomes "012"
"1234" becomes "001234"
"1234567" becomes "001234567"
"12345678912345678" becomes "012345678912345678"
and so on.

The number string will not have any fraction or decimal points, so not to worry about that.

The length of the resulting string should be a multiple of 3 by adding 0's to the Left.

The number string should remain a string throughout the coding and should not be converted into a number using a numeric function such as Val, as the original string could be very long over 150 digits and using a numeric function could truncate or round the number and give undesirable results.

I had come up with the following as a possible solution by checking the string length but feel there is a better more efficient way of doing it in one (1) coding statement.


Result = String((3 - (Len(MyString) Mod 3)) Mod 3, "0") & MyString

Thanks in advance.

**Fluff13** · 07-03-2019, 03:14 PM

Not neccesarily any better, but another way

String(Choose((Len(mystring) Mod 3) + 1, 0, 2, 1), "0") & mystring

**MohsenAlyafei** · 07-03-2019, 03:27 PM

Originally Posted by Fluff13

Not neccesarily any better, but another way

String(Choose((Len(mystring) Mod 3) + 1, 0, 2, 1), "0") & mystring

Thanks Fluff13.

I tested it, but it is about 2.8 times slower than using a double "Mod" function.

I was thinking my code using a double Mod function is slow.

**TMS** · 07-03-2019, 03:35 PM

Formula approach:

Formula:

=TEXT(A1,REPT(0,LEN(A1)+3-MOD(LEN(A1),3)))

Edit: doesn't look like this works for the longer "numbers".

**Marc L** · 07-03-2019, 07:07 PM

I had the same idea as Fluff13 (aka 0, 2, 1) but via an array in order to use a single mod function :

PHP Code:


    Dim V, MyString$, Result$

        V = Array(0, 2, 1)

        MyString$ = "12"

        Result = String(V(Len(MyString) Mod 3), "0") & MyString

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

**MohsenAlyafei** · 07-04-2019, 12:09 AM

Thank you all for the ideas.

I have come up with one more possibility with one less bracket, but still uses a double Mod function

MyString = String(2 * (Len(MyString) Mod 3) Mod 3, "0") + MyString

**MohsenAlyafei** · 07-04-2019, 12:42 AM

After several attempts and your ideas, I have come up with the following using a single Mod Function:

MyString = String((Len(MyString) * 2) Mod 3, "0") & MyString

In general, a general formula would be:

MyString = String((Len(MyString) * (Multiples-1) ) Mod Multiples, "0") & MyString

**TMS** · 07-04-2019, 08:54 AM

Bit late in the day but, as I've been playing with this, you might as well have it ...

Option Explicit

' Formula approach: =REPT(0,3-MOD(LEN(A2),3)) & A2

Sub Test1()
With Range("A2")
    .NumberFormat = "@"
    .Value = Evaluate("=REPT(0,3-MOD(LEN(A2),3))&A2")
End With
End Sub

Sub Test2()
' Loop using Evaluate
Dim rng As Range
Dim lLR As Long, i As Long
lLR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lLR
    Set rng = Range("A" & i)
    With rng
        .NumberFormat = "@"
        .Value = Evaluate("=REPT(0,3-MOD(LEN(" & rng.Address & "),3))&" & rng.Address & "")
    End With
Next i
End Sub

Sub Test3()
With Range("A2")
    .NumberFormat = "@"
    .Value = Application.WorksheetFunction.Rept(0, 3 - (Len(Range("A2")) Mod 3)) & Range("A2")
End With
End Sub

Sub Test4()
' Loop using VBA Worksheet Functions
Dim lLR As Long, i As Long
lLR = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lLR
    With Range("A" & i)
        .NumberFormat = "@"
        .Value = Application.WorksheetFunction.Rept(0, 3 - (Len(Range("A" & i)) Mod 3)) & Range("A" & i)
    End With
Next 'i
End Sub

Probably just overthinking it originally and trying to use TEXT/Format

**MohsenAlyafei** · 07-06-2019, 04:54 AM

Thanks TMS

**TMS** · 07-06-2019, 05:44 AM

You're welcome. Thanks for the rep.

Padding a Number String with Zeros to remain as a Multiple of 3's in length (in VBA)

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Padding a Number String with Zeros to remain as a Multiple of 3's in length (in VBA)

Re: Padding a Number String with Zeros to remain as a Multiple of 3's in length (in VBA)

Re: Padding a Number String with Zeros to remain as a Multiple of 3's in length (in VBA)

Re: Padding a Number String with Zeros to remain as a Multiple of 3's in length (in VBA)

Hi ! Try this …

Re: Hi ! Try this …

Re: Padding a Number String with Zeros to remain as a Multiple of 3's in length (in VBA)

Re: Padding a Number String with Zeros to remain as a Multiple of 3's in length (in VBA)

Re: Padding a Number String with Zeros to remain as a Multiple of 3's in length (in VBA)

Re: Padding a Number String with Zeros to remain as a Multiple of 3's in length (in VBA)

Thread Information

Users Browsing this Thread

Similar Threads

Format value with Padding of Zeros

padding out zeros in textbox

Preserve leading zeros in a varying length number when converting it to text

[solved] convert variable length decimal to fixed string w/left and right padding

[SOLVED] Counting number of zeros between two non zero values in a string

Extracting a number from a string that varies in length...

Count number of cells with a string length greater than X

Bookmarks

Bookmarks

Posting Permissions