Reformat Data from CSV file

I know that this is an insanely long formula, but I believe it will do what you're looking for:

=IF(LEN(IF(ISERR(FIND(".",A1)),A1,LEFT(A1,FIND(".",A1)-1)))=2,IF(ISERR(FIND(".",A1)),A1,LEFT(A1,FIND(".",A1)-1)),"0"&IF(ISERR(FIND(".",A1)),A1,LEFT(A1,FIND(".",A1)-1)))&"."&IF(RIGHT(MID(A1,FIND(".",A1)+1,2))<>".",MID(A1,FIND(".",A1)+1,2),"0"&LEFT(MID(A1,FIND(".",A1)+1,2),1))&"."&IF(LEN(RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1,".","*",LEN(A1)-LEN(SUBSTITUTE(A1,".",""))))))=4,RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1,".","*",LEN(A1)-LEN(SUBSTITUTE(A1,".",""))))),IF(LEN(RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1,".","*",LEN(A1)-LEN(SUBSTITUTE(A1,".",""))))))=3,"0"&RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1,".","*",LEN(A1)-LEN(SUBSTITUTE(A1,".",""))))),IF(LEN(RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1,".","*",LEN(A1)-LEN(SUBSTITUTE(A1,".",""))))))=2,"00"&RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1,".","*",LEN(A1)-LEN(SUBSTITUTE(A1,".",""))))),"000"&RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1,".","*",LEN(A1)-LEN(SUBSTITUTE(A1,".",""))))))))

It's probably long enough that you'd have to save this in one of the new Excel formats like .xlsx or .xlsm for it to save. If you have trouble with it let me know, I also have the formula broken out into several cells (or steps) if you need it that way.

There is a shorter (but still ugly) Excel formula using TEXT function for formatting:

=TEXT(MID(A1,1,FIND(".",A1)-1),"00") &"."& TEXT(MID(A1,FIND(".",A1)+1,FIND(".",A1,FIND(".",A1)+1)-FIND(".",A1)-1),"00") &"."& TEXT(RIGHT(A1,LEN(A1)-FIND(".",A1,FIND(".",A1)+1)),"0000")

I would like to point out there are other methods of solving the same problem. If you have lots of big csv files you might consider preprocessing before importing into Excel. For example, doing the same job in awk is trivial:

BEGIN {
    while(getline < "e.csv") {
        split($1,a,".");
        printf("%02d.%02d.%04d\n",a[1],a[2],a[3]);
    }
}

Gawk works well with Windows. After installing, you can run it on the Windows or PowerShell command line as .\awk -f x.awk where x.awk is the above code in a text file.