Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 05-29-2012, 06:23 AM
tinfanide tinfanide is offline Windows 7 64bit Office 2010 32bit
Expert
 
Join Date: Aug 2011
Posts: 308
tinfanide is on a distinguished road
Default Regular Expressions: [!0-9] does not work???


Code:
Sub test()

Dim str As String
str = "str 9999 9999 str"

Dim RegExp As Object
Set RegExp = CreateObject("vbscript.regexp")

With RegExp
    .IgnoreCase = True
    .MultiLine = False
    .Pattern = "[!0-9]"
    .Global = True
End With

output = RegExp.Replace(str, "")
MsgBox output

Set RegExp = Nothing

End Sub
I can't understand why [a-z] can extract the numbers but why [!0-9] can't.
Reply With Quote
  #2  
Old 05-29-2012, 09:14 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Windows 7 32bit Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 381
Colin Legg will become famous soon enough
Default

I think you mean to use this:

Code:
.Pattern = "[^0-9]"
Which could be simplified to:
Code:
.Pattern = "\D"
Reply With Quote
  #3  
Old 05-29-2012, 06:57 PM
tinfanide tinfanide is offline Windows 7 64bit Office 2010 32bit
Expert
 
Join Date: Aug 2011
Posts: 308
tinfanide is on a distinguished road
Default

Exactly. Thanks for reminding me about the use of "\D".

Just wonder why the tutorial below says this:
http://www.gmayor.com/replace_using_wildcards.htm
Under the "[!]" section, it says "!" means "NOT". Does VBA regular expressions share a different set of symbols from "Find and Replace" regular expressions?
Reply With Quote
  #4  
Old 05-30-2012, 01:21 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Windows 7 32bit Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 381
Colin Legg will become famous soon enough
Default

Hi,

That find and replace tutorial is for using Microsoft Word's find and replace tool. Regex has it's own set of wildcards and special characters, examples:

http://www.aivosto.com/vbtips/regex.html
Reply With Quote
  #5  
Old 05-30-2012, 04:09 AM
tinfanide tinfanide is offline Windows 7 64bit Office 2010 32bit
Expert
 
Join Date: Aug 2011
Posts: 308
tinfanide is on a distinguished road
Default

Thank you for the reference.
It adds more to my programming library...
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
work vs regular work. and how regular work works user0044 Project 5 03-06-2012 07:28 AM
regular Experessions Formatting for Search sesproul Mail Merge 1 11-30-2011 05:02 AM
Access 2003 says unsafe expressions not blocked, but only some users on same computer ZLA Office 3 07-23-2010 10:00 AM
Issue skiping characters by Regular Expressions in Word pochtara Word 0 04-01-2010 05:37 AM
How do I have headers and footers appear as regular text for the whole document? user908045 Word 0 03-13-2010 12:41 PM


All times are GMT -7. The time now is 10:51 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft