0

I'm currently trying to automate our accounting process. From the bank, I download a .csv file that I'd like to transform in a certain way. I'm also attempting to eliminate all IBAN and BIC numbers from the document as they're not necessary for the accounting process.

Now, every IBAN and BIC follows a certain pattern. How do I replace all strings with a certain pattern (i.e. XX00000000000000 and DEXXXXXXXXX) or at least how do I find them using Visual Basic? I'm familiar with the .replace method already, I just cannot manage to find the string.

Thank you so much in advance!

dnitsch
  • 13
  • 5
  • The Replace() will find and replace all instances in the entire file: `csvFile = Replace(csvFile, IBAN1, IBAN2): csvFile = Replace(csvFile, BIC1, BIC2)`, just use `vbBinaryCompare` for exact match, or `vbTextCompare` for case-insensitive match – paul bica Nov 02 '17 at 16:20

1 Answers1

0

I think this should help you: RegEx

An another way could be to load each textline of the .csv file into an array and just Loop through them.
Something like:

Dim Textline() As String   'array
Dim IBAN As String
Dim posIBAN As Integer
Dim iban_length As Integer

textlinelength = UBound(Textline)
iban_length = 22

For i = 0 To textlinelength
  If InStr(Textline(i), "DE") Then        'if array contains DE
    posIBAN = InStr(Textline(i), "DE")   'find position of IBAN
    IBAN = Mid(Textline(i), posIBAN, iban_length)  'get IBAN
    Textline(i) = Replace(Textline(i), IBAN, "") 'replace IBAN with ""
  End If
Next i

After that you could create a new file and write the arrays in it.
So you would have a IBAN-free txt-file

PS: Is there a way to properly link other questions/answers?

rosi97
  • 243
  • 3
  • 18
  • Thanks! Looks exactly like what I need, but apparently the RegEx Plugin doesn't exist for Mac OS. – dnitsch Nov 02 '17 at 09:16