Place zeros "0" at left to a number

Panoos64 · Sep 14, 2018

Hi all, kindly require to provide me support for a VBA code, so that to adjust the numbers in col. “B” on left side like the below format.

0000-00202121

Therefore that, where the number contains 7 digits, should complete just 0 and then the – and 0000. e.g. 0000-01202121

Please see below the original data and the expected result. I would be greatly appreciating any support

Thanking you in advance

Original data
[TABLE="class: MsoNormalTable, width: 256"]
<tbody>[TR]
[TD="width: 38"][/TD]
[TD="width: 112"]

A

[/TD]
[TD="width: 153"]

B

[/TD]
[TD="width: 123"]

C

[/TD]
[/TR]
[TR]
[TD="width: 38"]

10

[/TD]
[TD="width: 112"]

DATE

[/TD]
[TD="width: 153"]

Reference

[/TD]
[TD="width: 123"]

Amount

[/TD]
[/TR]
[TR]
[TD="width: 38"]

11

[/TD]
[TD="width: 112"]

09-05-18

[/TD]
[TD="width: 153"]

202121

[/TD]
[TD="width: 123"]

1522.17

[/TD]
[/TR]
[TR]
[TD="width: 38"]

12

[/TD]
[TD="width: 112"]

09-05-18

[/TD]
[TD="width: 153"]

202235

[/TD]
[TD="width: 123"]

1325.36

[/TD]
[/TR]
[TR]
[TD="width: 38"]

13

[/TD]
[TD="width: 112"]

09-05-18

[/TD]
[TD="width: 153"]

203145

[/TD]
[TD="width: 123"]

955.63

[/TD]
[/TR]
[TR]
[TD="width: 38"]

14

[/TD]
[TD="width: 112"]

09-05-18

[/TD]
[TD="width: 153"]

203879

[/TD]
[TD="width: 123"]

2125.45

[/TD]
[/TR]
</tbody>[/TABLE]

Expected result
[TABLE="class: MsoNormalTable, width: 256"]
<tbody>[TR]
[TD="width: 38"][/TD]
[TD="width: 112"]

A

[/TD]
[TD="width: 153"]

B

[/TD]
[TD="width: 123"]

C

[/TD]
[/TR]
[TR]
[TD="width: 38"]

10

[/TD]
[TD="width: 112"]

DATE

[/TD]
[TD="width: 153"]

Reference

[/TD]
[TD="width: 123"]

Amount

[/TD]
[/TR]
[TR]
[TD="width: 38"]

11

[/TD]
[TD="width: 112"]

09-05-18

[/TD]
[TD="width: 153"]

0000-00202121

[/TD]
[TD="width: 123"]

1522.17

[/TD]
[/TR]
[TR]
[TD="width: 38"]

12

[/TD]
[TD="width: 112"]

09-05-18

[/TD]
[TD="width: 153"]

0000-00202235

[/TD]
[TD="width: 123"]

1325.36

[/TD]
[/TR]
[TR]
[TD="width: 38"]

13

[/TD]
[TD="width: 112"]

09-05-18

[/TD]
[TD="width: 153"]

0000-00203145

[/TD]
[TD="width: 123"]

955.63

[/TD]
[/TR]
[TR]
[TD="width: 38"]

14

[/TD]
[TD="width: 112"]

09-05-18

[/TD]
[TD="width: 153"]

0000-00203879

[/TD]
[TD="width: 123"]

2125.45

[/TD]
[/TR]
</tbody>[/TABLE]

Joe4 · Sep 14, 2018

Do you actually need to change their underlying value, or just change how they appear?
Their appearance can be changed pretty easily using a custom format of "0000-00000000".

Joe4 · Sep 14, 2018

If you need VBA to actually change their underlying value (converting from a number to text), this should do it:

Code:

Sub MyFix()

    Dim lrow As Long
    Dim r As Long
    
    Application.ScreenUpdating = False
    
'   Find last row with data in column B
    lrow = Cells(Rows.Count, "B").End(xlUp).Row
    
'   Loop through all cells in column B starting at cell B11
    For r = 11 To lrow
        If Cells(r, "B") <> "" Then
            Cells(r, "B") = Format(Cells(r, "B"), "0000-00000000")
        End If
    Next r
    
    Application.ScreenUpdating = True
    
End Sub

Panoos64 · Sep 17, 2018

Thank you Joe, actually i wanted as VBA and your code works perfect. Thanks once again,. Hv a great day!

Rick Rothstein · Sep 17, 2018

Panoos64 said:
Thank you Joe, actually i wanted as VBA and your code works perfect. Thanks once again,. Hv a great day!

Here is an alternate macro that you can also try...

Code:

[table="width: 500"]
[tr]
	[td]Sub MyFix()
  With Range("B11", Cells(Rows.Count, "B").End(xlUp))
    .Value = Evaluate("TEXT(" & .Address & ",""0000-00000000"")")
  End With
End Sub[/td]
[/tr]
[/table]

Panoos64 · Sep 17, 2018

Thank you, your code works too. Thanks once again for your support. Hv a nice day!

Peter_SSs · Sep 17, 2018

Panoos64 said:
Thank you, your code works too.

Hmm, doesn't work for me, though this does.

Code:

Sub MyFix()
  With Range("B11", Cells(Rows.Count, "B").End(xlUp))
    .Value = Evaluate("if(" & .Address & "="""","""",TEXT(" & .Address & ",""0000-00000000""))")
  End With
End Sub

Rick Rothstein · Sep 17, 2018

Peter_SSs said:
Hmm, doesn't work for me, though this does.

Code:

Sub MyFix() With Range("B11", Cells(Rows.Count, "B").End(xlUp)) .Value = Evaluate("if(" & .Address & "="""","""",TEXT(" & .Address & ",""0000-00000000""))") End With End Sub

Hmm, I wonder why it did not work for you... I tested it before I posted it and it worked fine for me (XL2010).

Panoos64 · Sep 17, 2018

Peter your code works perfect, also thanks for noticing that, Rick's first code it was not working. It was copying the same number in col. "B", but Rick you adjusted and now runs perfect and correctly. Many thanks both for your continuance support!

Peter_SSs · Sep 17, 2018

Rick Rothstein said:
Hmm, I wonder why it did not work for you... I tested it before I posted it and it worked fine for me (XL2010).

As the OP has suggested, It simply repeats the first number, including for any blank or text data cells.

Before

Book1

B

10

Reference

11

202121

12

202235

13

14

203879

15

Text

format cells

After

Book1

B

10

Reference

11

0000-00202121

12

0000-00202121

13

0000-00202121

14

0000-00202121

15

0000-00202121

format cells

Place zeros "0" at left to a number

Panoos64

Well-known Member

Excel Facts

Joe4

MrExcel MVP, Junior Admin

Joe4

MrExcel MVP, Junior Admin

Panoos64

Well-known Member

Rick Rothstein

MrExcel MVP

Panoos64

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

Rick Rothstein

MrExcel MVP

Panoos64

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Place zeros "0" at left to a number

Well-known Member

Excel Facts

MrExcel MVP, Junior Admin

MrExcel MVP, Junior Admin

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP, Moderator

MrExcel MVP

Well-known Member

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock