VBA substrings in string

Hi all. I have searched high and low but I can't get to the bottom of this one. Hopefully someone here can point me in the general direction?

I have strings that could look like "N01N03N10N35" or "N 1N 7N12" or "N1N6N20"

Each substring will always start with a capital "N" followed by either two numbers, a space and a number, or no space and a number/s.

I need to do the following operations on this string:
1. Extract each substring text from the string
2. Find the start position of each substring (possibly using instr?)
3. Get the length of each substring

I have tried instr but I don't think it is possible to use a wildcard. I have had some success with split but can only extract the numbers following.

This my attempt at extracting the substring:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Any help is hugely appreciated!

Thanks
Mike

Please Login or Register  to view this content.

?

Try

Please Login or Register  to view this content.

Can't thank you both enough! Both solutions are exactly what I was looking for...

Originally Posted by jindon

Try

Please Login or Register  to view this content.

Hi jindon,

I like this code but I get an error "Constant expression required" when I integrate to my code. The string named NoteText is a variable not a constant. Is there a way I can adapt the code for this?

Thanks
Mike

Please Login or Register  to view this content.

Need to see full code.

Originally Posted by jindon

Need to see full code.

Thank you for the reply, jindon. I think I have just figured it out:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

What's this? It has no such method.

Originally Posted by jindon

Please Login or Register  to view this content.

What's this? It has no such method.

In your code I thought "test" referenced the name of the sub, so I change it to the name of my sub. The code works with either "test" or TableTest" though???

No, it's a test method for Regular Expression.

Is must be

Please Login or Register  to view this content.

I've changed my code and will learn how the expression works.
Your help is greatly appreciated.