Add Leading Zeros in Certain Cases

bradjedis
4StarLounger
Posts: 471
Joined: 30 Mar 2010, 18:49
Location: United States

Add Leading Zeros in Certain Cases

Post by bradjedis »

Greetings,

I have a situation where I need to add Leading Zeros to data based upon the number of existing characters.

Col I data is alphanumeric, and col format is set as TEXT. In cases where the char count is less than 5, I need to add leading zeros to bring the the char count up to 5.

so,
1 becomes 00001
11 becomes 00011
111 becomes 00111
1111 becomes 01111

number of lines will vary...

Thanks,
Brad

User avatar
StuartR
Administrator
Posts: 11925
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: Add Leading Zeros in Certain Cases

Post by StuartR »

Do you want to do this with VBA? Or with a formula in an adjacent column? OR something completely different?

If your numbers are in column A, then you could put =RIGHT("00000"&A1,5) in B1 and fill down
or if you want to do this with VBA then try

Code: Select all

Sub PadWithFiveZeros()
    Dim cl As Range
    For Each cl In Range("A1", Range("A65535").End(xlUp))
        cl.Value = Right("00000" & cl.Value, 5)
    Next cl
End Sub
StuartR


Toranaga
3StarLounger
Posts: 244
Joined: 15 Aug 2016, 11:23

Re: Add Leading Zeros in Certain Cases

Post by Toranaga »

Try,

Put this in B1 =TEXT(A1,"00000") if your data start in A1, then drag down.

User avatar
DocAElstein
2StarLounger
Posts: 124
Joined: 18 Jan 2022, 15:59
Location: Hof, Bavaria, Germany

Re: Add Leading Zeros in Certain Cases

Post by DocAElstein »

Using Toranaga’s formula in VBA

Code: Select all

 Sub PrettyWay() ' http://www.eileenslounge.com/viewtopic.php?f=27&t=38510
 Let Range("A1:A" & Range("A65535").End(xlUp).Row & "").Value = Evaluate("=If({1},TEXT(" & Range("A1:A" & Range("A65535").End(xlUp).Row & "").Address & ",""00000""))")
End Sub

If you want the same result in column B, and column B is formatted as text, then

Code: Select all

Sub PrettyBaby() ' https://www.youtube.com/watch?v=ChLQdF6PUxA
 Let Range("B1:B" & Range("A65535").End(xlUp).Row & "").Value = Evaluate("=If({1},TEXT(" & Range("A1:A" & Range("A65535").End(xlUp).Row & "").Address & ",""00000""))")
End Sub

If you want the same result in column C, and column C is not formatted as text, then this will do it, but it will add one of those ' things at the start of the number

Code: Select all

 Sub GodSaveTheQueen() '
 Let Range("C1:C" & Range("A65535").End(xlUp).Row & "").Value = Evaluate("=""'"" & If({1},TEXT(" & Range("A1:A" & Range("A65535").End(xlUp).Row & "").Address & ",""00000""))")
End Sub
_._____________________________________________________________________________________________________________________
StuartR wrote:
04 Aug 2022, 17:23
...Right("00000" & cl.Value, 5)....
That is a nice simple way I never thought about. It seems better, more fundamental, than this which I have always used for that sort of thing,
Format(cl.Value, "00000")

_._______

Alan
Last edited by DocAElstein on 06 Aug 2022, 09:16, edited 1 time in total.
_

adeel1
3StarLounger
Posts: 203
Joined: 04 Oct 2017, 15:47

Re: Add Leading Zeros in Certain Cases

Post by adeel1 »

Hi All and Alan

what is advantage of use =If({1} in formula, what is this referring and returning. please

although below is working fine

Code: Select all

P = Cells(Rows.Count, 1).End(xlUp).Row
Cells(2, 3).Resize(P - 1) = Evaluate("=TEXT(A2:A" & P & ",""0000"")")

Adeel

User avatar
DocAElstein
2StarLounger
Posts: 124
Joined: 18 Jan 2022, 15:59
Location: Hof, Bavaria, Germany

Why use If({1}, _______ )

Post by DocAElstein »

adeel1 wrote:
05 Aug 2022, 09:59
Hi All and Alan
what is advantage of use =If({1} in formula, what is this referring and returning. please
Hi adeel1,
I did forget to mention that…

For newer Excel versions from Office 2016 + you probably** do not need the extra
If({1}, _______ )

But for older Excel versions, these sort of Range Evaluate things do not always work properly. Sometimes, only the first value is returned. There are a few tricks that make the Range Evaluate things also work in early Excel versions. The trick,
If({1}, _______ )
, is one way


_._____________________

For Example: In my Excel 2007 I get these results:

Code: Select all

Sub HolyMoses() ' https://eileenslounge.com/viewtopic.php?p=297642#p297642
Dim P As Long
 P = Cells(Rows.Count, 1).End(xlUp).Row
 Cells(2, 3).Resize(P - 1) = Evaluate("=TEXT(A2:A" & P & ",""0000"")")
End Sub
Result:
HolyMoses.JPG

Only first value is returned :( :sad:

_.____________________



Now I add trick

Code: Select all

Sub Bingo()
Dim P As Long
 P = Cells(Rows.Count, 1).End(xlUp).Row
 Cells(2, 3).Resize(P - 1) = Evaluate("=If({1},TEXT(A2:A" & P & ",""0000""))")
End Sub
Now it works for me in my Excel 2007:
Bingo.JPG
:) :smile:


It never does any harm to include If({1}, _______ ) on newer Excel versions. So I always include it for backward compatibility.

Alan

_.___________________

** Note I say probably, because: Microsoft starting adding changes from about 2016 in the way array things work. Finally it resulted in the new “Spill” things. But the new “Spill” things only work in the most newest versions. ( I would not be surprised if they were messing about unintentionally, had no idea what was going on, made a total mess, which resulted in a bug that caused something similar to the “Spill” things, so they decided to tidy it up a bit, pretend it was intentional, and call it a new feature! )
You do not have the required permissions to view the files attached to this post.
Last edited by DocAElstein on 06 Aug 2022, 07:00, edited 4 times in total.
_

adeel1
3StarLounger
Posts: 203
Joined: 04 Oct 2017, 15:47

Re: Add Leading Zeros in Certain Cases

Post by adeel1 »

Hi Alan

Much thnx for sharing your knowledge :clapping: :thankyou:

Adeel

User avatar
DocAElstein
2StarLounger
Posts: 124
Joined: 18 Jan 2022, 15:59
Location: Hof, Bavaria, Germany

Re: Add Leading Zeros in Certain Cases

Post by DocAElstein »

Yous welcome
( BTW , I mixed up my pictures on the first post, and just edited them, so make sure you saw my updated post )
_

adeel1
3StarLounger
Posts: 203
Joined: 04 Oct 2017, 15:47

Re: Add Leading Zeros in Certain Cases

Post by adeel1 »

Hi Alan

yes, i was writing the same but you have done it timely thnx

Adeel

User avatar
StuartR
Administrator
Posts: 11925
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: Add Leading Zeros in Certain Cases

Post by StuartR »

The difference between my approach and using the TEXT function is that TEXT relies on Excel coercing the original values into numbers, even though they are all text. My approach avoids coercion, which is always a good thing to avoid when you can.
StuartR