How to delete last occurence of a character in vba

[spyrule]

Hello,
I've got a weird problem where I'm struggling to detect the last occurence of the character "-" and replace it with "x".

Does anybody have a way to take a string and replace only the last occurence of that character? (I've gotten a replace function to work, but it replaces ALL occurences, which isn't what i wanted).

Any helps is appreciated!

 

[Eric W]

Try:

Mid(str1, InStrRev(str1, "-"), 1) = "x"

 

[Mentor82]

Hi,
Let's assume you have a text in cells(1,1) you want to replace last occurence of "-" with "x". You have to loop backwards the string from last char to first and check the first occurence of "-". Then you have to use this char position in replace function as a 4th parameter.

Check below code.

Sub CharReplacement()
Dim str_text$
Dim i&

str_text= cstr(cells(1,1))
If len(str_text)>0 then
   For i =len(str_text) to 1 step -1
      If right(left(str_text,i),1)="-" then
         cells(1,1)=Replace (str_text, "-","x",i)
         exit for
      End if
   Next i
End if

End sub

 

[JoeMo]

Here's a UDF you can try:

Function ReplaceLastOne(S As String) As String
Const Char As String = "-"
Dim Position As Long
If InStr(S, Char) = 0 Then
    ReplaceLastOne = S
    Exit Function
End If
Position = InStrRev(S, Char)
ReplaceLastOne = Mid(S, 1, Position - 1) & Replace(S, Char, "x", Position, 1)
End Function

 

[MARK858]

Ugly way that appears to work (based on string being in A1, result in A2 just for testing)

Sub replaceit()
Cells(2, 1) = StrReverse(Replace(StrReverse(Cells(1, 1).Value), "-", "x", , 1))
End Sub

I don't think it will be as efficient as what Eric W posted in post number 2 but I haven't tested them for speed.

 

[rlv01]

Function ReplaceLastChar(ByVal S As String, FindChar As String, ReplaceChar As String) As String
    Dim SPos As Long

    SPos = InStrRev(S, FindChar)
    If SPos > 0 Then
        S = Left(S, SPos - 1) & ReplaceChar & Mid(S, SPos + 1, Len(S))
    End If
    ReplaceLastChar = S
End Function

 

[lrobbo314]

Another way. Takes 3 arguments. 1.) The value 2.) The character to replace, e.g. "-" 3.) The character to replace it with, e.g. "x".

Function REPLACELAST(s As String, findStr As String, repSTR) As String
With CreateObject("VBScript.RegExp")
    .Pattern = "(" & findStr & ")([^" & findStr & "]+$)"
    REPLACELAST = .Replace(s, repSTR & "$2")
End With
End Function