Free Microsoft Excel 2013 Quick Reference

How to suppress leading zeros in Excel

I recieve a spreadsheet that has phone stored as a 10 digit number. I need
to parse it to end up with 3 columns; AreaCode, Exchange, Extension. I use
Text to Columns to split this. The problem I have is that leading zeros are
suppressed. For example if an extension is 0040 the leading zeros are
suppressed and it is displayed as 40. How can I make sure it is always
displayed as a 4 digit number?

Post your answer or comment

comments powered by Disqus
when calculated something like a baseball hitting percentage, how do you drop
the leading zero in the number....0.350 versus .350

thanks, Ken

How to use this code in excel 2000?


[COLOR=#333333][FONT=Lucida Grande]Dim arquivo As String[/FONT][/COLOR] 
[COLOR=#333333][FONT=Lucida Grande]Set fd = Application.FileDialog(msoFileDialogFolderPicker)[/FONT][/COLOR] 
[COLOR=#333333][FONT=Lucida Grande]If fd.Show = -1 Then[/FONT][/COLOR] 
[COLOR=#333333][FONT=Lucida Grande]arquivo = fd.SelectedItems(1)[/FONT][/COLOR] 
[COLOR=#333333][FONT=Lucida Grande]MsgBox "A pasta selecionada é " & arquivo[/FONT][/COLOR] 
[COLOR=#333333][FONT=Lucida Grande]Else: arquivo = ""[/FONT][/COLOR] 
[COLOR=#333333][FONT=Lucida Grande]End[/FONT][/COLOR] 
[COLOR=#333333][FONT=Lucida Grande]End If[/FONT][/COLOR] 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I am generating a Sheet called Scripts based on a series of user inputs in other Sheet tabs in the same Workbook.

I need some ideas on how to execute this Scripts in excel automatically.

Right now it is working, if I select all contents of Scripts sheet and paste in the Query Analyzer (Manual process).

I was trying to save this sheet as text document, but somehow it is not working with the options (Unicode, DOS, space delimited).

Any ideas would be appreciated.


I have been trying to figure out how to write a formula in excel that does certain things. Here are the things I am trying to do.

1. if I put a number (1,2, 3 ect) in we will say c1 I want D1 to say WW1 or WW2 ect depending on the conflict.

2. If I put a number (1, 2, 3 ect) in say F1 I want a proper name to show up in G1. Like 1 in F1 and in G1 I want a name like Chicago.

I hope that someone gets the drift of my questions. I have not written any formulas for numerous years and I am totally lost in Excel, thus I am solicitating someones assistance.

thanks for your help.


How to get column properties in excel. This property window contains 4 tabs
namely 1. Value Format, 2. Column Format, 3. Data format and 4. Conditional
format. The main thing I am looking at is "Custom CSS Options (HTML Only)
check box.

I have looked all over to determine how to maintain leading zeros in a
formula result. I have tried formating cells as custom '0000' and as text to
no avail. I have two cell that I would like to combine into one cell.
Example, b1=60; c1=07 when I combine these two cells I get a1=607... This is
killing me. Any quick help would be greatly appreciated.

How to stop opening outlook in excel when clicking into a cell that contains
an email address.

how to copy a cell in excell and paste in a txt file with macro

I have a column of variable length numbers/text (6-12 characters). I need to
place leading zeros in each of the cells to create a standard length of 15
characters. What's the easiest way to do this?

Hi everyone,

Could you please tell me how to create a sheet in Excel when the sheetname include space?
like the sample below:

Create Table [as test one] (column1 int, column2 ...)

I just want to create a sheet in a excel table, and there should be spaces in the sheet name, like "as test one". I try to send the sql above and get a new sheet with the name like "as_test_one". But I hope I can get the sheet name like "as test one", not "as_test_one". Who can tell me how to write this sql query? Thanks a lot.



I found that Excel does not allow C-lang style escape sequence. E.g. "
like escape is not allowed. I tried with other tinkering as well but
did not work. Could not found direct pointer from Excel help.

Can somebody help, how to put escape sequences in Excel functions?

Thanks for your time.

- KA

How to make a game in Excel??? I know there is a way to make a game with
function or something like that but I forgot how or where to do it. Does
anyone know how to do a game??? Game looks like flight simulator.

Dear Friends,

Do you know how to write a marco in Excel to delete worksheet automatically, without prompting me to click OK?

I have tried one by using the command:

but it will ask me to click OK before delete it. It looks not professional.

Thanks a lot.

I need to display leading zeros in whole numbers eg 05324

1. How to set GUID function in Excel as I have to import into MYSQL table some predefined values like UUID.

2. How to set Date&Time like access
Format(Now(), "yyyy-mm-dd hh:nn:ss")

Is this possible to be achieved by clicking one button and within ONE row can be two fields with value like NOW() and UUID in terms of MYSQL?

Need help.

How to use this code in excel 2000?

Dim fd As FileDialog
Dim arquivo As String
Set fd = Application.FileDialog(msoFileDialogFolderPicker)
If fd.Show = -1 Then
arquivo = fd.SelectedItems(1)
MsgBox "A pasta selecionada é " & arquivo
Else: arquivo = ""
End If

Dear all,

I have an Excel macro program which uses ADO connection. It connects to our
AS/400 database. When the program connects to the AS/400 system for the first
time after the program is opened, it prompts a AS/400 login dialogue box and
then the user keys in his/her user name and password. If I want to capture
the user name from the AS/400 login dialogue box, then how to do it? How to
write the code in Excel macro? Is it possible?
Thanks in advance!


Hello Everyone,
I need some help with a formula.
I need to know how to use the following in excel format:
+ or - 1% or every $10 change in price, the base number is $555.
Any help would be greatly appreciated.

I need to keep the leading zeros in my CSV files. How can I do this?

Hi all,
I have an excel file is .xls which I need to convert to .csv to upload my price list to my website but am aware that this will get rid of the leading zeros in the part number. The file has a column of part numbers and a column of prices eg
01234 1.50
01235 2.75
01236 2.95
01237 3.25

Can you please tell me how to retain the leading zero.
Thanks in anticipation

nI wish to remove the leading zeros from a column of numbers in an Excel 2000
Excel 2003 handles this easily but I need to be able to do the same thing in
excel 2000.
Would appreciate your comments.

I am working in Excel and would like to show leading zeros in cells that have
been formated as number cells. Is this possible and how is it done?

I need to put leading zero's in excel cells. Can somebody please help.



I have a simple spreadsheet that looks like this:

Origin State Dest State Company

....and so on. Basically, the first 2 columns have all states/provinces and
column 3 has the company we use. I need to create one matrix with all
states/provinces in Row 1 and in Column 1, and all the cells in between are
populated with the company.

Question is How can I do this using MS Excel using Pivot Table. I can do this using Business Objects or Crosstab Query in MS Access but how do I do it in Excel using Pivot Table? This can be done by using INDIRECT and MATCH functions but this involves a couple of steps. Is there any shorter method?

I have attached an excel sheet that gives what I have, What I can do using Access or Business Objects and what I get using Pivot Table in Excel. The normal Pivot Table in Excel gives numbers instead of the company name even if I use Max or Min function (which works fine in MS Access query). So what am I missing here?

Thanks in anticipation,
Warm Regards,

No luck finding an answer? You could always try Google.