• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA: Find a pattern and replace characters from nth position

K

karthizen

New Member
Can someone please help me in finding a pattern & then replace 7 characters from the Xth position after the pattern is found.

The file will have N number of records.

There is a pattern [OAF1-REC:} in every record and from there I need to replace the ID which is present at 53 till 59th position. I have marked it bold it in the below two records. Please note the position in which OAF1-REC: appearing in the records is not uniform.

0013259117030009642800 0000440 OAF1-REC:GB00098952920000000000000000290430000000GB00098952922270300096428

0015575117030009642802 0000440 PAS-REC:0000000000000000290430000000GB000989OAF1-REC:GB00098952920000000000000000290430000000GB00098952922270400096428 P

Thanks & your help/guidance will be much appreciated.
 
Hi,

May be a very basic question. sorry about it. the file has multiple rows. how can I replace all the occurrences? Please help.
 
1) You can filldown the formula as you want.
2) If you want to change the original data then vba.
Code: 
Sub test()
    With [a1:a1000]
        .Value = Evaluate("iferror(replace(" & .Address & ",find(""OAF1-REC:""," & .Address & _
                 ")+61,7,""Your replacement""),if(" & .Address & "<>""""," & .Address & ",""""))")
    End With
End Sub
Both assuming data is in col.A, so adjust as you need.
 
I don't get error but your search item is wrong.
It should be "OAF1-REC" not "OIF1-REC:"

Code: 
Sub test()
    With [A1:A1000]
        .Value = Evaluate("iferror(replace(" & .Address & ",find(""OAF1-REC:""," & .Address & _
                 ")+61,3,""333""),if(" & .Address & "<>""""," & .Address & ",""""))")
    End With
End Sub
 
You must log in or register to reply here.
Back
Top