replace text only if the text is complete string in cell

[kwagner1]

Is there anyway to do a find/replace that will replace the text if, and only if, the text being replaced is the complete string in the cell (i.e. exact match).
Example:
ColA in my worksheet may contain data as follows:
AI1234
AI123456

I want my VB routine to always replace AI1234 with "ID25" and I always want to replace AI123456 with "ID33" (simple enough). What i don't want is the "AI123456" to become "ID2556" when the command to replace AI1234 executes.

I'd like to do this using VB code.

Thanks!

 

[jindon]

Hi
1) Replace AI123456 with ID33
2) Replace AI1234 with ID25

so that ID2556 will never happen.

 

[mikerickson]

You could check the "find entirecells only" box in the Replace from the Edit Menu. If you want VB, this shows the syntax you want, specificaly the LookAt argument.

Selection.Replace What:="aa", Replacement:="x", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=True

 

[kwagner1]

thanks guys. Mike - i think i'll take your approach.. the LookAt:=xlWhole appears to be what I need...

thanks guys!

 

[Yogi Anand]

Hi kwagner1:

How about using ...

Range("A:A").Replace What:="AI1234", Replacement:="ID25", LookAt:=xlWhole
Range("A:A").Replace What:="AI123456", Replacement:="ID33", LookAt:=xlWhole

 

[stanleydgromjr]

kwagner1,

Try this:

Option Explicit
Sub Test()
    Dim i, j As Long
    Application.ScreenUpdating = False
    i = Range("A" & Rows.Count).End(xlUp).Row
    'I want my VB routine to always replace AI1234 with "ID25" and I always want to replace AI123456 with "ID33"
    For j = 1 To i
        With Cells(j, 1)
            .Value = Replace(Cells(j, 1), "AI123456", "ID33")
        End With
    Next j
    For j = 1 To i
        With Cells(j, 1)
            .Value = Replace(Cells(j, 1), "AI1234", "ID25")
        End With
    Next j
    Application.ScreenUpdating = True
End Sub

Have a greaqt day,
Stan

 

[jindon]

Stan,
2nd loop is not needed

Option Explicit
Sub Test()
    Dim i, j As Long
    Application.ScreenUpdating = False
    i = Range("A" & Rows.Count).End(xlUp).Row
    'I want my VB routine to always replace AI1234 with "ID25" and I always want to replace AI123456 with "ID33"
    For j = 1 To i
        With Cells(j, 1)
            .Value = Replace(.Cells.Value, "AI123456", "ID33")
            .Value = Replace(.Cells.Value, "AI1234", "ID25")
        End With
    Next j
    Application.ScreenUpdating = True
End Sub

 

[stanleydgromjr]

jindon,

As always, thank you. We are getting better all the time.

Hasve a great day,
Stan