Your formulas worked to perfection, Ken. The only nitpick was that I
had to change the picture of the date at the end of the 2nd formula to
"mm/dd/yy", very minor thing. Clever the way you had to handle the
double-quotes with their character representation. Many thanks, Ed


"Ken Wright" wrote:

And assuming your helper column is in Column B, with your date to go in
say
C1 use this in another helper column

=SUBSTITUTE(B2,MID(B2,FIND("%",SUBSTITUTE(B2,CHAR( 34),"%",5))+1,8),TEXT($C$1,"dd/mm/yy"))

Also note that the first formula I gave you had a constant hardwired
into it
which was at odds with me describing the constant being in A1 -
apologies
for that - it should have read

=LEFT(A2,17)&TEXT((--MID(A2,18,7)+$A$1),"0000000")&MID(A2,25,LEN(A2))

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------