FOR loop in Excel - find and replace

Question

I have the following code

Sub CleanCat()
Dim i As Integer

For i = 1 To 50

    Columns("A").Replace What:="Cat" & i, Replacement:="Category" & i, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Columns("A").Replace What:="Cat " & i, Replacement:="Category" & i, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Columns("A").Replace What:="Category " & i, Replacement:="Category" & i, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Columns("A").Replace What:="Category" & i, Replacement:="Category" & i, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

    Columns("A").Replace What:="cat" & i, Replacement:="Category" & i, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Columns("A").Replace What:="cat " & i, Replacement:="Category" & i, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Columns("A").Replace What:="category " & i, Replacement:="Category" & i, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    Columns("A").Replace What:="category" & i, Replacement:="Category" & i, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

Next                                                     
End Sub

What I want is to loop through every cell in column A and do the replacements shown (I am looping through tweets) but this doesn't replace everything. I get stuff such as something cat 13 here left

Example tweets:

@thisaccount  I nominate @thataccountfor category 12 #somehashtag
Cat 12 I nominate @thisaccount #somehashtag

Any ideas?

My guess is that the "space" is not a true space but another character that looks like a space. — Scott Craner
– Scott Craner, Commented Aug 14, 2017 at 15:29
cat 13 is not the same as cat13 and you should loop backwards so that replacing cat 1 doesn't replace cat 13. — user4039065
– user4039065, Commented Aug 14, 2017 at 15:30
To add to what @ScottCraner said, try Columns("A").Replace What:=chr(160), Replacement:=chr(32)) before entering the loop. — user4039065
– user4039065, Commented Aug 14, 2017 at 15:32

Scott Craner · Accepted Answer · 2017-08-14 20:18:04Z

Another option without the number loop. (Note: I built on @Jeeped's answer)

It also puts the words in an array for easier updating.

Sub CleanCat()
    Dim i As Long
    Dim srch() As Variant
    Dim srchPart As Variant

    srch = Array("Category ", "Category", "Cat ", "Cat") ' make sure this is in order longest to shortest.

    With Worksheets("Sheet1")
        .Columns("A") = .Evaluate("INDEX("'" & A:A,)")
        .Columns("A").Replace What:=Chr(160), Replacement:=Chr(32), LookAt:=xlPart

        For Each srchPart In srch
            .Columns("A").Replace What:=srchPart, Replacement:="}}}}", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        Next srchPart

        .Columns("A").Replace What:="}}}}", Replacement:="Category", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    End With
End Sub

I also stole @Jeeped's formula to test:

score 1 · Accepted Answer · 2017-08-14 16:02:56Z

1

This is all you should require.

Option Explicit

Sub CleanCat()
    Dim i As Long

    With Worksheets("sheet1")
        .Columns("A").Replace What:=Chr(160), Replacement:=Chr(32), LookAt:=xlPart
        For i = 50 To 1 Step -1

            .Columns("A").Replace What:="Cat" & i, Replacement:="Category" & i, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            .Columns("A").Replace What:="Cat " & i, Replacement:="Category" & i, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            .Columns("A").Replace What:="Category " & i, Replacement:="Category" & i, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

        Next i
    End With
End Sub

My sample data was created with,

=CHOOSE(RANDBETWEEN(1, 3), "cat", "Cat", "category")&CHOOSE(RANDBETWEEN(1, 3), TEXT(,), CHAR(32), CHAR(160))&RANDBETWEEN(1, 50)

edited Aug 14, 2017 at 16:02

answered Aug 14, 2017 at 15:41

user4039065

7 Comments

Scott Craner Over a year ago

Do you really need the loop, are we not just replacing "Cat ","Cat", and "Category " with "Category"? Doesn't the number remain the same? Or did I miss something.

user4039065 Over a year ago

No, i is iterated from numbered categories as I see it. But I see what you mean, that might work. (actually, it looks like it does in you response)

pee2pee Over a year ago

I still get stuff like this remaining: ...category 12 ... I am looping through tweets which have ampersands, hashes etc. Should this matter?

user4039065 Over a year ago

Of course it matters. That is why it is important to show sample data together with expected results. See minimal reproducible example. If you have a 'special' problem, how many guesses do you think we will offer before giving up?

Scott Craner Over a year ago

@pee2pee When the string starts with @ excel is trying to see it as a formula. when you import you will need to set those as text string.

|

Collectives™ on Stack Overflow

FOR loop in Excel - find and replace

2 Answers 2

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related