[mso] Re: Numbers & Leading Zeros in Excel

g

 Finally an Excel question I can answer.

 if in B3 you have a month value
 if in C3 you have a day value
 if in D3 you have a year value

 =3DIF(LEN(B3) =3D 1,"0" & B3, B3)&IF(LEN(C3) =3D 1,"0" & C3, C3)&D3

 Will concatenate a string like you require. It doesn't seem to matter =
the formatting of the cell or if the cell value contains a leading 0.

-Anthony

-----Original Message-----
From: Glenda Wells [mailto:gwells@xxxxxxxxxxx]
Sent: Thursday, March 27, 2003 9:23 AM
To: 'mso@xxxxxxxxxxxxx'
Subject: [mso] Numbers & Leading Zeros in Excel


Hi All.
=20
I can't get this to work and I'm losing my hair....
=20
Three columns
=20
month   day   year
3           2       87
12         7       85
4          14      85
=20
what i see
month   day   year
03         02     1987
12         07     1985
04         14     1985
=20
desired result
mmddyyyy
=20
date
03021987
12071985
04149185
=20
what i am getting
321987
1271985
4141985
=20
Those %&*$# leading zeros are killing me.  I've formatted the cells with =
a
custom, as text, as number...HELPPP!
=20
g
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or 
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, send an email to 
mso-request@xxxxxxxxxxxxx with the word "unsubscribe" (without the quotes) in 
the subject line.

Or, visit the group's homepage and use the dropdown menu.  This will also allow 
you to change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to use the files section for sharing files with the group, send a 
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with 
instructions.  Once you are a member of the files group, you can go here to 
upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************

Other related posts: