# Regular Expressions - Find & Replace

#### HendPro12

##### New Member
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>

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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:

Code:
``````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:

Code:
``````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.

pgc01,

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

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>

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:

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.

Code:
``````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.

pgc01,

Thanks! Works great.

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

Replies
8
Views
727
Replies
6
Views
318
Replies
5
Views
2K
Replies
17
Views
3K
Replies
0
Views
963

1,203,396
Messages
6,055,157
Members
444,766
Latest member
bryandaniel5

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back