How do I rename the beginning and end of a cell value?

Hi all,

I have a very unique task I am trying to simplify and its making my head spin .

I have a spread sheet that has thousands of names of a product we make. I collect the names from an online database and paste them in a spreadsheet to keep track of when they were manufactured. I need a formula or macro that will change the beginning and end of these names to the actual product name that our customer will understand without loosing the serial number embedded in the middle of the value. I will use generic numbers and text since this information is proprietary.

example:

Column C
2ABC1212Y to be changed to D2E3-1212 X
3ABC3434Y to be changed to D3E3-3434 X
7ABC5656Y to be changed to E3 QWERT-5656 X
2XYZ1212Y to be changed to D2E2 FGH IJ-1212 X
3XYZ3434Y to be changed to D3E2 FGH IJ-3434 X
7XYZ5656Y to be changed to M7 QWERT IJ-5656 X

Column D (has similar values, will need these to be part of the formula as well)
2ABC1212Y to be changed to D2E3-1212 X
3ABC3434Y to be changed to D3E3-3434 X
7ABC5656Y to be changed to E3 QWERT-5656 X
2XYZ1212Y to be changed to D2E2 FGH IJ-1212 X
3XYZ3434Y to be changed to D3E2 FGH IJ-3434 X
7XYZ5656Y to be changed to M7 QWERT IJ-5656 X


The first value is copied from our online database, the second is what goes in the spreadsheet (these names I will manually enter into the formula, use the given values above for the example i.e. D2E3 I will replace with our proprietary name later). basically I need the first four characters and the last character of the copied database value (i.e. 2XYZ1212Y) to change to our actual product name with spaces and a dash (i.e. D2E2 FGH IJ-1212 X), while keeping the four digit serial number in the middle. I would prefer a macro that I can run and look up all the values I pasted and replace them with their corresponding product name in the same cell. This way I don't have to go back and rename them all one at a time.

I have tried the find and replace function, but that only seems to efficiently replace the beginning value but struggles replacing the last character after the four digit serial number because ****Y in the find field could be any word with four letters before Y and it does not allow me to specify that these asterisks are actually numbers. Ignore this if it is confusing, its not that important. Let me know if you have any questions and thanks for your help!

Are the copied database values always the same length? You can use the strings functions len, mid, left, right etc. to extract certain parts. For example
if A1 is "2XYZ1212Y" you want the formula:
="D2E2 FGH IJ-" & mid(A1,5,4) & "X"

Probably the best way for you to do it is to use the find and replace function for the first parts then use the formula:
=left(A1,len(A1)-1) & " X"
to get the product you need, use autofill for a column to get all the values then copy and paste special -> values