Number and Date problems

[keeegs]

I guys I'm using Excel 2010 and I'm having a few problems with formatting.

I add information from cells as string. create a new sheet and use that information plus other information to make forms to print out.

All that works fine its just it drops of the first 0 from the numbers and dates.

I have a message box that comes up before it does all that and they are all fine

But when it put the information into the new sheet it misses of the front 0

Information examples that they are missing are

meant to be 045569 is now 45569
and dates 01-Jun-12 is now 1-Jun-12

The 0 in front is very important and I need to get this resolved.

Any help/ ideas will be greatly appreciated

 

[Jeff Meyers]

You can resolve these issues by formatting the cells with custom formats. Here are some examples: Col A is the default formats, Col B has been formatted per your specs, and Col D shows the custom formats to set / create.

Excel Workbook
	A	B	C	D
1	45569	045569		Format Cells, Custom, 000000
2	455691	455691
3	1	000001
4	22	000022
5	333	000333
6	4444	004444
7
8
9	1-Jun-12	01-Jun-12		Format Cells, Custom, dd-mmm-yy
10	9-Jun-12	09-Jun-12
11	30-Jun-12	30-Jun-12
12
13
14	--------------	--------------	------	-------------------------------------------------
15
Sheet1

Excel 2010

 

[keeegs]

Hi Jeff

I can format the cells on the current sheet and that works fine. but the problem is that i have to create a new sheet so I need to format that with code, but I'm not sure how to do that

 

[keeegs]

I used the code below on the cells that need to keep the 0

ActiveCell.Offset(1, 1).NumberFormat = "@"

 

[Jeff Meyers]

You could also try the following.

For your 6 digit number with leading zeros:

ActiveCell.Offset(1, 1).NumberFormat = "000000"

For your date format with two-digit date:

ActiveCell.Offset(1, 1).NumberFormat = "dd-mmm-yy"

 

[keeegs]

I tried that for the date but it still missed out the first 0. It used to work with excel 2003, but didn't in 2010.
And with the number, some times it might not be 6 digits

 

[Jeff Meyers]

I'm using Excel 2010 and both worked for me.

The format "000000" forces (should) your number to be 6 digits with leading zeros if needed (see examples in Post #2)

The format "dd-mmm-yy" forces (should) the day to be 2 digits by inserting a leading zero for days 1 thru 9 of each month.

I'm stumped that it's not working.

Here are some links to resources regarding setting up custom formats:

http://www.ozgrid.com/Excel/CustomFormats.htm

http://office.microsoft.com/en-us/excel-help/number-format-codes-HP005198679.aspx

Anyone else have any suggestions or guidance?