+ Reply to Thread
Results 1 to 7 of 7

Format data with leading zeros

  1. #1
    Registered User
    Join Date
    05-20-2015
    Location
    seattle
    MS-Off Ver
    2013
    Posts
    15

    Format data with leading zeros

    Hi,

    I'm trying to add the text S- to the beginning of the cell contents at the beginning of a birthday formatted as mm/dd/yyyy. I have the column formatted with the leading zeros but when I use the formula ="S-" & A1 it drops the leading zeros. I've tried all kinds of formatting options and they haven't worked.

    Here is an example.
    Formatted correctly:
    S-10212002 (birthday is Oct 21 ,2002)

    Formatted wrong:
    S-6222004 (birthday is Jun 22, 2004)

    Thank you,

    Linda

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 Version 2211, rarely 2007
    Posts
    12,807

    Re: Format data with leading zeros

    Try this formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  3. #3
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Format data with leading zeros

    Two ways:

    A
    B
    C
    D
    1
    S-08/16/2016
    Format of A1: \S-mm/dd/yyyy
    2
    8/16/16
    S-08162016 B2: ="S-" & TEXT(A2, "mmddyyyy")


    A1 is entered as a date and the formatting does the rest.

    A2 is entered as a date and the formula in B2 does the rest.
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Registered User
    Join Date
    05-20-2015
    Location
    seattle
    MS-Off Ver
    2013
    Posts
    15

    Re: Format data with leading zeros

    To clarify I'm starting with the 8 character birthday like 01051999 and trying to add the S- to the beginning of that. I can't seem to get the above formulas to make that work.

  5. #5
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Format data with leading zeros

    Maybe this...

    A1 = 01051999

    ="S-"&TEXT(A1,"00000000")
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  6. #6
    Registered User
    Join Date
    05-20-2015
    Location
    seattle
    MS-Off Ver
    2013
    Posts
    15

    Re: Format data with leading zeros

    That worked! Thank you!

  7. #7
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Format data with leading zeros

    You're welcome. Thanks for the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How do i add leading zeros in hh:mm:ss format?
    By anniea88 in forum Excel General
    Replies: 9
    Last Post: 10-22-2021, 03:34 AM
  2. Leading Zeros in Numeric Format
    By zakel in forum Excel General
    Replies: 2
    Last Post: 04-12-2012, 04:45 PM
  3. Leading Zeros lost in csv format
    By Scotsman89 in forum Excel General
    Replies: 18
    Last Post: 04-05-2012, 04:04 AM
  4. Format for Leading Zeros
    By DavidW in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-19-2006, 04:01 PM
  5. Replies: 4
    Last Post: 11-17-2005, 10:20 AM
  6. Format a cell to keep leading zeros.
    By Shadyhosta in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 07-27-2005, 11:37 AM
  7. Replies: 1
    Last Post: 05-04-2005, 02:06 PM
  8. leading zeros in text format
    By BigBrook in forum Excel General
    Replies: 3
    Last Post: 02-04-2005, 08:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1