How to Find and Replace a string if cell does not contain certain texts.

**windcloud2** · 04-04-2012, 11:35 AM

Hi I want to create a macro that searches a column for a specific criteria, it will then check the portion being replaced if it contains either "Apt", "Suite", or "Floor"
before replacing it.

For Ex if I were to Find "*West" and replace with "", I want it to be able to check to see if "Suite" is in the portion being replaced before replacing it, and if it is then dont replace:

A1) 123 Fake Street West Apt 1
A2) 234 Random Street Suite 3 West
A3) 333 Excel Ave West Floor 3

Result should be the following.
b1) Apt 1
b2) 234 Random Street Suite 3 West
b3) Floor 3

any ideas?

thanks!

**rvasquez** · 04-04-2012, 12:03 PM

Hello windcloud2,

This can be done with a formula in a column next to the original cell.

=RIGHT(A1,LEN(A1)-SEARCH("West",A1)-4)

Thanks,

Rvasquez

**Dennis7849** · 04-04-2012, 12:18 PM

This works.

Sub Macro1()

    FindString = InputBox("Enter the string to find.")
    ReplaceString = InputBox("Enter the replacement string.")
    StartRow = 1
    EndRow = 3
    
    For MyRow = StartRow To EndRow
    
        Range("A" + CStr(MyRow)).Select
    
        Test1 = InStr(Range("A" + CStr(MyRow)).Text, " Apt")
        Test2 = InStr(Range("A" + CStr(MyRow)).Text, " Suite")
        Test3 = InStr(Range("A" + CStr(MyRow)).Text, " Floor")
        If Test1 = 0 And Test2 = 0 And Test3 = 0 Then
              ActiveCell.Replace What:=FindString, Replacement:=ReplaceString, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        End If
    
    Next MyRow
    
End Sub

**windcloud2** · 04-04-2012, 12:55 PM

Dennis,

hmm unfortunately that macro still replaces all content.
When I enter "*West" into the prompt it still deletes everything before "west" even though floor or apt is before it.

A1) 123 Fake Street Floor 1 Apt 2 West

If I enter "*West" into the prompt I should get the following because the test failed.

B1) 123 Fake Street Floor 1 Apt 2 West