Customized Predecessor and Successor fields

I'm looking for a macro/vba help/code that does the following:
1. Parses the MSP included Predecessor/successor field separating at commas (",")
2. Look up the specific number in ID column
3. And return a customized field for the corresponding task
4. then Concatenate all these customized fields into a different text columns

so basically and example:

Predecessor column looks like: 566, 234, 255SS
corresponding customized field has values (for these individual tasks): MCM100; AA0001; GHJ1234 --> these are hand input and are have no rhyme or reason.
So I'm looking for the final output to be MCM100, AA0001, GHJ1234SS

Any idea how to do this?