Results 1 to 4 of 4

Thread: add zero to cells with cells with less that 8 characters

  1. #1
    Junior Member
    Join Date
    Nov 2017
    Posts
    1
    Rep Power
    0

    add zero to cells with cells with less that 8 characters

    I receive a csv file from a client that I have to massage in order to import into my system.
    The issue is:
    The file has a column with date of birth with a format of mmddyyyy. I need the format to be mm/dd/yyyy

    I am trying to use the formula below (inserted via vba)

    HTML Code:
    =LEFT(E2, 2)&" / "&MID(E2, 3, 2) & " / "&RIGHT(E2, 4)
    If month is 2 digits it works fine (see below)
    10222017 returns 10 / 22 / 2017


    If month is digit it does not work (see below)
    9022017 returns 90 / 22 / 2017

    My thought was to add a leading 0 to the cells where the month is 1 digit....Not sure how to do this either.
    I have tried multiple formats with no success.

    Is there an easier way to convert this?
    How can I add a leading zero based on the number of characters?


    Thanks in advance for your help.

  2. #2
    Member
    Join Date
    May 2013
    Posts
    31
    Rep Power
    0
    Welcome to the forum! If data in A2 and down:
    Code:
    Sub cDates()
      Dim r As Range, c As Range
      Set r = Range("A2", Cells(Rows.Count, "A").End(xlUp))
      For Each c In r
        c.Value = DateSerial(Right(c, 4), Left(c, Len(c) - 6), Left(Right(c, 6), 2))
        c.NumberFormat = "mm/dd/yyyy"
      Next c
    End Sub

  3. #3
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    12
    =TEXT(RIGHT("00"&A2;8);"00\/00\/0000")

  4. #4
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    For English version, replace ; with ,
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

Similar Threads

  1. Replies: 2
    Last Post: 08-31-2013, 03:54 PM
  2. Replies: 4
    Last Post: 08-20-2013, 06:28 PM
  3. Replies: 13
    Last Post: 06-10-2013, 09:05 AM
  4. automatic Add date in cells
    By Ryan_Bernal in forum Excel Help
    Replies: 1
    Last Post: 01-23-2013, 02:50 PM
  5. Replies: 2
    Last Post: 09-24-2012, 11:19 PM

Posting Permissions

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