Take the 2-minute tour ×

I have a list of filenames in a spreadsheet in the form of "Smith, J. 010112.pdf". However, they're in the varying formats of "010112.pdf", "01.01.12.pdf", and "1.01.2012.pdf". How could I change these to one format of "010112.pdf"?

share|improve this question
add comment

6 Answers

up vote 26 down vote accepted

Personally I hate using VBA where worksheet functions will work, so I've worked out a way to do this with worksheet functions. Although you could cram this all into one cell, I've broken it out into a lot of independent steps in separate columns so you can see how it's working, step by step.

For simplicity I'm assuming your file name is in A1

B1 =LEN(A1)
determine the length of the filename

C1 =SUBSTITUTE(A1," ","")
replace spaces with nothing

D1 =LEN(C1)
see how long the string is if you replace spaces with nothing

E1 =B1-D1
determine how many spaces there are

replace the last space with a special character that can't occur in a file name

find the special character. Now we know where the last space is

H1 =LEFT(A1,G1-1)
peel off everything before the last space

I1 =MID(A1,G1+1,255)
peel off everything after the last space

J1 =FIND(".",I1)
find the first dot

K1 =FIND(".",I1,J1+1)
find the second dot

L1 =FIND(".",I1,K1+1)
find the third dot

M1 =MID(I1,1,J1-1)
find the first number

N1 =MID(I1,J1+1,K1-J1-1)
find the second number

O1 =MID(I1,K1+1,L1-K1-1)
find the third number

P1 =TEXT(M1,"00")
pad the first number

Q1 =TEXT(N1,"00")
pad the second number

R1 =TEXT(O1,"00")
pad the third number

S1 =IF(ISERR(K1),M1,P1&Q1&R1)
put the numbers together

T1 =H1&" "&S1&".pdf"
put it all together

It's kind of a mess because Excel hasn't added a single new string manipulation function in over 20 years, so things that should be easy (like "find last space") require severe trickery.

share|improve this answer
+1 for correctness, but whether it works or not, I find highly complicated formulas to be more cumbersome than VBA to me, especially if you've made a mistake. You yourself call it severe trickery, which should say something about the process. ;-) –  Gaffi Jun 28 '12 at 17:47
If anyone is curious what this looks like in a single-cell formula: pastebin.com/tUKcjyM1 –  Kip Jun 28 '12 at 18:17
I just threw up a bit in my mouth. I don't know whether to upvote or downvote this! (or flag as offensive) –  Jean-François Corbett Jun 28 '12 at 18:44
You're working harder than you need to since worksheet functions lack string processing functionality - if you don't want to use VBA this could be done with just a few Excel commands. a) Edit Replace twice:[.pdf]->[] then [* ]->['] b) Text to columns|Fixed Width|Next|Date:DMY c) Format cells: ddmmyy.p\df. (If you then want to convert the cell formats to text just copy and paste from office clipboard or notepad) –  lori_m Jun 29 '12 at 8:56
@JoelSpolsky I've added a slight variation on the method below - if it needs repeating a UDF would surely be the way to go. –  lori_m Jun 29 '12 at 15:00
show 4 more comments

Here's a screenshot of a simple four-step method based on Excel commands and formulas, as suggested in a comment to the answered post (with a few changes)...

enter image description here

share|improve this answer
Your date format in col D doesn't seem to to fit the MDY you have labeled in D2 (looks more like DMY, base on D7 to me), though the final output is still right. –  Gaffi Jun 29 '12 at 15:52
I did actually choose the MDY option for Column D but it is formatted d/m/yy by default because that's the default short date format for my European settings. –  lori_m Jun 29 '12 at 17:57
add comment

This function below works. I've assumed that the date is in ddmmyy format, but adjust as appropriate if it's mmddyy -- I can't tell from your example.

Function FormatThis(str As String) As String

    Dim strDate As String
    Dim iDateStart As Long
    Dim iDateEnd As Long
    Dim temp As Variant

    ' Pick out the date part
    iDateStart = GetFirstNumPosition(str, False)
    iDateEnd = GetFirstNumPosition(str, True)
    strDate = Mid(str, iDateStart, iDateEnd - iDateStart + 1)

    If InStr(strDate, ".") <> 0 Then
        ' Deal with the dot delimiters in the date
        temp = Split(strDate, ".")
        strDate = Format(DateSerial( _
            CInt(temp(2)), CInt(temp(1)), CInt(temp(0))), "ddmmyy")
        ' No dot delimiters... assume date is already formatted as ddmmyy
        ' Do nothing
    End If

    ' Piece it together
    FormatThis = Left(str, iDateStart - 1) _
        & strDate & Right(str, Len(str) - iDateEnd)
End Function

This uses the following helper function:

Function GetFirstNumPosition(str As String, startFromRight As Boolean) As Long
    Dim i As Long
    Dim startIndex As Long
    Dim endIndex As Long
    Dim indexStep As Integer

    If startFromRight Then
        startIndex = Len(str)
        endIndex = 1
        indexStep = -1
        startIndex = 1
        endIndex = Len(str)
        indexStep = 1
    End If

    For i = startIndex To endIndex Step indexStep
        If Mid(str, i, 1) Like "[0-9]" Then
            GetFirstNumPosition = i
            Exit For
        End If
    Next i
End Function

To test:

Sub tester()

    MsgBox FormatThis("Smith, J. 01.03.12.pdf")
    MsgBox FormatThis("Smith, J. 010312.pdf")
    MsgBox FormatThis("Smith, J. 1.03.12.pdf")
    MsgBox FormatThis("Smith, J. 1.3.12.pdf")

End Sub

They all return "Smith, J. 010312.pdf".

share|improve this answer
add comment

You don't need VBA. Start by replacing the "."s with nothing:


This will change the ".PDF" to "PDF", so let's put that back:

share|improve this answer
This works, but I still have a period after the FirstName initial. How would I get this to skip that period? –  CountDerp Jun 27 '12 at 21:59
Also, this doesn't take care of the issue with the "1.01.2012" –  CountDerp Jun 27 '12 at 22:30
Do you always have a space before the numeric date part? –  Joel Spolsky Jun 27 '12 at 22:52
Yes, there is a always a space –  CountDerp Jun 27 '12 at 23:56
add comment

Got awk? Get the data into a text file, and

awk -F'.' '{ if(/[0-9]+\.[0-9]+\.[0-9]+/) printf("%s., %02d%02d%02d.pdf\n", $1, $2, $3, length($4) > 2 ? substr($4,3,2) : $4); else print $0; }' your_text_file

Assuming the data are exactly as what you described, e.g.,

Smith, J. 010112.pdf
Mit, H. 01.02.12.pdf
Excel, M. 8.1.1989.pdf
Lec, X. 06.28.2012.pdf

share|improve this answer
add comment


As @Jean-FrançoisCorbett has mentioned, this does not work for "Smith, J. 1.01.12.pdf". Instead of reworking this completely, I'd recommend his solution!

Option Explicit

Function ExtractNumerals(Original As String) As String
'Pass everything up to and including ".pdf", then concatenate the result of this function with ".pdf". 
'This will not return the ".pdf" if passed, which is generally not my ideal solution, but it's a simpler form that still should get the job done. 
'If you have varying extensions, then look at the code of the test sub as a guide for how to compensate for the truncation this function creates.
Dim i As Integer
Dim bFoundFirstNum As Boolean

    For i = 1 To Len(Original)
        If IsNumeric(Mid(Original, i, 1)) Then
            bFoundFirstNum = True
            ExtractNumerals = ExtractNumerals & Mid(Original, i, 1)
        ElseIf Not bFoundFirstNum Then
            ExtractNumerals = ExtractNumerals & Mid(Original, i, 1)
        End If
    Next i

End Function

I used this as a testcase, which does not correctly cover all your examples:

Sub test()

MsgBox ExtractNumerals("Smith, J. 010112.pdf") & ".pdf"

End Sub
share|improve this answer
+1 Nice try, but fails for "Smith, J. 1.01.12.pdf". –  Jean-François Corbett Jun 28 '12 at 7:52
add comment

Your Answer


