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
Add Leading Zeros in Certain Cases
-
- 4StarLounger
- Posts: 540
- Joined: 30 Mar 2010, 18:49
- Location: United States
-
- Administrator
- Posts: 12623
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Add Leading Zeros in Certain Cases
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
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
-
- 3StarLounger
- Posts: 256
- Joined: 15 Aug 2016, 11:23
Re: Add Leading Zeros in Certain Cases
Try,
Put this in B1 =TEXT(A1,"00000") if your data start in A1, then drag down.
Put this in B1 =TEXT(A1,"00000") if your data start in A1, then drag down.
-
- 5StarLounger
- Posts: 604
- Joined: 18 Jan 2022, 15:59
- Location: Re-routing rivers, in Hof, Beautiful Bavaria
Re: Add Leading Zeros in Certain Cases
Using Toranaga’s formula in VBA
If you want the same result in column B, and column B is formatted as text, then
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
_._____________________________________________________________________________________________________________________
Format(cl.Value, "00000")
_._______
Alan
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
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.
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(
-
- 3StarLounger
- Posts: 264
- Joined: 04 Oct 2017, 15:47
Re: Add Leading Zeros in Certain Cases
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
Adeel
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
-
- 5StarLounger
- Posts: 604
- Joined: 18 Jan 2022, 15:59
- Location: Re-routing rivers, in Hof, Beautiful Bavaria
Why use If({1}, _______ )
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
Only first value is returned :(
_.____________________
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
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.
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(
-
- 3StarLounger
- Posts: 264
- Joined: 04 Oct 2017, 15:47
Re: Add Leading Zeros in Certain Cases
Hi Alan
Much thnx for sharing your knowledge
Adeel
Much thnx for sharing your knowledge
Adeel
-
- 5StarLounger
- Posts: 604
- Joined: 18 Jan 2022, 15:59
- Location: Re-routing rivers, in Hof, Beautiful Bavaria
Re: Add Leading Zeros in Certain Cases
Yous welcome
( BTW , I mixed up my pictures on the first post, and just edited them, so make sure you saw my updated post )
( BTW , I mixed up my pictures on the first post, and just edited them, so make sure you saw my updated post )
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(
-
- 3StarLounger
- Posts: 264
- Joined: 04 Oct 2017, 15:47
Re: Add Leading Zeros in Certain Cases
Hi Alan
yes, i was writing the same but you have done it timely thnx
Adeel
yes, i was writing the same but you have done it timely thnx
Adeel
-
- Administrator
- Posts: 12623
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Add Leading Zeros in Certain Cases
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