Need leading zero

[bam12]

I need a leading zero to not be deleted

Example. Need 011

 

[Ron Coderre]

How do your numbers get in the cells?
- Formulas?
- User input?
- Are the numbers always located in the same row, column, or range?

 

[SydneyGeek]

If the field is a number field the leading zero will be stripped. If it's text the leading zeros will be kept.
You can *display* a leading zero in a number field with custom formatting but the actual value will not have the zeros, so searching could be confusing.

Denis

 

[Chipb]

I am having a similar issue. I do a lot of database work that has leading zeros. I use the custom field to show the leading zero's, however when i save it as a csv. file and reopen it doesn't show the leading zero's. Any idea on how to resolve this?

 

[Joe4]

[COLOR=#333333]however when i save it as a csv. file and reopen it doesn't show the leading zero's.[/COLOR]

What program are you using to view the CSV?
Do not use Excel. Even if the leading zeroes are in the CSV file, Excel will drop them upon opening the CSV file.
Use a Text Editor like NotePad or WordPad to view your CSV file.
If you do that, do you see the leading zeroes?

 

[Chipb]

Yes I can see them in notepad, but there are many times I need to do additional work and would like to reopen in excel

 

[Joe4]

If you see that they are in NotePad, then that means the leading zeroes are really there!
The issue is not with the file, the file is fine!
The issue is with how you are opening the files in Excel.

With CSV files, when you open them using Excel, Excel tries to determine what the format of each field is. Anything that contains all numbers will be treated as a number, so all leading zeroes of these fields will be dropped.

In order to open them in Excel and keep the leading zeroes, you need to invoke the Text Import Wizard so that you determine the format of each field and not Excel. There are two ways of doing that:

1. Change the extension of your CSV file to something like TXT

2. Open Excel (without opening a file), go to the Data menu, go to the "Get External Data" ribbon, and select From Text, and browse to your file.

In both cases, you need to go through each field. For the fields you wish to keep the leading zeroes, you need to select the "Text" Column Data Format in Step 3.

 

[Chipb]

Thanks so much Joe. At least I know there is a way to accomplish this now.