How to add zeros at the end

T

timpepu

Board Regular
Joined
Mar 4, 2008
Messages
89
Hi !

I have text like this 00000058 , I want add 0 so many times that it is 14 character long like this 00000058000000 .
How should I do it with VBA.

Br
Timo
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
Does it need to be done in VBA? Maybe a custom cell format

00000000"000000"

would be sufficient.
 
Upvote 0
I have already done vba code that fill 0 at the beginig so many times that it is 8 character !
 
Upvote 0
Using the format 00000000"000000" would eliminate the need for the vba, it does both in 1 go

Sheet4


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 160px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: right">00000001000000</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">00000012000000</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">00000123000000</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">00001234000000</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">00012345000000</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right">00123456000000</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right">01234567000000</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: right">12345678000000</TD></TR></TBODY></TABLE>


Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

For the VBA solution, it just needs

Code: 
& "000000"
added to the end of the line producing existing 8 characters
 
Last edited:
Upvote 0
Hi !
Fine but I have to export it to access and I can't get any 0 ,only 58 .
I my mind it should be formatted txt add fill zeros using VBA
 
Upvote 0
Copy the code into the worksheet module for the sheet where the codes are entered, change Target.Column = 1 to match the column with the codes.
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column = 1 Then Target = Format(Target, "00000000") & "000000"
Application.EnableEvents = True
End Sub
 
Last edited:
Upvote 0
With a pre-existing column of data:

Code: 
Sub test()
Dim c As Range
For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    c.NumberFormat = "@"
    c = Format(c, String(8, "0")) & String(6, "0")
Next
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Add zeros preceding number so that number is a total of 6 characters
Replies
2
Views
142
Rick Rothstein
Rick Rothstein
SanjayGMusafir
  • Solved
VBA to add new record - Declaring variable
2
Replies
14
Views
201
Micron
M
J
  • Question
Excel VBA Help
2
Replies
16
Views
436
justincaza
J
S
  • Question
VBA to ignore certain MAC Address values but process all others
Replies
1
Views
206
myall_blues
myall_blues
L
  • Question
Check for any match between three ranges but ignore zeros
Replies
4
Views
180
Louis chiron
L

Forum statistics

Threads
1,215,232
Messages
6,123,763
Members
449,120
Latest member
Aa2

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top