Are you doing this in a query?
Let's assume your TABLE is named Table1
Let's assume your FIELD is named Field1
Code:
SELECT Table1.field1, InStr([field1],"SHPG100") AS StartPos, InStr([startpos]+1,[field1],",") AS EndPos, Replace(Mid([field1],[startpos],[endpos]-[startpos]),":"," ") AS FinalString, Replace(Mid([field1],(InStr([field1],"SHPG100")),(InStr((InStr([field1],"SHPG100"))+1,[field1],","))-(InStr([field1],"SHPG100"))),":"," ") AS FinalStringSingleStatement
FROM Table1
WHERE (((Table1.field1) Like "*SHPG100*"));
I left some fields in there to show you how I built it bit by bit.