You can only do half of what you want with formulas... while you can put this formula in cell B1 and copy it down as needed...

="CC"&RIGHT(A1,2)

you cannot put a formula into the cells of Column A in order to truncate the values there (a cell can contain either a value or a formula, but not both). There is a VB macro solution, but I think the easiest thing would be to leave Column A as is and put the two values you want from it in Columns B and C. If you do that, here are the formulas to use...

B1: =LEFT(A1,LEN(A1)-3)

C1: ="CC"&RIGHT(A1,2)

If you cannot do this for some reason, write back and we will give you the VB macro code to use instead.