+ Post New Thread
Results 1 to 5 of 5
Office Software Thread, Problem with Excel Replacing leading zeros in Technical; Hi All, I am currently trying to format an Excel spreadsheet for our schools new intake to enter automatically into ...
  1. #1

    Join Date
    Jul 2009
    Location
    Middlesbrough
    Posts
    32
    Thank Post
    6
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    Problem with Excel Replacing leading zeros

    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

  2. #2
    mb2k01's Avatar
    Join Date
    Jan 2007
    Posts
    1,128
    Thank Post
    189
    Thanked 227 Times in 193 Posts
    Rep Power
    92
    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?

  3. #3

    Join Date
    Jul 2009
    Location
    Middlesbrough
    Posts
    32
    Thank Post
    6
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    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.

  4. #4

    Join Date
    Aug 2005
    Location
    London
    Posts
    3,154
    Thank Post
    114
    Thanked 527 Times in 450 Posts
    Blog Entries
    2
    Rep Power
    123
    Assuming you've got something like this in columns A-C
    Code:
    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(MON TH(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.

  5. 2 Thanks to srochford:

    chris-t (28th August 2009), enjay (28th August 2009)

  6. #5

    Join Date
    Jul 2009
    Location
    Middlesbrough
    Posts
    32
    Thank Post
    6
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    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!

SHARE:
+ Post New Thread

Similar Threads

  1. Strange Excel Problem
    By Jon in forum Windows
    Replies: 14
    Last Post: 14th July 2011, 04:55 PM
  2. Excel sum problem
    By wesleyw in forum How do you do....it?
    Replies: 8
    Last Post: 29th August 2009, 11:23 AM
  3. excel lookup problem, can someone help me please?
    By RabbieBurns in forum Windows
    Replies: 3
    Last Post: 2nd July 2008, 12:51 PM
  4. Excel Problem
    By TechSupp in forum General Chat
    Replies: 0
    Last Post: 2nd November 2007, 10:08 PM
  5. Bizzare Excel Problem
    By Norphy in forum Windows
    Replies: 3
    Last Post: 17th January 2006, 10:19 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •