Add two new columns to the right of the column with all the codes (or two rows under if in row format, and see rows for columns in the rest of the solution).
Assuming the first code is in cell A1, in cell B1 put:
=LEFT(A1,7) & "0" & RIGHT(A1,3)
Then drag the corner of A1 down to auto fill for the other codes.
Lastly, select all the new codes, cope and 'paste special' into the C column and paste values only. Then delete columns A and B.
The 'LEFT' function looks for the number of characters specified in a string from the left of the string, in a specified cell. 'RIGHT' does the same, but from the right. The code [ & "0" & ] allows text to be inserted in formulae.
You want ABC-00-101 to say ABCDEF
=LEFT(A1,3) & "DEF" & MID(A1,4,4) & "A" & RIGHT(A1,3)
The 'MID' is for extracting string data from the middle (-00- in this example). A1 is the source cell, 4 = the 4th character from the left and 4 for the number of characters.
© Camieabz 2002-2011