searching in a string for text cleanly

I need to find the row of a sheet that contains a specific text within a text string.
But the problem comes in the construct of the text string.
I have a number of rows, each having a reference column 'M' In the rows of column 'M' are a number of text strings:
"R17, R50, R109, R111"
"C2, C12, C14"
"C1, C3, C5, C15"
for example.
My problem is trying to find C1. If I use find or INSTR on "C1" I could get the row for "C12", If I search for "C1," then I can get the correct row, but only if C1 is in a list with other numbers after it. If it is the only entry on that row "C1" the search will fail.
How can I search for a text C1 without pulling up C11 but doesn't fail on a string like "C2, C3, C1"
Can anyone help with a clean way to search these strings?

One way.
Select he range first.

Please Login or Register  to view this content.

But this will give me the first C1 ie C12 it doesn't distinguish between C1, C11, C10 or C19.
I need to be able to search for C1 not C10. The range will be M9:M65535 every time. But the content changes with every application. The M column is not filtered in order and can have blanks also. I have only shown 3 here but there could be 10,000 rows. each with any number of parts listed with comma separators.

This is a custom function, e.g use as =x(A1,"C1"). By the way, always best to try code before declaring that it won't work...

Please Login or Register  to view this content.

Originally Posted by tmd_63

there could be 10,000 rows.

And if you want only first match then

Please Login or Register  to view this content.

Sorry. I couldn't get it to work as it is a range from M9 to M??? and I cannot select the range first. This is to be run in code.
Also, the company restricts macros and VBScripts for security reasons.

Sorry. I couldn't get it to work as it is a range from M9 to M??? and I cannot select the range first. This is to be run in code.
Also, the company restricts macros and VBScripts for security reasons.

Then I don't know how to do it without vba... perhaps very complex formula I hope.

So why post a question in a VBA forum?

I tried this. It appears to work if I am on the same sheet, but not if I am in a different sheet.

Please Login or Register  to view this content.

I get a run time error 1004
Select method of Range class failed.
I need to run the code from any sheet but the search is inside sheet2 (for this part).

Perfect thanks. That got it that time.