CODEWITHSUNDEEP
excelexcel-2013vba
xxxRxxx
xxxRxxx

Reputation: 367

How to modify part of a string with VBA

So for example (the Then statement isn't correct, I'm trying to figure out what to put there):

For i = 1 to 20

If Instr(1, Cells(i, "A"), "@goggle.com") > 0 Then
"@goggle.com" = "@google.com"

Next i

So let's say the email "[email protected]" is in cell A2, so Instr finds "@goggle.com" in A2. If I want to change the @goggle.com to @google.com, how would I do that?

My goal is to find common misspellings of email domains and fix them.

Upvotes: 0

Views: 1632

Answers (2)

paul bica
paul bica

Reputation: 10715

To fix your code you can use the Replace function, as mentioned

For i = 1 To 20

    If InStr(1, Cells(i, "A"), "@goggle.com") > 0 Then

        Cells(i, "A") = Replace(Cells(i, "A"), "@goggle.com", "@google.com")

    End If

Next

but to be more efficient about all replacements use the Range().Replace method for a list of values and replacements:

Option Explicit

Public Sub fixColumnSpelling()

    Const FIND_LIST As String = "@goggle.com @yahho.com @test1.com"
    Const REPL_LIST As String = "@google.com @yahoo.com @test2.com"

    Dim totalItems As Long, i As Long, findItems As Variant, replItems As Variant

    findItems = Split(FIND_LIST)
    replItems = Split(REPL_LIST)

    totalItems = UBound(findItems)

    For i = 0 To totalItems     'bulk replecements in col A

        ActiveSheet.UsedRange.Columns(1).Replace _
            What:=findItems(i), _
            Replacement:=replItems(i), _
            LookAt:=xlPart, _
            SearchOrder:=xlByColumns, _
            MatchCase:=False

    Next

End Sub

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152450

Look into the Replace Statement.

In this instance:

Replace(Cells(i, "A"), "@google.com", "@gmail.com")

Basically the formula says, in this string `Cells(i,"A") find "@google.com" and replace it with "@gmail.com"

With this you will not need the if statement as the replace function will not replace anything that it does not find. In other words, if "@google.com" is not found in the string it moves on and returns nothing.

Upvotes: 1

Related Questions