stringexcelvbaiban

Issue with InStr and Left to return right text from string

I am playing around with some IBAN/SWIFT codes that I have in a string in Excel VBA, but I would like to just pick the IBAN number out of the string.

Samples:
DK4520005010201483 IBAN NDEADKKKXXX
NL24ABNA0424079763 iban ABNANL2A
DE56300700100303808000 IBAN DEUTDEDD

The string(s) from above is saved in the string iban
Code:

Dim cutDownStr as String, iban as string

cutDownStr = iban
fooStr = Left(cutDownStr, (Len(cutDownStr) - InStr(cutDownStr, " ")))
MsgBox fooStr
Solution

  • Your problem is your code was cutting the length of the IBAN from the whole string. You needed to cut the length of the string AFTER the IBAN, including the space. You can do that with:

    fooStr = Left(cutDownStr, (Len(cutDownStr) - (Len(cutDownStr) - InStr(cutDownStr, " "))))