Finding Text within a String (non-conforming)

[CodyMonster]

Everyone.. I have a string where I need to pull out a specific code/number, its begining three letters will not change, but its position and the length of the text might change.

Example:

<TABLE style="WIDTH: 291pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=388 x:str><COLGROUP><COL style="WIDTH: 291pt; mso-width-source: userset; mso-width-alt: 14189" width=388><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 291pt; HEIGHT: 12.75pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" id=td_post_2578426 height=17 width=388>DMRG-7983, XYZ CTP09/176, D3190, TORGOVY BRIDGE

I need to pull out the CTP09/176 the CTP prefix never changes but where its placed can be different, and the number of charters might change. I'm using the formula which works for 9 characters but not if its more or less

=TRIM(RIGHT(SUBSTITUTE(MID(M253,FIND("CTP",M253),9)," ",REPT(" ",255)),255))

In this case its CTP10/22

</TD></TR></TBODY></TABLE>
DMRG-7990, ABC Corp, CTP10/22, D4050, ATLANTIC

How do I get this to work so I pick up to the Right of CTP but left of the comma.

Thanks for you help!!!

 

[VoG]

Try

=MID(A1,FIND("CTP",A1),FIND(",",SUBSTITUTE(A1,",","~",1))-FIND("CTP",A1))

 

[bosco_yip]

Try

=LEFT(MID(A1,FIND("CTP",A1),255),FIND(",",MID(A1,FIND("CTP",A1),255))-1)

 

[CodyMonster]

Thanks.. .that works if the string has the CTP after the first comma.. but it will and frequestly changes positions..

Your formula works for this..
91908-CPL, CTP09/174,D3873, CTC10/1,D143

But not for this..
91995-CPL CTP10/50, D143

 

[CodyMonster]

bosco_yip - It worked!! Thanks