Advanced matching? Regex?

[SirMille]

Hi,

I need to extract data a substring form a string matching tree letter followed by a space and then three numbers; XYZ 123. But sometimes it can be surrounded by other text, sometimes not. Some times it doesn't even exist on the row.

What's the best solution of matching this? I was thinking regex but that isn't available huh?

Maybe the best solution would be to add it through a macro/module somehow? (But how about Mac OS X support then?)

Any tips, suggestions or comments are welcome.

Thank you.

 

[Rick Rothstein]

Perhaps you can use the UDF (user defined function) that I posted on my mini-blog article here...

Find a text substring that matches a given "pattern"

Assuming all upper case letters, the pattern that you would want is this...

[A-Z][A-Z][A-Z] ###

If the letters could be upper or lower case, then this instead...

[A-Za-z][A-Za-z][A-Za-z] ###


HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use GetPattern just like it was a built-in Excel function. For example, assuming cell A1 contained the text you were searching in...

=GetPattern(A1,"[A-Z][A-Z][A-Z] ###")