Oh, and Leading Zeroes don’t exist in excel (if you can see them, that’s because someone’s set the number format as 00000000 – press Ctrl 1 to see it)…
You’ll need to work from the right to get it into the right format. Take the first 4 digits on the right to get the year, then the next two to establish the month, then whatever’s left to get the first digit. Not got the time to work out the formulae, but I’d do it with a load of columns (ie, one for year, one for month etc.), using LEFT, RIGHT, MID and LEN functions.
Left takes a chunk off the left, Right- I think you can guess, and Mid takes a chunk out the middle.
Len gives you the length of whatever’s in the cell so you can still make things work if the length of your string varies.
An alternate method would be to concatenate a 9 (all months should be at most 31 days long) onto the front of every leading zero date, and then strip that out later. This would mean you could just take the left two digits, the middle 2 digits, and the last 4 digits using pretty simple formulae.
You’d get your 9 with something like =if(“(Len(A2)<8”),(concatenate(“9”,A2),) if your first date is in A2. Copy and paste values again to strip out the formulae…
You’d then do another if statement to pull out the right hand digit if the number starts with a 9…