topbanner_forum
  *

avatar image

Welcome, Guest. Please login or register.
Did you miss your activation email?

Login with username, password and session length
  • Thursday March 28, 2024, 9:12 am
  • Proudly celebrating 15+ years online.
  • Donate now to become a lifetime supporting member of the site and get a non-expiring license key for all of our programs.
  • donate

Author Topic: Excel - pattern matching in cells  (Read 38374 times)

Ampa

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 592
  • I am cute ;)
    • View Profile
    • MonkeyDash - 2 Player strategy boardgame
    • Donate to Member
Excel - pattern matching in cells
« on: March 16, 2011, 06:19 PM »
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

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 13,288
  • Tell me something you don't know...
    • View Profile
    • Renegade Minds
    • Donate to Member
Re: Excel - pattern matching in cells
« Reply #1 on: March 16, 2011, 07:10 PM »
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.
Slow Down Music - Where I commit thought crimes...

Freedom is the right to be wrong, not the right to do wrong. - John Diefenbaker

Target

  • Honorary Member
  • Joined in 2006
  • **
  • Posts: 1,832
    • View Profile
    • Donate to Member
Re: Excel - pattern matching in cells
« Reply #2 on: March 16, 2011, 07:15 PM »
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

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 8
    • View Profile
    • Donate to Member
Re: Excel - pattern matching in cells
« Reply #3 on: March 17, 2011, 02:04 AM »
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 FunctionsExcel Custom Functions using VBA

kfitting

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 593
    • View Profile
    • Donate to Member
Re: Excel - pattern matching in cells
« Reply #4 on: March 17, 2011, 05:46 AM »
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

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 13,288
  • Tell me something you don't know...
    • View Profile
    • Renegade Minds
    • Donate to Member
Re: Excel - pattern matching in cells
« Reply #5 on: March 17, 2011, 06:02 AM »
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.
Slow Down Music - Where I commit thought crimes...

Freedom is the right to be wrong, not the right to do wrong. - John Diefenbaker

Ampa

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 592
  • I am cute ;)
    • View Profile
    • MonkeyDash - 2 Player strategy boardgame
    • Donate to Member
Re: Excel - pattern matching in cells
« Reply #6 on: March 17, 2011, 06:22 AM »
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!  :up:

Basic tutorial on using regular expressions…

Cheers, useful reading matter.

Target: Thanks for your input too also :)

kfitting

  • Charter Member
  • Joined in 2005
  • ***
  • Posts: 593
    • View Profile
    • Donate to Member
Re: Excel - pattern matching in cells
« Reply #7 on: March 17, 2011, 07:30 AM »
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

  • Supporting Member
  • Joined in 2008
  • **
  • default avatar
  • Posts: 345
    • View Profile
    • Donate to Member
Re: Excel - pattern matching in cells
« Reply #8 on: March 17, 2011, 09:24 AM »
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.