VBA Find Partial String in String Array and Output the Found String Array Value

Okay, so I am trying to do a sort of index match thing using VBA. What I am attempting to do is to use the prefix of a long number and try to find that exact prefix in a string array, and output that string array value. So this is what I have so far as a test:

Please Login or Register  to view this content.

So I can match the text exactly so if I put PREFIXB in cell A1 in this example, i will get the msg box saying "YES", but if I make it PREFIXB1231k4j3jj1kj32cj, it will display "NO". I want to get it so that PREFIXB will be displayed in the cell that I put the formula in. So if A1 = "PREFIX1AAA100CF" and cell B1 = "=ABC(A1)", cell B1 will display "PREFIX1AAA".

Now the thing is that these prefixes can have different lengths, but will never encompass the exact prefix of another. So if I had a prefix of: PRE1AB, I won't have a prefix of PRE1A.

Thanks for any help!

I am not really using s as an input parameter for this code. For some reason the code only works with (s as string) in there. I came up with my own solution to this problem (any input/advice would be appreciated); however, it isn't very elegant:

Please Login or Register  to view this content.