regEx replace
If a substring is found that matches my regular expression
pattern, I would like each character to be replaced with a "*" character. I thought the following code would work, but I'm missing something? : '-------------------------------------------------------- Sub myregex() Dim regEx As New VBScript_RegExp_55.RegExp Dim str As String str = "Call me at (982)555-1234!" regEx.Pattern = "(...)...-...." regEx.Global = True regEx.IgnoreCase = True MsgBox regEx.Replace(str, "*") End Sub |
regEx replace
"Robert Crandal" wrote:
'-------------------------------------------------------- Sub myregex() Dim regEx As New VBScript_RegExp_55.RegExp Dim str As String str = "Call me at (982)555-1234!" regEx.Pattern = "(...)...-...." regEx.Global = True regEx.IgnoreCase = True MsgBox regEx.Replace(str, "*") End Sub If I wasn't clear, the output message should be: ************* But, I would also be happy if the output was: (***)***-**** Either way is fine, but the first is better. |
regEx replace
Hi Robert,
Am Thu, 2 Apr 2015 03:08:31 -0700 schrieb Robert Crandal: If I wasn't clear, the output message should be: ************* But, I would also be happy if the output was: (***)***-**** try: Sub ReplaceGlobal() Dim str As String Dim ptrn As String, re As Object str = "Call me at (982)555-1234!" ptrn = "[0-9]" Set re = New RegExp re.Pattern = ptrn re.IgnoreCase = False re.Global = True MsgBox re.Replace(str, "*") End Sub Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
regEx replace
Hi again,
Am Thu, 2 Apr 2015 03:08:31 -0700 schrieb Robert Crandal: If I wasn't clear, the output message should be: ************* Sub ReplaceGlobal() Dim str As String Dim ptrn As String, re As Object str = "Call me at (982)555-1234!" ptrn = "\(|\)|-|!|\d" Set re = New RegExp re.Pattern = ptrn re.IgnoreCase = False re.Global = True MsgBox re.Replace(str, "*") End Sub Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
regEx replace
"Claus Busch" wrote:
Hi again, str = "Call me at (982)555-1234!" ptrn = "\(|\)|-|!|\d" Wow, I never woulda guessed that expression. Can you explain how it works? All I see are a couple of alternation "|" symbols and one digit "\d" metacharater. Also, what pattern works for Social Security numbers? |
regEx replace
Hi Robert,
Am Thu, 2 Apr 2015 09:47:12 -0700 schrieb Robert Crandal: Can you explain how it works? All I see are a couple of alternation "|" symbols and one digit "\d" metacharater. \d is equivalent to [0-9] To find parentheses you have to put a back slash in front \( and \) But I am not an expert with Regulkar Expressions. I always play around with try and error. Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
regEx replace
Hi again,
Am Thu, 2 Apr 2015 09:47:12 -0700 schrieb Robert Crandal: Can you explain how it works? All I see are a couple of alternation "|" symbols and one digit "\d" metacharater. the alternation sign splits all characters in your string and the characters are searched one by one. If you want to do it with a pattern try: ptrn = "[\(\d\)\d-\d!]" can you post an example of Social Security numbers? I don't know how they look like in your country. Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
regEx replace
"Claus Busch" wrote:
\d is equivalent to [0-9] To find parentheses you have to put a back slash in front \( and \) But I am not an expert with Regulkar Expressions. I always play around with try and error. Yup, I'm not a regular expression expert either. For the pattern, my guess was: "\(\d\d\d\)\d\d\d-\d\d\d\d" The only problem was that the entire string got replaced with a single "*", instead of filling in all 13 characters with a "*". |
regEx replace
"Claus Busch" wrote:
can you post an example of Social Security numbers? I don't know how they look like in your country. In my country, they are typically 123-45-6789. So, my guess for the pattern would be: regEx.Pattern = "\d\d\d-\d\d-\d\d\d\d" |
regEx replace
Hi again,
Am Thu, 2 Apr 2015 10:26:21 -0700 schrieb Robert Crandal: regEx.Pattern = "\d\d\d-\d\d-\d\d\d\d" try it with ptrn = "[\d-\d-\d]" Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
regEx replace
"Claus Busch" wrote:
Hi again, Am Thu, 2 Apr 2015 10:26:21 -0700 schrieb Robert Crandal: regEx.Pattern = "\d\d\d-\d\d-\d\d\d\d" try it with ptrn = "[\d-\d-\d]" Okay, that works good enough for me. However, it is not very precise because it will also erase something like 123-45-67890000000025, which is not a valid Social Securty number. Your solution is still good enough, because I don't expect my data will contain odd numbers like that. |
regEx replace
Hi Robert,
Am Thu, 2 Apr 2015 10:39:38 -0700 schrieb Robert Crandal: However, it is not very precise because it will also erase something like 123-45-67890000000025, which is not a valid Social Securty number. you can add the length of the substrings: ptrn = "[\d{3}\d{2}\d{4}]" Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
regEx replace
"Claus Busch" wrote:
ptrn = "[\d{3}\d{2}\d{4}]" Hmmm, I tried that with the following code, but it didn't work: Sub ReplaceGlobal() Dim str As String Dim ptrn As String Dim re As New VBScript_RegExp_55.RegExp str = "My SSN's are 123-45-6789 or 13434344-34-13300012664" ptrn = "[\d{3}\d{2}\d{4}]" re.Pattern = ptrn re.IgnoreCase = False re.Global = True MsgBox re.Replace(str, "*") End Sub |
regEx replace
Hi Robert,
Am Thu, 2 Apr 2015 10:53:03 -0700 schrieb Robert Crandal: str = "My SSN's are 123-45-6789 or 13434344-34-13300012664" try: ptrn = "\b[0-9]{3}-[0-9]{2}-[0-9]{4}\b" Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
regEx replace
"Claus Busch" wrote:
ptrn = "\b[0-9]{3}-[0-9]{2}-[0-9]{4}\b" Awesome! You got it! Also, remember the previous phone number problem? The pattern was "\(|\)|-|!|\d", but that erases abnormal phone numbers such as (555)555-12345678900000. Do you know how to fix the pattern so it precisely only matches this format: (123)456-7890? |
regEx replace
Hi Robert,
Am Thu, 2 Apr 2015 12:36:47 -0700 schrieb Robert Crandal: Do you know how to fix the pattern so it precisely only matches this format: (123)456-7890? str = "Call me at (123)456-7890" ptrn = "\(\d{3}\)\d{3}-\d{4}" Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
regEx replace
This is pure VB. It returns a 7 or 10 digit result, trimming of leading
characters. So if string "12345678901" includes the long distance enum, it trims off the leading '1' and returns "(234) 567-8901". Change the formats to suit your needs... Function FormatPhoneNumber$(TextIn$) ' Strips out any embeded formatting characters and inserts dashes, spaces and '()' in the correct positions. ' Handles 7-digit and 10-digit phone numbers. Allows extensions. ' Arguments: TextIn The string being filtered ' Returns: Properly formatted phone number as a string ' Requires the FilterString() function. Const sSource As String = "FormatPhoneNumber()" If TextIn = "" Then Exit Function Dim sTmp$, sExt$, iPos%, bHasExt As Boolean 'Check for an extension iPos = InStr(1, TextIn, Choose(3, "Ext", "EXT", "ext"), vbTextCompare) If iPos 0 Then bHasExt = True sExt = FilterString(Mid(TextIn, iPos), , False) TextIn = Left(TextIn, iPos - 1) End If ' Get rid of any unwanted characters sTmp = FilterString(TextIn, , False) ' Insert dashes in the correct positions If Len(sTmp) <= 7 Then If bHasExt Then FormatPhoneNumber = Format$(sTmp, "!@@@-@@@@") & " Ext " & sExt Else FormatPhoneNumber = Format$(sTmp, "!@@@-@@@@") End If Else If bHasExt Then FormatPhoneNumber = Format$(sTmp, "!(@@@) @@@-@@@@") _ & " Ext " & sExt Else FormatPhoneNumber = Format$(sTmp, "!(@@@) @@@-@@@@") End If End If End Function 'FormatPhoneNumber() Function FilterString$(ByVal TextIn$, Optional IncludeChars$, _ Optional IncludeLetters As Boolean = True, _ Optional IncludeNumbers As Boolean = True) ' Filters out all unwanted characters in a string. ' Arguments: TextIn The string being filtered. ' IncludeChars [Optional] Any non alpha-numeric characters to keep. ' IncludeLetters [Optional] Keeps any letters. ' IncludeNumbers [Optional] Keeps any numbers. ' ' Returns: String containing only wanted characters. ' Comments: Works very fast using the Mid$() function over other methods. Const sSource As String = "FilterString()" 'The basic characters to always keep by default Const sLetters As String = "abcdefghijklmnopqrstuvwxyz" Const sNumbers As String = "0123456789" Dim i&, CharsToKeep$ CharsToKeep = IncludeChars If IncludeLetters Then _ CharsToKeep = CharsToKeep & sLetters & UCase(sLetters) If IncludeNumbers Then CharsToKeep = CharsToKeep & sNumbers For i = 1 To Len(TextIn) If InStr(CharsToKeep, Mid$(TextIn, i, 1)) Then _ FilterString = FilterString & Mid$(TextIn, i, 1) Next End Function 'FilterString() -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
regEx replace
"Claus Busch" wrote:
str = "Call me at (123)456-7890" ptrn = "\(\d{3}\)\d{3}-\d{4}" That one didn't work. The output was "Call me at *" Oh, the pattern for the SSN didn't work either: ptrn = "\b[0-9]{3}-[0-9]{2}-[0-9]{4}\b" It replaces the entire SSN with a single "*" char. |
regEx replace
"GS" wrote:
Function FormatPhoneNumber$(TextIn$) ' ....... End Function 'FormatPhoneNumber() Function FilterString$(ByVal TextIn$, Optional IncludeChars$, _ ' ........ End Function 'FilterString() Hey Gary. Thanks again for this code. I knew you always had an alternative to regular expressions. |
regEx replace
Hi Robert,
Am Fri, 3 Apr 2015 01:27:59 -0700 schrieb Robert Crandal: That one didn't work. The output was "Call me at *" the pattern is handled as ONE word. So you get only 1 *. Calculate the length of the substring to replace first: Sub ReplaceGlobal() Dim str As String Dim ptrn As String, re As Object Dim lLen As Long str = "Call me at (982)555-1234" ptrn = "\(\d{3}\)\d{3}-\d{4}" lLen = Len(str) - InStr(str, "(") - 1 Set re = New RegExp re.Pattern = ptrn re.IgnoreCase = False re.Global = True MsgBox re.Replace(str, Application.Rept("*", lLen)) End Sub Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
regEx replace
"Claus Busch" wrote:
the pattern is handled as ONE word. So you get only 1 *. Calculate the length of the substring to replace first: Sub ReplaceGlobal() Dim str As String Dim ptrn As String, re As Object Dim lLen As Long str = "Call me at (982)555-1234" ptrn = "\(\d{3}\)\d{3}-\d{4}" lLen = Len(str) - InStr(str, "(") - 1 Set re = New RegExp re.Pattern = ptrn re.IgnoreCase = False re.Global = True MsgBox re.Replace(str, Application.Rept("*", lLen)) End Sub Excellent! Thanks again for your help Claus! |
regEx replace
"GS" wrote:
Function FormatPhoneNumber$(TextIn$) ' ....... End Function 'FormatPhoneNumber() Function FilterString$(ByVal TextIn$, Optional IncludeChars$, _ ' ........ End Function 'FilterString() Hey Gary. Thanks again for this code. I knew you always had an alternative to regular expressions. Ugh! It only handles North American phone numbers. I can see a use, though, for having a function that formats based on country! <FWIW I use Farpoint's Spread.ocx in VB6 projects to duplicate my Excel projects as stand-alone Windows apps for users that don't/won't use MS Office. This control requires using code to replicate many of the built-in Excel features it doesn't have. This is one function I make use of for customer/vendor lists or contact info lists. It obviates using a separate field if an (optional) extension is used with my default Phone1/Phone2 data fields. I also have a function to format credit card numbers, but haven't had need to format social security numbers (yet). The 3 Excel features I replicate by code most often are Group/Ungroup, DV lists (which use a 'Combobox cell type' in place of a regular cell), and CF. There are 15 'cell types' in all, many of which have restricted 'data type' input by default as their respective 'data validation' feature. Thus, using 'dependant dropdowns' is also entirely managed by code. I also have the option to use multi-column dropdowns and multiple header rows/cols. The latter can be replicated in Excel by using FreezePanes, but this doesn't permit using FreezePanes in the non-header cell area. Events is the primary code vehicle for implementation. Group/Ungroup is a bit tricky in that a hidden helper column is needed to 'map' the range extent of a group. The coding is rather simple, though, and I have the option of using a 'picture' cell type to display whatever expand/collapse symbols I choose to use. I normally don't use symbols, but rather use event code for the cell containing the group 'label' text. For example, expense/income/bank ledger sheets group transactions for a fiscal period by month. Clicking a month 'label' toggles expand/collapse of row details. On the expense/income ledger sheets, clicking a Detail label toggles expand/collapse of the month distribution cols. I like that this technique saves the screen real estate lost by displaying the group/outline symbols Excel uses. Fortunately the Spread.ocx has all the common built-in formula functions I need, but it also supports UDFs. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
All times are GMT +1. The time now is 12:22 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com