Regular Expressions - Find & Replace

[HendPro12]

Hi,

What is wrong with the following formula?

=SearchNReplace1("[a-zA-Z0-9_]*\s[a-zA-Z0-9_]*\s[a-zA-Z0-9_]*[…]$","[...]$","\s",A9)

I am trying to do the following: If cell A9 contains (word followed by a space followed by a word followed by a space followed by a word followed by ...), replace ... with nothing or a whitespace

Here is the SearchNReplace1 function, however I believe the logic in the function is correct.

<CODE>Public Function SearchNReplace1(Pattern1 As String, _
Pattern2 As String, Replacestring As String, _
TestString As String)
Dim reg As New RegExp
reg.IgnoreCase = True
reg.MultiLine = False
reg.Pattern = Pattern1
If reg.Test(TestString) Then
reg.Pattern = Pattern2
SearchNReplace1 = reg.Replace(TestString, ReplaceString)
Else
SearchNReplace1 = TestString
End If
End Function
</CODE>

Thanks in advance!

 

[pgc01]

I am trying to do the following: If cell A9 contains (word followed by a space followed by a word followed by a space followed by a word followed by ...), replace ... with nothing or a whitespace

Hi

I don't understand the pattern, but from your description, I'd use something like:

Public Function SearchNReplace1(sPattern1 As String, sReplacestring As String, _
                                 sTestString As String) As String
Dim reg As RegExp
 
Set reg = New RegExp
With reg
    .IgnoreCase = True
    .Pattern = sPattern1
    SearchNReplace1 = .Replace(sTestString, sReplacestring)
End With
End Function

You can test like:

Sub test()
Dim s As String
 
s = "123 abc"
MsgBox SearchNReplace1("^\w+(\s\w+)*$", " ", s)
 
s = "123 abc 1a2 a1b"
MsgBox SearchNReplace1("^\w+(\s\w+)*$", " ", s)
 
s = "!123 abc"
MsgBox SearchNReplace1("^\w+(\s\w+)*$", " ", s)
End Sub

In the first 2 cases the result is a space, in the third case the substitution doesn't happen because one of the characters invalidates the pattern.

 

[HendPro12]

pgc01,

Thanks for your help. However, its not working. Here is some sample data:

STEPHANIE ADAMS disa...

If a cell contains data in this format, I want to remove the final word segment as well as the 3 periods that follow.

Example: Remove (disa...)

Thanks

<table border="0" cellpadding="0" cellspacing="0" width="251"><col width="251"><tr height="20"> <td style="height:15.0pt;width:188pt" height="20" width="251">
</td> </tr></table>

 

[pgc01]

Hi

Sorry, but it's still not clear to me. To define a pattern for a regular expression you must have a clear and rigorous definition.

Here is some sample data:

STEPHANIE ADAMS disa...

If a cell contains data in this format, I want to remove the final word segment as well as the 3 periods that follow.

This is what I understand:

If the last part of a text consists of a space followed by a word followed by three dots, then delete this last part.

I'd use the same code with a different pattern.

Public Function SearchNReplace1(sPattern1 As String, sReplacestring As String, _
                                 sTestString As String) As String
Dim reg As RegExp
 
Set reg = New RegExp
With reg
    .IgnoreCase = True
    .Pattern = sPattern1
    SearchNReplace1 = .Replace(sTestString, sReplacestring)
End With
End Function
 
Sub test()
Dim s As String
Dim sPattern As String
 
sPattern = "\s\w+\.{3}$"
 
s = "123 abc..."
MsgBox SearchNReplace1(sPattern, " ", s)
 
s = "123 abc 1a2 a1b..."
MsgBox SearchNReplace1(sPattern, " ", s)
 
s = "123 abc.."
MsgBox SearchNReplace1(sPattern, " ", s)
 
s = "123 abc ..."
MsgBox SearchNReplace1(sPattern, " ", s)
End Sub

In the first 2 cases the last part is deleted because it consists of a space + a word + 3 dots. In the last no cases there's no substitution since the pattern is not satisfied.

 

[HendPro12]

pgc01,

Thanks! Works great.

 

[pgc01]

I'm glad it helped. Thanks for the feedback.