How do i prevent the 2nd Instance of patricular String being Replaced using REPLACE Function

[NimishK]

Hello

How do i prevent the 2nd Instance of patricular String being Replaced using REPLACE Function

For Eg
Amount 75000.00
ID Number 2229400331
Status Amount Trfd

so i would like to prevent string Amount being replaced in line Status Amount Trfd

using the Below code

Dim Strline As String
         Strline = Replace(Strline, "Amount", vbCrLf & "AMOUNT")
         Strline = Replace(Strline, "ID Number", vbCrLf & "ID NUMBER")
         Strline = Replace(Strline, "Status", vbCrLf & "STATUS")

I get below result

AMOUNT 75000.00
ID NUMBER 22229400331
STATUS Amount Trfd
AMOUNT Trfd

Instead of

AMOUNT 75000.00
ID NUMBER 22229400331
STATUS Amount Trfd

Your help will be appreciated

NimishK

 

[Jaafar Tribak]

Set the 5th argument of the Replace function (Count) to 1.

 

[NimishK]

Set the 5th argument of the Replace function (Count) to 1.

Meaning ???

 

[Alex Blakenburg]

Since you are putting it on 2 lines, do you still want the space ?
Does this work for you ?
(Remove the -1 if you want to keep the space)

Sub testAlternative()

    Dim Strline As String
    Dim arrReplace As Variant
    Dim i As Long
    
    Strline = "ID Number 9999"        ' My test value only
    arrReplace = Array("AMOUNT", "ID NUMBER", "STATUS")
    
    For i = 0 To UBound(arrReplace)
        Strline = rCell.Value
    
        If InStr(1, Strline, arrReplace(i), vbTextCompare) = 1 Then
            Strline = arrReplace(i) & vbCrLf & _
                        Right(Strline, Len(Strline) - Len(arrReplace(i)) - 1)
        End If
        
        Debug.Print Strline
    Next i

End Sub

 

[NimishK]

Since you are putting it on 2 lines, do you still want the space ?
Does this work for you ?
(Remove the -1 if you want to keep the space)

Sub testAlternative()

    Dim Strline As String
    Dim arrReplace As Variant
    Dim i As Long
  
    Strline = "ID Number 9999"        ' My test value only
    arrReplace = Array("AMOUNT", "ID NUMBER", "STATUS")
  
    For i = 0 To UBound(arrReplace)
        Strline = rCell.Value
  
        If InStr(1, Strline, arrReplace(i), vbTextCompare) = 1 Then
            Strline = arrReplace(i) & vbCrLf & _
                        Right(Strline, Len(Strline) - Len(arrReplace(i)) - 1)
        End If
      
        Debug.Print Strline
    Next i

End Sub

Sir,
As per your Coding
i just put REM mark before Strline = rCell.Value as it messaged "variable not defined" Although i am not taking any reference of cell value Range
and i get following result with Debug.Print

ID Number 9999
ID NUMBER
9999
ID NUMBER
9999

Bit Confussed

 

[Alex Blakenburg]

Sorry I meant to remove the debug.print line and rCell.Value line.
You only provided 3 lines of code and not the whole macro, so I have no idea what your input or outputs are.
The debug.print was to see what it was doing, and the rCell was for me testing it using a range of data and a For each loop.

All I have done is put your 3 criteria into a loop, since your code currently performs all 3 replaces.
I have assumed you only want to do the replace if the replace word is at the beginning of Strline, so the If statement is identifying which Search term is at the beginning of Strline and then effectively replacing just the term(s) at the start which overcomes your additonal replace of the embedded Amount.

If you want to show me all your code or tell me where your inputs & outputs are coming from I can modify my suggested code.

 

[Jaafar Tribak]

Meaning ???

This is what I meant :
Dim Strline As String Strline = "Amount 22229400331 ID Number 2229400331 Status Amount Trfd" Strline = Replace(Strline, "Amount", vbCrLf & "AMOUNT", , 1) Strline = Replace(Strline, "ID Number", vbCrLf & "ID NUMBER") Strline = Replace(Strline, "Status", vbCrLf & "STATUS") MsgBox Strline

 

[NimishK]

Sorry I meant to remove the debug.print line and rCell.Value line.
You only provided 3 lines of code and not the whole macro, so I have no idea what your input or outputs are.
The debug.print was to see what it was doing, and the rCell was for me testing it using a range of data and a For each loop.

All I have done is put your 3 criteria into a loop, since your code currently performs all 3 replaces.
I have assumed you only want to do the replace if the replace word is at the beginning of Strline, so the If statement is identifying which Search term is at the beginning of Strline and then effectively replacing just the term(s) at the start which overcomes your additonal replace of the embedded Amount.

If you want to show me all your code or tell me where your inputs & outputs are coming from I can modify my suggested code.

Sir as desired by you

Sending the whole code

Public Sub Whole_Code()

Dim Strline As String
Dim strDataTxtFile As String

txtfilename = "C:\TRIAL\1-ABC.TXT"
txtfilename2 = "C:\TRIAL\2-XYZ.TXT"

Open txtfilename For Input As #1
     Open txtfilename2 For Output As #2
         While Not EOF(1)
           Line Input #1, strDataTxtFile
           strline = Strline & strDataTxtFile
           lineCount = lineCount + 1
          Wend
         Strline = Replace(Strline, "Amount", vbCrLf & "AMOUNT")
         Strline = Replace(Strline, "ID Number", vbCrLf & "ID NUMBER")
         Strline = Replace(Strline, "Status", vbCrLf & "STATUS")

   Print #2, strline;
   Close #2
   Close #1

End Sub

Structure of txtfilename = "C:\TRIAL\1-ABC.TXT" being INPUT

Amount 75000.00
ID Number 22229400331
Status Amount Trfd

Structure of txtfilename2 = "C:\TRIAL\2-XYZ.TXT" being OUTPUT

AMOUNT 75000.00
ID NUMBER 22229400331
STATUS
AMOUNT Trfd

Below is the Desired output of C:\TRIAL\2-XYZ.TXT

AMOUNT 75000.00
ID Number 22229400331
STATUS Amount Trfd

Thanks for inputs so far you have shown

NimishK

 

[bebo021999]

Dim Strline As String
If InStr(1, Strline, "Status Amount") Then
    Strline = Replace(Strline, "Status Amount", vbCrLf & "STATUS Amount")
Else
    Strline = Replace(Replace(Replace(Strline, "Amount", vbCrLf & "AMOUNT"), "ID Number", vbCrLf & "ID NUMBER"), "Status", vbCrLf & "STATUS")
End If

 

[Alex Blakenburg]

That helps a lot and puts a totally different complexion on it.
See if the below works for you.

Public Sub Whole_Code_Mod_v02()

    Dim Strline As String
    Dim strDataTxtFile As String
   
    Dim txtfilename As String, txtfilename2 As String
    Dim lineCount As Long
    Dim arrReplace As Variant
    Dim i As Long
   
    arrReplace = Array("Amount", "ID Number", "Status")
   
    txtfilename = "C:\TRIAL\1-ABC.TXT"
    txtfilename2 = "C:\TRIAL\2-XYZ.TXT"
   
    Dim txtfileno As Integer
    txtfileno = 1
    Open txtfilename For Input As txtfileno
    Strline = Input(LOF(txtfileno), txtfileno)                                              ' Read file entire file as a single string
   
     Open txtfilename2 For Output As #2

    Strline = vbCrLf & Strline                                                              ' Add leading vbCrLf for replace consistency
    For i = 0 To UBound(arrReplace)
        Strline = Replace(Strline, vbCrLf & arrReplace(i), vbCrLf & UCase(arrReplace(i)))   ' Replace only if at start of line
    Next i
    
    Strline = Right(Strline, Len(Strline) - 2)                                              ' Remove vbCrLf added at the start of the file

    Print #2, Strline;
    Close #2
    Close #1

End Sub