Extract variable length text from a string

[gpsurf]

I have been forking on solving an problem extracting a specific portion of a text from a string.

Here is a few examples of the text format that is in column A:

[TABLE="width: 233"]
<COLGROUP><COL style="WIDTH: 233pt; mso-width-source: userset; mso-width-alt: 11337" width=310><TBODY>[TR]
[TD="class: xl77, width: 310, bgcolor: transparent"]Invoice: PST-53012-IN:CID:XYZ10010819: id Month (

[TABLE="width: 233"]
<COLGROUP><COL style="WIDTH: 233pt; mso-width-source: userset; mso-width-alt: 11337" width=310><TBODY>[TR]
[TD="class: xl79, width: 310, bgcolor: transparent"]Payment: 100272-CR:CID:XYZ192:

[TABLE="width: 311"]
<TBODY>[TR]
[TD]Payment: 19313-CR:CID:XYZ10014254:
[TABLE="width: 233"]
<COLGROUP><COL style="WIDTH: 233pt; mso-width-source: userset; mso-width-alt: 11337" width=310><TBODY>[TR]
[TD="class: xl79, width: 310, bgcolor: transparent"]Payment: 3660-CR:CID:xyz10015999:


[/TD]
[/TR]
</TBODY>[/TABLE]
I need to extract the text (invoice #) between the colons that begins with xyz. The string size is variable and the invoice may be in capital or lower case. Also the invoice number is not the always the same length. One consistent variable is that before the Invoice # always has the text “CID:” and it ends with a colon.<o></o>
I am looking for a formula solution instead of a VBA solution.<o></o>
<o> </o>
I have tried this solution but it just does not give me all the values I need =+MID(A10,FIND("XYZ",A10),11)<o></o>
<o> </o>
Any ideas you have would be greatly appreciated.<o></o>
<o> </o>
Thanks<o></o>
George<o></o>


</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]

 

[Robert Mika]

One way:
[TABLE="width: 519"]
<tbody>[TR]
[TD]PST-53012-IN:CID:XYZ10010819: id Month ([/TD]
[TD]XYZ10010819[/TD]
[/TR]
[TR]
[TD]Payment: 100272-CR:CID:XYZ192:[/TD]
[TD]XYZ192[/TD]
[/TR]
[TR]
[TD]Payment: 3660-CR:CID:xyz10015999:[/TD]
[TD]xyz10015999[/TD]
[/TR]
[TR]
[TD]Payment: 19313-CR:CID:XYZ10014254:[/TD]
[TD]XYZ10014254[/TD]
[/TR]
[TR]
[TD]Payment: 19313-CR:fdfadf:fdf:CID:XYZ10014254 :[/TD]
[TD]XYZ10014254[/TD]
[/TR]
[TR]
[TD]dfasd;fl:fadsfasd:908709804:CID:XYZ:fadfafdf;[/TD]
[TD]XYZ[/TD]
[/TR]
</tbody>[/TABLE]

=TRIM(LEFT(MID(A1,FIND("CID",A1)+4,LEN(A1)),FIND(":",MID(A1,FIND("CID",A1)+4,LEN(A1)))-1))

 

[gpsurf]

That worked, thank you! I just have to figure out how it works I haven't combined these functions together like this before.

 

[bosco_yip]

A bit shorter formula :

=MID(A1,FIND("CID",A1)+4,FIND(":",A1,FIND("CID",A1)+4)-FIND("CID",A1)-4)

Regards