add zero to cells with cells with less that 8 characters

[poppafry]

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)


=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.

[Kenneth Hobson]

Welcome to the forum! If data in A2 and down:

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

[snb]

=TEXT(RIGHT("00"&A2;8);"00\/00\/0000")

[Admin]

For English version, replace ; with ,