Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  HOME    TRACKER    Visual Basic

Leading Zero Problem In Excel / VBA

I have numbers (equity tickers) that start with 0, 00 or 000. Excel formatting takes the zeros out. Eg 000123 becomes 123. The trouble is that even if I add a ' that when I do a =match in VBA that it doesn't get recognized.

intmatch = WorksheetFunction.Match(Correlation.Range("TheTicker"), _
Chg1.Range(Chg1.Range("B4"), Chg1.Range("IU4")), False)


In the "TheTicker" I concatenate and add a ' to the text to try and fix this problem. Eg if the user enters 000123 it would make it '00123 which should allow the =MATCH formula to work. In excel it seems to work but in VBA it does not. Should I make this .text or .value or something????

View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Keep Excel From Dropping Leading Zeros
When I open a CSV file in Excel it loves to reformat the cells to what it thinks should be the correct values before I get a chance to tell it how I want to format the data.

As an example I have a CSV file with the following data in it

General Decription,09089,1988
General Decription,09088,1989
General Decription,09089,1987

when I open this file in Excel the leading zeros in the second column have all been removed because Excel think this a number and I should not want the zeros. Is there any way to keep this from happening? I want my zeros


Leading Zero Trancated When Exported To Excel
I have successfully exported recordset set to Excel, but some of of the details exported with leading Zero (ie.. 01123456) zero were trancated as in 1123456, by the way the recordset field is define as char..any suggestion will be highly rated thanks...

Hi All,

I'm writing data from a text file to Excel.

However, leading zeros for numbers that are supposed to be treated as text disppear. E.g. 00001 and 00102 appear as 1 and 102 respectively.

Any help would be appreciated.


Creating .csv Files That Retain Leading Zeroes In Excel ***RESOLVED***
I am attempting to write code that will create a .csv file that can be opened with Excel intact. Example: one field may contain a string such as "FE24" or "0026". "FE24" will appear in Excel as-is, but it insists on changing "0026" to "26". Since the end-users of this program are not tech-savvy, creating a .txt file that can be imported into Excel is not an option. How can I force the .csv file to be opened with Excel without dropping leading zeroes? Any advice will be greatly appreciated.

Thanks in advance,

How To Pad Leading Zero?
VB Code:
Private Sub Form_Load()   ' to find next caseno   Dim caseno As String   Dim next_caseno As String      caseno = "CR20041906"      MsgBox "the case no is : " & (Val(Right(caseno, 4))) + 1      MsgBox "the next case no is : " & Left(caseno, 6) & CStr((Val(Right(caseno, 4))) + 1)  ' CR20041907      caseno = "CR20050001"  '???   End Sub

if using (Val(Right(caseno, 4))) + 1 for caseno="CR20050001", then only "1", how to pad zero(s) in front to read like "0001"?

Note: the caseno is always casetype(CR), current year, caseno


Where's My Leading Zero?
I hope that this is simple. I'm donig this very basic format command:

mYear = Format(Now, "yy")

But I'm getting a return of "7" and I need "07". mYear is type INT

What am I missing?


There are 10 types of people in the world: those who understand binary, and those who don't.

Edited by - AbbydonKrafts on 4/16/2007 7:32:22 AM

Leading 0 Problem ..PLEASE HELP!
Please can someone help me? I'm new to VB and have a problem that I've tried for hours to solve

I'm using com1 and sending a command to a mobile phone .. no problem there
BUT the reply i get is missing the leading 0's.. this is the code:-

Private Sub MSComm1_OnComm()

If MSComm1.CommEvent = comEvReceive Then
strRx = MSComm1.Input

For j = 1 To Len(strRx)

log = log.Text & Hex(Asc(Mid(strRx, j, 1))) + " "

Next j

End If
End Sub

and get : 2 E D2 5 1 3B 8 3A 5 8 30 25 69 68 88 48 A 3 as the reply...

So i tried :-

Private Sub MSComm1_OnComm()

If MSComm1.CommEvent = comEvReceive Then
strRx = MSComm1.Input

For j = 1 To Len(strRx)
logs = Asc(Mid$(strRx, j, 1))
log.Text = log.Text & Format(Hex$(logs), "0# ")

Next j

End If
End Sub

Hoping that would work but i get 02 ED205 01 3B08 00 05 08 30 25 69 68 88 48 A03 as the reply??

I should get 02 0E D2 05 01 3B 08 3A 05 08 30 25 69 68 88 48 0A 03

Really hope some one will feel sorry for me and help..

Also 1 last this .. how could i choose the bytes that are displayed in log.text like if i only wanted to disply bytes 2,5,7 ??


Holding Leading 0's
I am trying to create a formatted data set that will then be converted into CSV file for electronic submission as a VAT return.

The date for the submission needs to be in the format ‘ddmmyy’. From this I also need to submit a value for the ‘ddmm’ part. I should be able to extract this information easily enough from a user input box to get the ‘ddmmyy’ part and then just use the left function to take the first four characters.

However my problem arises when the day of the month is before the 10th so that the user has to enter a leading 0 i.e. ‘090704’. This leading 0 seems to be dropped, thus making the value that it attempts to submit ‘90704’, which will be rejected. Further to this the left function then selects ‘9070’ which is again incorrect. I know this problem occurs in Excel, however I thought I would be able to get around it using VBA as the variable value would not drop the 0.

I have also tried setting the variable to be a string, but to no avail.

I would be mighty grateful if someone knows a solution.



Leading Zeros
Hello to all,
I have a text that I search. The text is 10 characters long as a field in the backend. Its a fixed length.

Example of the way the users entered the text when they search


but I would like to pad the first 5 numbers with leading zeros or the year so that it returns the full string

00037/2004 (valid search) will get records.

Users entered Output Return String
7/2004 00037/2004
37/04 00037/2004
37/2004 00037/2004
037/2004 00037/2004
0037/2004 00037/2004

I am trying to do this.

dim strOutPut() as string
dim strRetVal as string


do while strOutPut(0) < 5
'pad with leading zeros
strRetVal =strRetVal & "0" & strOutPut


I need to pad the year after the slash.

I am lost here.

thanks for any help

Save The Leading Zero??
I'm writing a simple encryption program, and I'm having a little trouble. I know there are already posts with encryption code out there, but I wanted to write my own :

Private Sub cmdEncrypt_Click()
Dim intI As Integer
Dim intU As Integer
Dim strPlainText As String
Dim strPlain As String
Dim lngCommaText As String
Dim lngEncrypted As Long
Dim Temp() As Byte

strPlainText = txtPlainText.Text
strPlain = UCase(strPlainText)

ReDim Temp(Len(strPlain))

For intI = 1 To Len(strPlain)
Temp(intI) = Asc(Mid(strPlain, intI, 1))

For intU = 1 To UBound(Temp)
lngCommaText = lngCommaText & Temp(intU)

txtEncrypted.Text = lngCommaText

Dim intFile
Dim intL

intFile = FreeFile

Open App.Path & "/reg.txt" For Output As #intFile
For intL = 1 To UBound(Temp)
Write #intFile, Temp(intL)
Close #intFile

End Sub
that is the code I have so far. I want to be able to save the 'asc' value as a 3 digit number in my temp array. I've tried "format(thisnumber, "000") but it didn't work. Anyone have any Idea how I can save the values in my array with the leading zero in tact?

Deleting Leading Zero's
I have question about deleting the leading 0. For example, I have
MR: 01234567
actullay I don't want the 0 in front the MR. It can have from 3 to 10 numbers some time the user puts only 7 number. Is there anyway, where the user puts the 0 even by mistake but the program deletes it. First 0 or the second 0.
00123456 or 0123456
I want it to just 123456
Thanks in advance

Leading Zeros
In a module, how can I establish how many leading zeros a string has?

We have a Serial Number database where the serial numbers vary in format across different product groups.  The serial number is a combination of the product code and a number with other pre/suffixes.  I have set up a form allowing a user to input the first number in a batch and the qty in the batch and the code then generates the follow on serial numbers.  This works fine for the most part but I'm struggling with one particular range of products where the serial numbers have leading zeros eg 000026.

I'm sure the solution is simple but I just can't get my head round it!

How To Make Leading Zero?
I had an integer called intMonth that is set to the value of Month(Date.Text)
intMonth = Month(Date.Text)
If the month is let say January (1), i won't have a leading zero. I had tried to assign another integer that equals to 0.
intZero = 0
intMonth = intZero & intMonth
But it didn't work too.
How can i make a leading zero??
Thank You.

Remove Leading Zeros
My issue is that I've a range of cells (in a column) that contain UPC codes.
To arrive at those I had to use the TextToColumns function to strip out the hyphens.
I need to remove all the leading zeros and had tried the following code I found here but am getting an overflow error because the value is outside of the range.
Is there another approach to achieve this?

For i = 2 To varLength
varCell = "K" & i
Selection.R1C1 = CStr(CInt(Range(varCell).Value))

Many thanks,


Loosing Leading Spaces.
When I open a txt file up for Input to populate a multi-line text box...
like this:

If sPath <> "" Then
Open sPath For Input As #1
'Loop till finished reading file
Do While Not EOF(1)
Input #1, strBuff
strFile = strFile & strBuff
If Not EOF(1) Then
strFile = strFile & vbCrLf
End If
Close #1
'Display the file opened
Form1.Caption = sPath
End If
'Put contents in your text editor control
Text1.Text = strFile
I loose all my leading spaces...
In other words... when my text file looks like in notepad

This is line one
line two has four spaces in front of it
line three has a tab in front of it

line for is way out here
It looks like this when my code opens it:

This is line one
line two has four spaces in front of it
line three has a tab in front of it

line for is way out here
Am I using the wrong method of reading the file?

If I put a bunch of leading spaces in the text and save it like...

strFile = Split(Text1.Text, vbCrLf)

Open sPath For Output As #1
For loopc = 0 To UBound(strFile)
Print #1, strFile(loopc)
Close #1
It saves it just fine. I open it in notepad and all the leading spaces are there, just not when I open it with my app.

Thanks in advance for any advice.

Leading Zeros In Text Box
I want to display the leading zeros in a text box. I have a small database program that when the user Addes a new product, the form opens with the new product ID number displaid in the text box, (database absolute position +1) in my Access Database I have the field length set to 4. I want the leading zeros to display when I display the id numbers, and when I add a new Id.

So when I addNew when the form opens, I would want to see 0010, for the tenth idem. , 0200 for the 200 th item.

Can someone help me out?

Dale S

Remove Leading Zeros
I have a text box that is a total of 7 chars. Of the 7 chars, there can be 0 - 6 number of leading zeros. Can someone help me with a function that loops through each char to determine if it is a zero and delete it? (e.g., 0000034 will be 34 after and 0012005 will be 12005 after). The number of zeros can vary, so I want to only delete all zeros up until the first non-zero value. Thanks in advance.

Format For Leading Zeros
Anyone know how I would use the FORMAT function to all a formating that will insert leading zeros like the following.......ZZZZ9 would display 00009. So the function would have to be something like this

txtbox = 9

FORMAT("txtbox", "ZZZZ9")

Any help would be appreciated.

Removing Leading Zeros
I'm using this method to remove any leading zeros from a string (which is an integer):

Orders(i) = CStr(CInt(Orders(i)))

But this method gives an "Overflow" error with anything larger than 31000 (ish). That seems very small to me to be giving this sort of error.

Any ideas?


School Project Leading Nowhere
Ive seen another guy on this forum who is asking the same question basically. Im using VB 6.0 Professional, and trying to do a game that is pretty much deal or no deal. I need pretty much what the other guy needs.

How can i get the array of 26 money values to randomize to one of the 26 cases.

How can, when selected in one of the deal sets, i make the value be removed from the array, and a deal made from the remaining values in the array.

Im not very efficient with VB, but im sure i can work my way around the other aspects of the project.

Removing Leading 0's From String
Im looking for a faster way to remove the 0's from the beginning of my strings.
right now i am using this.

VB Code:
cleanthis = 1While cleanthis = 1If Left(testline13, 1) = "0" Thenstringlen = Len(testline13)testline13 = Right(testline13, stringlen - 1)Elsecleanthis = 0End IfWend

the number of zeros changes.
your help with this is very much appertiated

Getting Rid Of Leading And Trailing "
If I input a file into a string using a simple ‘Open (Path)for Input’ statement, The resulting string starts and ends with “, e.g. “h%*doih&*o*%” . If I open the same file with a FileSystemObject type statement, the string starts and finishes with its first and last characters.

In the first instance, how can I rid the string of the “s. The Replace(string,””””,””) does not work for this situation.

Losing Leading Zeros In CSV
Hi All,

I've got a problem when using leading zero's in my spreadsheets that I have to save as CSV before importing into my SQL database using a vb interface

I have a spreadsheet with my column, account number. It has to keep the leading zero's therefore I add a ' before entering zero's.

If my account number is 0009 I enter '0009 into the cell.

I then save the file as a CSV file, and when I open the file with notepad the leading zeros are kept.

My problem is if I open the CSV file with excel, the leading zero's are gone the moment I open the file. I've tried different formatting but everytime I open it with excel it is gone.

The file must be able to open in Excel, as it easier to change and edit data in excel due to the readability of a large text file.

Any help will do.


Leading Zero's Dropped With Csv File

I have a csv file. I open the file via code as a recorset with ms text driver. The problem is that when I have a value of 00001, it only reads it as 1. How can I prevent the leading zero's from being dropped.

Leading 0 In Hex Number *RESOLVED*
Hi All.

I'm using the following code to convert dec to hex, but when the hex number is 0 to 9 or A to F the leading 0 is not displayed , I would like it to look like 01 or 0A, how can this be done ??

HexCon = Hex(Text2.Text)

Remove Leading Zer0's
i used access with a col num as autonumber

i used this code to pad leading 0 -> Format$(rs!Num, "0000")
for listview purpose

but now how do i remove the leading zero from the record selected thru listview ?


ADO Chopping Off Leading And Trailing 0's
I'm reading a text file into a recordset, but when I look at the resulting data, many of the values are being concatenated, with the leading and trailing zeroes being chopped off. So, 02001.000 turns out to be 2001. These values are job #'s, so I need the zeroes there. I'm using access 2002, not sure if that's the problem, or if it's the way I'm interacting with the DB, or data types or what.

VB Code:
'create recordset    Set cn = New ADODB.Connection    Set cn2 = New ADODB.Connection        cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & ";Extended Properties=Text;"    cn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=po_fixer.mdb;Persist Security Info=False"        sSQL = "SELECT * FROM detail.txt"    Set rs2 = New ADODB.Recordset    rs2.Open "SELECT * FROM detail", cn2, adOpenKeyset, adLockPessimistic, adCmdText        If cn.State = adStateOpen Then        Set rs = New ADODB.Recordset        rs.Open sSQL, cn, adOpenKeyset, adLockOptimistic, adCmdText        rs.MoveFirst                MousePointer = vbHourglass                Do While rs.EOF = False                    With rs2                .AddNew                .Fields("linenum").Value = rs.Fields(0).Value                .Fields("item").Value = rs.Fields(1).Value                .Fields("qty").Value = rs.Fields(2).Value                .Fields("brnloc").Value = rs.Fields(3).Value '<---chopping here                .Fields("job").Value = rs.Fields(4).Value '<---chopping here                .Fields("acct").Value = rs.Fields(5).Value                .Fields("gross").Value = rs.Fields(6).Value                .Fields("uom").Value = rs.Fields(7).Value                .Fields("poid").Value = rs.Fields(8).Value                .Update            End With                        rs.MoveNext        Loop

That's how I update the records. I checked the value of "job" after reading in the text file, and the zeroes are there, so it's somewhere in the above code, or just in Access itself where it's happening.

Any ideas?

Thanks in advance.

Omit Leading Zeroes
How can I format cells in VSFlexGrid so that leading zeroes will not be displayed?

Keep Leading Zeros In A Variable
I want to send a variable the number "001" then add 1 to make then next number "002" - how do I Dim this variable to make it hold the 2 preceding zeros and when it adds 1 to the third place.

Putting Leading Spaces...
I have an array of 10 TEXTBOXES(that accepts only digits), I have posted a similar post before but the replies of which did not really seem to work, I still am figuring out how to achieve this:
Whatever is entered in those textboxes by the user I store into a variable & am writing it to a text what I really need help on is this: if lets say users enter 8 digits, so 2 leading spaces should be added & then the figure of 8 digits should be written to the text file with the spaces. I can do the spacing but it does not show the spaces when I open the text file. And if users enter 7 digits, 3 leading spaces should be shown. Any ideas???

Add Leading Zeros To A List
I can't get my zeros to format right for a project I'm working on. Can anyone tell me how to do this in VB? I need the random numbers in my list to be preceded by two zeros so the numbers (between 1 and 200) will sort correctly.
This is what i have:

For i = 1 To Int(Rnd * 500) + 1
lstRawData.AddItem Int(Rnd * 200) + 1
Next i
i = Str(i)
Format (i = "0000.00")

Do I have the format statement wrong?


Leading Zero In Month Code
I am trying to display a month in a text box and all single digit months are displaying as single digits instead of 01, 02 etc..

Padding With Leading Zeroes
I believe this to be a simple problem to solve, however, I am a mainframe/AS400 programmer (COBOL/VisualAge Generator) and I am not able to figure out how to complete this simple task in VB. In my program I am reading a row from a Access 97 table. The Primary key is a 4 byte text field called RECNO. I store the record number in this field. When I save this field in my application, I want it saved in the format "0001". However moving a numeric field into a string field will only produce "1". My question is how do I pad the text field with leading zeroes?

As always thanks a bunch for your help.


Removing Leading Box [][][] Characters.
While reading an ID3 tag, there are a bunch of box [] characters that destroy what ever comes after them (for some reason!!), so I need to... destroy them. What ascii or chr() character is the box [] character???

How To Put Leading Space As Zero In Text Box.
Hi Friends

Suppose I got Value 4 in my TextBox Text1.txt

Now I want to Show It as "00004". My database Fields is Charecter.
and Length of Fields is 5

Which Function I have use. I tried Format, it is not Working



How Do I Get Rid Of Leading And Trailing Commas??
This the example I'm using, it formats to:
Need to remove the leading and trailing commas....

Dim strchars As String
strchars = "[11,11][N,N,N,E,E,W][10,01]"

Dim invals(0 To 11) As String
invals(0) = "["
invals(1) = "]"
invals(2) = "0"
invals(3) = "1"
invals(4) = "2"
invals(5) = "3"
invals(6) = "4"
invals(7) = "5"
invals(8) = "6"
invals(9) = "7"
invals(10) = "8"
invals(11) = "9"

For x = 0 To 11
strchars = Replace(strchars, invals(x), "")
Next x

MsgBox strchars

Thanks guys for you help this is an awesome site!

Keeping Leading Zeros?
I have a number which needs to continually increase by 1, and keep its leading zero.

The number will go from 00000 to 99999 increasing by 1 each time. The number is called FileNo


Do you have any ideas how to do this?

Not Able To Save Leading Zeores

I have a filed in SQL 2000 table, declared as varchar(10). I m genefating a number and adding leading zeroes to it and saving it to the field.

For example : if 22 is the new number generated then , I m adding 8 leading zeroes to it saving it in database as '0000000022'.

But the problem I m facing is, then leading zeroes are not getting saved in the database. I checked with the data type and the value that I m passing to the database. All are correct, but when the data is saved to database the leading zeroes are missing.

Can some body provide me a soultion, its very urgent

Thanking in advance

Trim Leading Tabs
Is there a way to trim leading tabs from a string? I am reading lines in from a text file and assigning the text in each line to strings.

Line Input #FileNum, tempString

I need to preserve and use the tabs in some situations, but I don't want the tabs on the left in other cases.

FileSearch Leading To Unexpected Results
Not sure if this is a question at all, but I'm wondering if this happened to anyone else.

FileSearch always worked fine.

Then all of a sudden, when I created a new application in which I search for XLS files, I get 0 files found, when I look in a dir with 10 files in it!

After a lot of research (and a visit to the local hypnotherapist), I remembered that I've been toying with my indexing service. The service was running on my Win 2000 computer. Stopping the service led my application to the correct behaviour!

So how can they invent a service like that if it messes up your whole faith in the filesystem???

Immediately stopped the service from running ever again on my computer

Display Leading Zeros In A Number
I thought this was a simple thing to do (and it might still be)

I want to display numbers as two digits, regardless of whether they are greater than 10 or not.

10 would display as 10
0 would display as 00
7 would display as 07

I have tried using formatnumber(intNumber, "00") but that didnt work.
Is there any way to format my number in VB or will I have to write my own function to handle this?

Format String - Getting Rid Of Leading Zeros
Hi Guys

I'm very new to Excel VBA and I would like some ideas on doing some string manipulation for a small excel macro i'm writing for work.

Basically I need to do this:

Transform '30/03-07 A' to '30/3-7 A'

I need to get rid of all leading zeros

The problem is that the string can look quite different from the example above. Here are some examples of how it may look like:



48/02-2ST2 (needs to be converted to 48/2/2ST2)


214/04-1 (needs to be converted to 214/4-1)

Get the syntax? There are always one backslash (/) with a maximum of four leading digits, then a mix of digits and characters followed by a "-" and then again a mix of digits and characters.



Trim Leading Space Question
I want to run down a column and trim the first leading space. heres what I have, but its not working. Any suggestions:

Dim chkString As Range
For z = 1 To 15567
Set chkString = Left(Range("z1"), 1)
If chkString = " " then
End If
Next z
End Sub

thanks in advance, john

Display Leading Zeros In Text Box
Let me try to explain what I am looking for.

I have date stored in Access with leading zeros. 0001, 0002...

I need to display the numbers just as they are stored, with the zeros because I am filtering like this:

dbVenProd.Recordset.Filter = "pro_id = '" & txtProdID.Text & "'"

and when I load the form the zeros are not in the text box just the number, 1, 2 .... , so my filter will not work..

did I explain my problem well enough?

Let me know if you need more info from me

Dale S

Select From MS SQL Server With Leading Zeros?

Is it possible to select from MSSQL Server with leading zeros? So that my data is sorted right in a ListView

Tia! Legshot

Displaying Leading Zeros On Integers

I am parsing a set of files in vb and the file names have "01", "02", etc in the names. I have written a loop that will go through the files, but the integer I am using in the loop is 1, 2, etc.

So when looping through the files, I tag the value onto the end to parse the right file.

But how do I get my variable to hold the value "01" (as an integer or number value of some sort", rather than "1"


Trim Leading And Trailing Silence From WAV
Does somebody know how to detect and trim leading and trailing silence from WAV files?

Remove Leading Space Character

I'm changing an integer value to a string value but VB keeps putting a leading space (ascii 32) character in the variable.



Dim JobID As Integer


JobID = 0
JobID = str(JobID)

and the new string variable JobID now equals " JobID". How do I remove this character? Is it like C where i could say redim the JobID character array to remove the first element? or is there a different way in VB.

Thanks very much

Displaying Leading Zeros In A Combo Box
I have a combo box which contains numbers from a field in an Access
table that contains numbers with leading zeros.
The leading zeroes are not being displayed in the combo box at run
time. How can I format the combobox such that the leading zeroes are

************************************************** **
This e-mail and any attachments thereto, is intended only for use by the addressee(s) named herein and may contain legally privileged and/or confidential information. If you are not the intended recipient of this e-mail, you are hereby notified any dissemination, distribution or copying of this email, and any attachments thereto, is strictly prohibited. If you receive this email in error please immediately notify me at (212) 735-3000 and permanently delete the original copy and any copy of any e-mail, and any printout thereof.

Further information about the firm, a list of the Partners and their professional qualifications will be provided upon request.
************************************************** **

Copyright © 2005-08, All rights reserved