text file replace

[assafbe]

hello
i am trying to replace spacific character in a text file
the text file contain several lins in a known format:
aaa ~ bbb ~ ccc ~ ddd ~ eee
fff ~ ggg ~ hhh ~ iii ~ jjj
and so on
i need to replace in the text file only the second "~" in each line to be "-":
aaa ~ bbb - ccc ~ ddd ~ eee
fff ~ ggg - hhh ~ iii ~ jjj

using the "replace" function won't help here
any idea?

[baja_yu]

Use Split() to split the string into an array using ~ as the delimiter. Then join it manually into a string using a for loop, but on the second iteration instead of joining with ~ join them with -

Just of the top of my head. There's probably a hundred more ways of going about it.

[LaVolpe]

If your files aren't very large, Mid$() can work well in this case.... with some other code. Steps look like following:

1) Read entire file into memory
2) Split() it on carriage returns
3) Loop thru each line, use InStr() to locate 2nd occurrence & replace it with Mid$()
4) When done looping, Join() array back to a string & write the string to file

Edited: similar approach to baja_yu

[RhinoBull]

...using the "replace" function won't help here...

It really depends how you use it:

Private Sub Command1_Click()
Dim fileNum As Integer
Dim sText As String
Dim arLines() As String
Dim iPos As Long
Dim i As Long

    fileNum = FreeFile()
    Open "c:\test.txt" For Input As #fileNum
        sText = Input(LOF(fileNum), #fileNum)
    Close #fileNum
    
    arLines() = Split(sText, vbNewLine)
    For i = 0 To UBound(arLines)
        iPos = InStr(InStr(1, arLines(i), "~") + 1, arLines(i), "~")
        arLines(i) = VBA.Left$(arLines(i), iPos - 1) & Replace(arLines(i), "~", "-", iPos, 1)
        '''Debug.Print arLines(i)
    Next i
    
    sText = Join(arLines(), vbNewLine)
    fileNum = FreeFile()
    Open "c:\test.txt" For Output As #fileNum
        Print #fileNum, sText
    Close #fileNum

End Sub

[anhn]

I suggest this for the loop: (safer and a bit faster)

    For i = 0 To UBound(arLines)
        iPos = InStr(arLines(i), "~")
        If iPos Then iPos = InStr(iPos + 1, arLines(i), "~")
        If iPos Then Mid$(arLines(i), iPos, 1) = "-"
    Next i

[Code Doc]

I suggest this for the loop: (safer and a bit faster)

    For i = 0 To UBound(arLines)
        iPos = InStr(arLines(i), "~")
        If iPos Then iPos = InStr(iPos + 1, arLines(i), "~")
        If iPos Then Mid$(arLines(i), iPos, 1) = "-"
    Next i

+1. Truly exceptional code. Instr() and Mid$() are not to be denied when used effectively as shown here.

[RhinoBull]

I suggest this for the loop: (safer and a bit faster)

I agree with your "sentiments" however my sample code was basically an "answer" to OP's comments (using the "replace" function won't help here) and was not a quest for efficiency.