Excel formula to rearrange the cell contents ENG/DEF/001/14 to ENG/DEF/14/001

[HelenL]

Excel formula to rearrange the cell contents ENG/DEF/001/14 to ENG/DEF/14/001

Help appreciated - completely lost!

 

[Rick Rothstein]

1) single cell A1 Row 2 but will pull the formula down through the rest
2) yes
3) yes
4) yes
5)yes this is the year
6) yes

Since you answered yes to all my questions, this formula should also work...

=LEFT(REPLACE(A1,8,0,RIGHT(A1,3)),14)