Number and Date problems

keeegs

Board Regular
Joined
Sep 25, 2011
Messages
145
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
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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
ABCD
145569045569Format Cells, Custom, 000000
2455691455691
31000001
422000022
5333000333
64444004444
7
8
91-Jun-1201-Jun-12Format Cells, Custom, dd-mmm-yy
109-Jun-1209-Jun-12
1130-Jun-1230-Jun-12
12
13
14-----------------------------------------------------------------------------------
15
Sheet1
Excel 2010
 
Upvote 0
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
 
Upvote 0
I used the code below on the cells that need to keep the 0

Code:
ActiveCell.Offset(1, 1).NumberFormat = "@"
 
Upvote 0
You could also try the following.

For your 6 digit number with leading zeros:
Code:
ActiveCell.Offset(1, 1).NumberFormat = "000000"

For your date format with two-digit date:
Code:
ActiveCell.Offset(1, 1).NumberFormat = "dd-mmm-yy"
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,211,841
Messages
6,104,302
Members
447,901
Latest member
boy3hc2004

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