Worksheet.Find - A way to Find a STRING with an unknown group of numbers within a cell

I using this method to find a text string within a selected cell. The string may or may not exist and I have further error handling for those instances.

WorksheetFunction.Find("This is a test string", Selection.Value)

What I'd like to do is find that text string followed by an unknown group of 6 characters followed by a quotation " mark.

Note: (the unknown groupl of characters will actually be numbers, but that's not important unless it makes it easier that they are numbers) followed by

As an example, the selected cell contains the following:

now is the time for all good men to come to the this is a test123456" of their country

The "123456" will be the unknown characters (numbers).
So the code should be searching for "this is a test######" that is followed by a quotation mark.

I hope this makes sense.

Thanks.
Paul

view results in intermediate window

Please Login or Register  to view this content.

Thank you for this suggestion. I think I can make this work... Of course I would replace the quoted item in the "lngstr =" line to "selection.value" based on a little testing I've done with your suggestion so far. Seems to work.

This is really good.

Thanks again,
Paul

You cannot use wildcards in the Find function, but you can in the Search function

Please Login or Register  to view this content.

Here is a UDF (user defined function) that you can use...

Please Login or Register  to view this content.

Simply pass it the text to search in (either a cell reference or a quoted text string) and the base text (the part without the numbers) (again either as a cell reference or a quoted text string) and the function will return the base text along with the trailing six digits.

This is really good. I think this is what I'm looking for. My initial test is looking positive so far using a simple sample cell string. I was having trouble when there were multiple matching items in the same cell. Once I find the matching criteria, I'm adding a specified string before the quotation at the end. This means the next time I run the code, that should no longer be a match. However, if there was a second match in the same cell, then that should now be identified. I'm going to give this a go.

Thanks again.
Paul

Not sure who's message you were responding to (when more than one person has offered help, you should mention the name of who you are responding to in your message). I have modified my UDF to return all of the text strings that start with your "find me" text followed by six and only six digits. The list is comma/space delimited if there is more than one instance in the "search me" text. Note that I used the same function name so you have to remove my first UDF from your project so that VBA can find the following one.

Please Login or Register  to view this content.

Sorry Rick. My reply was to Fluff13. I was able to apply that suggestion to my code and seems to work well. I didn't get a chance to try out your UDF. However, I know you from many years of Excel contribution going back to the News Group days. Great help to many in the past and you continue. In any case, thank you for your suggested solution. I may still try to look into your solution and apply the methods in other code.

Thanks again.
Paul

Glad to help & thanks for the feedback.