replace : with _

[dimava]

I have a text file with 3 colons (:) in each line (with text in between each colon)

I need to replace the 3rd colon in each line with an underscore (_)

how can I do this?

thanks,

Dimava

[MartinLiss]

Public Function ChangeThirdColon(strText As String) As String
 
    Dim intPos As Integer
    Const COLON As String = ":"
    Const UNDERSCORE As String = "_"
    
    intPos = InStr(1, strText, COLON)
    intPos = InStr(intPos + 1, strText, COLON)
    intPos = InStr(intPos + 1, strText, COLON)
    
    strText = Left$(strText, intPos - 1) & UNDERSCORE & Right$(strText, Len(strText) - intPos)
 
End Function

[dimava]

you da man

[KayJay]

Should the third colon be the last colon of the line, InStrRev() will reduce 3 steps to find the indices to one. Modifying Martin's code : -

Public Function ChangeLastColon(strText As String) As String
    Dim intPos As Integer
    Const COLON As String = ":"
    Const UNDERSCORE As String = "_"
    intPos = InStrRev(strText, COLON, -1, vbBinaryCompare)
    strText = Left$(strText, intPos - 1) & UNDERSCORE & Right$(strText, Len(strText) - intPos)
End Function
 
Private Sub Command1_Click()
Dim strVal As String
strVal = "ABCD:EFGH:IJKL:MNOP"
Call ChangeLastColon(strVal)
MsgBox strVal
End Sub