Main Area and Open Discussion > General Software Discussion

Excel - pattern matching in cells

(1/2) > >>

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/

Navigation

[0] Message Index

[#] Next page