Problem with Excel Replacing leading zeros

[chris-t]

Hi All,

I am currently trying to format an Excel spreadsheet for our schools new intake to enter automatically into active dir, cache pilot etc.

The problem is our usernames are created using the pupils initials and DOB, example John Smith born on 2nd of February would be JS0202. So i have a formula telling excel to take the first letter of each name then take the date of birth and combine them to create the username.

I have the DOB cells formatted so they are always four characters to stop excel taking out the leading zero, the problem is the leading zero is removed and replaced with a 'theoretical' 0 rather than an actual character.

This then means when i go to combine the cells it only takes the 3 'actual' characters meaning what should be JS0202 becomes JS202.

 

Any help would be greatly appreciated as i only have a couple of days before the new term.

 

Thanks in advance,

 

Chris

[mb2k01]

What are the DOB cells formatted as?

I think I had something similar before and had to format the cells as text instead of number?

[chris-t]

Hi thanks for the quick reply,

I have tried them fromatted as number and as text. Thought formatting them as text would help with it being alpha numeric but still removes the leading zero. I've just had a look through the sheet and it's only 30 or so kids that create this problem so will just add the zeros manually. Would just be interested for future reference if anyone has had or knows how to solve this problem.

[srochford]

Assuming you've got something like this in columns A-C

A    B     C
John Smith 2 Feb 1998

then in column D you would put:

 

=LEFT(A1,1)&LEFT(B1,1)&TEXT(DAY(C1),"00")&TEXT(MONTH(C1),"00")

 

this will extract first letter of first name, first letter of surname, day formatted with leading zero and then month formatted with leading zero.

[chris-t]

hi thanks for that, wasn't exactly how we had it but have tweaked the formula a bit and it works now. It was just the last part of the formula i was missing. Saved me a lot of time there!