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

"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.

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

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

"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?

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

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

"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 "*".

"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"

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

"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.

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

"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

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

"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?

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

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

"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.

"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.

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

"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!

"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