Excel - pattern matching in cells

[Ampa]

Can't believe that there isn't a really simple way of doing this, but then I've been surprised by Excel before…

I have a invoice numbers which all have the format three letters followed by three digits (eg. WHS005).
If the data doesn't match this pattern it should be rejected.

I've Googled, read the help files and failed spectacularly.

Anyone got the answer?

Thanks, Ampa

[Renegade]

You're likely best off looking into VBA and macros. Excel functions are limited and don't really have any good text processing. The lack of regular expression support in Office is quite frankly astounding.

[Target]

what is it you want to do, and how are you trying to do it?

is the format of the invoice numbers consistent, ie 3 letters and 3 numbers?

this quick formula will give you a flag

=IF(AND(TYPE(LEFT(A14,3))=2,ISNUMBER(VALUE(RIGHT(A14,3)))),"","XX")

Note that it doesn't properly test the alpha segment, ie WHS and WH2 both evaluate as text in my quick test

Unless you have a consistent naming convention you may need to test each character

and string length may be another test...

[moonwatcher]

If you don't mind using UDFs (User Defined Excel Functions), regular expression matching is also possible:

Public Function RegExMatch(ByVal vsStringIn As String, ByVal vsPattern As String) As Boolean
    Dim objRegEx As Object
    Set objRegEx = CreateObject("VBscript.regexp")

    objRegEx.Global = True
    objRegEx.MultiLine = True
    objRegEx.pattern = vsPattern
    
    RegExMatch = objRegEx.Test(vsStringIn)
    
    Set objRegEx = Nothing
End Function

The worksheet formula would then look like this:

=RegExMatch(A1, "[A-Z]{3}\d{3}")

More about UDFs: How to Create Custom User Defined Excel Functions,  Excel Custom Functions using VBA

[kfitting]

Basic tutorial on using regular expressions in Excel VBA: http://www.tmehta.com/regexp/index.htm
Regular Expression guide (many languages): http://www.regular-expressions.info/
List of articles showing some other ways of using Regex in Excel VBA: http://excelicious.wordpress.com/category/regular-expressions/

[Renegade]

Basic tutorial on using regular expressions in Excel VBA: http://www.tmehta.com/regexp/index.htm
Regular Expression guide (many languages): http://www.regular-expressions.info/
List of articles showing some other ways of using Regex in Excel VBA: http://excelicious.wordpress.com/category/regular-expressions/

That's some good stuff there!

I'm still hoping that they add in pure .NET at some point as I don't really like VBA.

[Ampa]

The lack of regular expression support in Office is quite frankly astounding.

Glad you agree! I realise that they are available via VB, but why on earth can't I have easy GUI access in dialogues such as Conditional Formatting and Data Validation?!

If you don't mind using UDFs (User Defined Excel Functions), regular expression matching is also possible:

This looks great! Thanks for such a clear complete and yet concise reply! 

Basic tutorial on using regular expressions…

Cheers, useful reading matter.

Target: Thanks for your input too also

[kfitting]

Actually, what I like less than having "no Regular Expression support" in office, is having "partial support only in Word."  Look at the Word Find help and you'll see some regex-like stuff.  To me it just completely confuses the issue (personal opinion of course). 

At least with VB you can get it back, but yes, it would certainly be nice to have it built in (unless you dont dont trust the probability of MS' implementation being helpful of course!). 

[bob99]

In addition to the help & great site links above the forums at Mr. Excel might be of help. http://www.mrexcel.com/forum/index.php
I looked and Mr. Excel was briefly mentioned in the forums back in 09.  The forum members are quite active like they are here and helped me out recently with something I was trying to do.

On the main page http://www.mrexcel.com/ he also has tutorial videos, a blog and other helpful information.  Some free some some not.

Not affiliated with them in any way.