Using =MID To Replace Specific . In String Only

[indi visual]

I am trying to replace the dot at a specific point in a string.


It will always be the dot followed by 6 characters and the word "EndSearchMarker".


But all of that might be at the front, middle, or end (so I think the MID function is key).


The code below works, but not what I need it for because it replaces all the dots in the string (when I actually only need the 1 dot replaced with "DotMarker").


''eg

Blah.Blah.Blah.Example.StringEndSearchMarker

to

Blah.Blah.Blah.ExampleDotMarkerStringEndSearchMarker


Could anyone help me with the MID line that achieves this?


Sub Replace_This_Dot_Only()

Dim Cell As Range

For Each Cell In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)

If Cell Like "*.??????EndSearchMarker*" Then

Cell.Replace What:=".", Replacement:="DotMarker"

End If

Next Cell

End Sub

 

[Hui]

You say

It will always be the dot followed by 6 characters and the word "EndSearchMarker".

But your example isn't like that

It is

Blah.Blah.Blah.Example.StringEndSearchMarker

which has 7 characters and a . after the .


However maybe this will help

[pre]

Sub Replace_This_Dot_Only()
Dim Cell As Range
Dim j As Integer

For Each Cell In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
If Cell Like "*.*" Then
j = 0
Do While Mid(Cell.Value, Len(Cell) - j) <> "."
If Mid(Cell.Value, Len(Cell.Value) - j, 1) = "." Then
Cell.Value = Left(Cell.Value, Len(Cell.Value) - j) + "dotmarker" + Right(Cell.Value, Len(Cell) - j - 2)
Exit Do
End If
j = j + 1
Loop
End If
Next Cell
End Sub

or this slimmed down version

Sub Replace_This_Dot_Only()
Dim Cell As Range
Dim loc As Integer

For Each Cell In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
If Cell Like "*.StringEndSearchMarker" Then
loc = InStr(Cell.Value, ".StringEndSearchMarker")
Cell.Value = Application.WorksheetFunction.Replace(Cell.Value, loc, Len(Cell.Value) - loc + 1, "DotMarkerStringEndSearchMarker")
End If
Next Cell
End Sub

[/pre]

 

[indi visual]

I can't lie I'm super excited about this one thanks Hui.


Resolved.