Problem with Excel Replacing leading zeros
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,