Trying to find a value right of specific string with in text

[weilerda]

I'm new to access but am familiar with sql. I've imported a csv file into access and I have a column I need to pull specific information out. Here is an example of the text in the column.

(113:000034.310,SHPG100:000032.040,SBRD100:000034. 160,AGC100:000034.310)

(113:000031.950,SHPG100:000031.950,SBRD100:000031. 950,AGC100:000031.950)

(994010:000024.630,090003:000026.180,090001:000026 .180)

I need to find the value to the right of specific string.
For Example I want to find only the values for SHPG100

SHPG100 000032.040

SHPG100 000031.950

Any help would be greatly appreciated. Thanks in advance for your help.

[rpeare]

Are you doing this in a query?

Let's assume your TABLE is named Table1
Let's assume your FIELD is named Field1

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.

[weilerda]

Thanks! That Worked.