Formula to convert data to a different format in excel

Hi,
I have a seed lot numbered in the following format:

11D4000-1

where 11 is the year, D is the location, 4000 is the plot and -1 is the selection number.

It needs to be converted to the following format

SDF11F06-4000/001

where SDF is the new location code, 11 is the year, F06 is the generation, 4000 is the plot and 001 is the selection.

I have an excel table with 2 columns:
Lot.....................Generation
11D4000-1............... F6

I have thousands of lots to convert and will have thousands more in the future and will be working between the two formats. I would like to be able to set up a file so that all I'd need to do would be to paste the lots in and it would generate the code in the new format for me as often as I need to.

I will probably have a list of about 10 different location codes that could possibly be present in the original lot number.

Can anyone help?

Thanks,

Adrienne

RE: Formula to convert data...

Hello,

I need to know more about how D from 11D4000-1 become SDF from SDF11F06-4000/001. What I mean is to understand how your data are organized. If you can attach simple example file will be more helpfull.

Best regards.