Complicated date format.

Hi all.

I'm working from an irritating import that seems to be protected in every way possible. The date is supplied as 17.07.1985
So far I've been unable to manipulate it in any way shape or form.

I need it to be 1985/07/17 or 1985.07.17 - I just need the year first.

I've tried reversing the characters but that of course doesn't work as my version does a character at a time.

I've used a replace function on the cell already to replace the dots with slashes but this is not required and can be removed.

Can anyone suggest a way please? In java I would use a substring and seperate the text from the "/" etc but I don't know how to do it in excel.

Could anyone help?

Many thanks!

Steve

Hi Russell, I did originally want it to have "/"'s in but thats no longer a requirement.

I've tried to format the cells and still no joy. the bizarre thing is when I copy and paste only the values into another cell...I still can't format it or custom format it etc. it's set in stone. I've not come across this issue before.

Just to make things clearer.

I have: 29.12.2012

I then used =REPLACE(REPLACE(M16,3,1,"/"),6,1,"/") to get 29/12/2012.

From this point on I can no longer manipulate the format of the cell to make the year first.

I'm now working ona very ugly solution but having issues.

=RIGHT(LEFT(H65,7)&RIGHT(LEFT(H65,8))&RIGHT(LEFT(H65,9))&RIGHT(LEFT(H65,10))&RIGHT(LEFT(H65,6))&RIGHT(LEFT(H65,4))&RIGHT(LEFT(H65,5))&RIGHT(LEFT(H65,3))&RIGHT(LEFT(H65,1))&RIGHT(LEFT(H65,2)),LEN(H65))

this is generating .03.22

I'm getting there. I think there is a lot of white space at the end of the year so may need to trim first?

from left to right

data:2 2 . 0 3 . 1 9 8 2
char:1 2 3 4 5 6 7 8 9 10

sequence reqd:
7 8 9 10 | 6 | 4 5 | 3 | 1 2

SUCCESS!!!

=TRIM(H64)

=RIGHT(LEFT(I64,7)&RIGHT(LEFT(I64,8))&RIGHT(LEFT(I64,9))&RIGHT(LEFT(I64,10))&RIGHT(LEFT(I64,6))&RIGHT(LEFT(I64,4))&RIGHT(LEFT(I64,5))&RIGHT(LEFT(I64,3))&RIGHT(LEFT(I64,1))&RIGHT(LEFT(I64,2)),LEN(I64))

=REPLACE(REPLACE(J64,5,1,"/"),8,1,"/")

Gives me the right result!!!

Now...can I put all of that in one cell?

Hi Martin,

Thanks for having a peek but that didn't work either

I wish I knew but thank you for the help!!!