Leading Zeros with a twist.
 

I am trying to build a match field. It will consist of the first three
letters of the persons last name + the first 2 letters of the persons first
name + their birthdate.

I have resolved to problem of the birthdates showing up without the leading
zeros in the first position for the months 01-09. The problem arises when I
concatenate the three columns into one.

In my example it would be Smith William 01011952. The individual columns
appear fine as SMI WI 01011952. The data should appears as SMIWI01011952
but Excel will suppress the leading zero on month and will appear in the
concatenated column as SMIWI1011952.

I am wondering why MS has not addressed this problem. There are almost too
many inquiries about this problem and there does not appear to be a clean fix
for it. The data always needs to be manipulated to get around this problem.
This presents a problem especially when going from one platform to another as
well as in my example.

You could concatenate your date using =text()

=a1&b1&text(c1,"mmddyyyy")



Steve D wrote:

I am trying to build a match field. It will consist of the first three
letters of the persons last name + the first 2 letters of the persons first
name + their birthdate.

I have resolved to problem of the birthdates showing up without the leading
zeros in the first position for the months 01-09. The problem arises when I
concatenate the three columns into one.

In my example it would be Smith William 01011952. The individual columns
appear fine as SMI WI 01011952. The data should appears as SMIWI01011952
but Excel will suppress the leading zero on month and will appear in the
concatenated column as SMIWI1011952.

I am wondering why MS has not addressed this problem. There are almost too
many inquiries about this problem and there does not appear to be a clean fix
for it. The data always needs to be manipulated to get around this problem.
This presents a problem especially when going from one platform to another as
well as in my example.

--

Dave Peterson