IBAN notatie in excel

[HdeKteP]

Hallo,
Ik wil mijn lijst met IBAN-nummers (van mijn vereniging) in excel wat overzichtelijker hebben.

Graag zou ik de notatie als volgt willen zien:

NL72 INGB 0002 5674 22 (dus niet NL72INGB0002567422 aan elkaar).

Ik ben al bezig geweest met celeigenschappen - aangepast in excel maar krijg het daar niet voor elkaar.

Wie geeft mij raad?

[Fish]

ow .. niet aan elkaar

mid

Dat is het, ga daar maar eens op zoeken ..

[ Voor 180% gewijzigd door Fish op 31-03-2015 16:02 ]

[Speedmaster]

=LINKS(A1;4) & " " & LINKS(A1;5-3) & " " & LINKS(maak de rest zelf maar af

Bij een Engels talige versie is het left en eventueel mid

[ Voor 29% gewijzigd door Speedmaster op 31-03-2015 16:04 ]

[Fish]

Niet alles met links imho .. maar met formatting zo het ook moeten lukken

moet genoeg zijn voor de ts om wat uit te vinden

[ Voor 26% gewijzigd door Fish op 31-03-2015 16:20 ]

[F_J_K]

Fish schreef op dinsdag 31 maart 2015 @ 16:20:
Niet alles met links imho .. maar met formatting zo het ook moeten lukken

moet genoeg zijn voor de ts om wat uit te vinden

Hoe lukt het dan met formatting? AFAIK kan dat alleen met cijfers en niet met karakters. Of is dat nieuw in OFF2013?

Met mid() etc kan het in ieder geval, vereist wel een extra hulpkolom of -rij. Of VBA OnChange()

[HdeKteP]

Klinkt allemaal erg profi
Fish - Substitute - waarschijnlijk een functie? Lukt niet
Speedmaster - dit is een verwijzing naar een andere kolom - ik wil geen extra kolom

Wat wil ik?
In een veld van kolom bankrekeningnummer tik ik bij een lid zijn/haar bankrekeningnummer (b.v. NL72INGB0002567422) in. Na dat ik op enter heb gedrukt wil ik dat excel dan in dat veld NL72 INGB 0002 5674 22 aangeeft.

[Bastien]

Helaas zit je denk ik vast aan of een extra kolom of het gebruiken van een VBA scriptje. Formatting werkt afaik alleen met nummers dus dat werkt helaas niet.

Aangezien je geen extra kolom wilt zal het dus dat laatste worden. Hier zijn met gebruik van Google wel voorbeelden voor te vinden.

[F_J_K]

Tot je de concepten van iets als wat Speedmaster in "IBAN notatie in excel" zegt niet kent, dan wil je m.i. ook niet met Excel aan de gang met financiële gegevens. Dat bedoel ik overigens niet vervelend.

Zonder formules (en hulpkolommen/-rijen) of VBA an het -voor zover ik weet- niet. Behalve als ik achterloop en fish gelijk heeft. Check daarvoor How to control and understand settings in the Format Cells dialog box in Excel

[Fish]

mmm met alleen nummers lukt het me wel (#### #### #### #### ##) .. maar niet alfanumeriek.. doet excel kennelijk niet

ik denk dat je toch een beetje vastzit aan mid mid mid ..

[ Voor 17% gewijzigd door Fish op 31-03-2015 19:20 ]

[HdeKteP]

LINKS, RECHTS en MIDDEN (Nederlands) ken ik maar zoals ik al schreef wil niet aan een extra kolom.
Hoe is de celnotatie van een postcode ... die zie je vaak veranderen na het intikken van b.v. 1234AA in 1234 AA? (numeriek en alfanummeriek)

[F_J_K]

In het Engels wel: https://support.office.co...87-46bd-abac-df3c1bd01ceb

Maar ik zou dus suggereren VBA te leren

[HdeKteP]

Een goede suggestie F_J_K 😄

[Anoniem: 586752]

Als A2:A100 het bereik is waarin je Ibannummers wil invoeren, klik dan met de rechtermuisknop op de tab met de naam van het blad (bv. Blad1), kies "Programmacode weergeven" en plaats in het rechtervenster deze code:

code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim istr As String, jstr As String, kstr As String, lstr As String, mstr As String, nstr As String
If Not Intersect(Range("A2:A100"), Target) Is Nothing Then
If Len(Target) = 18 Then
istr = Target.Value
jstr = Left(istr, 4): kstr = Mid(istr, 5, 4): lstr = Mid(istr, 9, 4)
mstr = Mid(istr, 13, 4): nstr = Right(istr, 2)
Target.Value = jstr & " " & kstr & " " & lstr & " " & mstr & " " & nstr
End If
End If
End Sub

[HdeKteP]

alfas Top hoor!!

Dit is het en werkt perfect!

Ontzettend bedankt voor jouw hulp!

[Anoniem: 586752]

Dank je,
graag gedaan.

[Trommelrem]

Microsoft Connect.

Voor toekomstige Excel versies zou een IBAN functionaliteit erg welkom zijn. Wellicht kun je hier een feature request droppen.

[Anoniem: 1256172]

Ik ben aan nog een redelijke leek in VBA Excell.
Daar ik dit een prachtige oplossing vond heb ik dit overgenomen in mijn bestand.
Probleem is ik heb verschillende IBAN nummers ( verschillende landen )
Ik kom niet verder dan twee, met meerdere draai ik in de soep.
Enige hulp zou van harte welkom zijn.
Bij voorbaat dank.

Ik heb dit bijgevoegd in de code

code:

Visual Basic:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim istr As String, jstr As String, kstr As String, lstr As String, mstr As String, rstr As String, nstr As String
If Not Intersect(Range("V2:V20"), Target) Is Nothing Then
If Len(Target) = 16 Then
istr = Target.Value
jstr = Left(istr, 4): kstr = Mid(istr, 5, 4): lstr = Mid(istr, 9, 4)
mstr = Mid(istr, 13, 4)
Target.Value = jstr & " " & kstr & " " & lstr & " " & mstr & " " & nstr
End If
If Not Intersect(Range("V2:V20"), Target) Is Nothing Then
If Len(Target) = 18 Then
istr = Target.Value
jstr = Left(istr, 4): kstr = Mid(istr, 5, 4): lstr = Mid(istr, 9, 4)
mstr = Mid(istr, 13, 4): nstr = Right(istr, 2)
Target.Value = jstr & " " & kstr & " " & lstr & " " & mstr & " " & nstr
End If
If Not Intersect(Range("V2:V20"), Target) Is Nothing Then
If Len(Target) = 22 Then
istr = Target.Value
jstr = Left(istr, 4): kstr = Mid(istr, 5, 4): lstr = Mid(istr, 9, 4)
mstr = Mid(istr, 13, 4): rstr = Mid(istr, 18, 4): nstr = Right(istr, 2)
Target.Value = jstr & " " & kstr & " " & lstr & " " & mstr & " " & rstr & " " & nstr
End If
End If
End If
End If
End Sub

En dit is het resultaat:

NL72 ING B 00 02 5 42 2 22
BE59 7509 5481 6026
DE12 1234 5678 8765 3219 99

[ Voor 66% gewijzigd door F_J_K op 19-09-2019 17:17 . Reden: code tags ]

[F_J_K]

Welkom @Anoniem: 1256172

In de regel zie ik liever een nieuw topic dan het omhoogschoppen van een stokoud topic Maar hier is het prima ontopic denk ik, het gaat over de code. Ik heb wel je code wat leesbaarder gemaakt door code tags toe te voegen. Je zou eea nog beter leesbaar kunnen maken door spaties in te voegen voor indenting.

Wat is concreet je vraag?

[Anoniem: 1256172]

De code die ik hierboven geplaatst heb is deze die ik heb aangepast van het eerdere antwoord in deze topic.
Deze was alleen voor de Nederlandse iban nummers, maar ik zou dit willen gebruiken voor de Belgische, Franse, Nederlandse en Duitse iban nummers.
Ik ben al begonnen, en met twee gaat het goed, maar vanaf ik er een derde inzet draait het in de soep. Zoals bij mijn code voorbeeld, daar word de opmaak van de Nederlandse notatie volledig uit verband getrokken.
Ik kan niet meer dan om hulp vragen.
Ben redelijk thuis in excel maar in VBA nog een kleuter.
Ik ben ondertussen al aan zelfstudie begonnen met het boek “ Programming excel with VBA “ maar eer ik dat uit heb zijn we wel al een paar maand verder denk ik.

[F_J_K]

Je wilt alle verschillende varianten eerst ‘op een papiertje’ zetten en zien wat de patronen zijn. Bijvoorbeeld: eerst vier karakters die altijd samen blijven, dan misschien letters die samen moeten blijven, dan op te delen cijfers.

Die letters zijn dan tricky want soms 0, soms 3, soms 4 en misschien ook andere aantallen. Dan zou ik zeggen: pak de eerste vier op de huidige manier. Het volgende blok kan je doen met een viertal If statements: als volgende is een letter dan ..., als volgende is een letter dan ... etc. Net zoals er nu al een aIF lengte 16, IF lengte 22 is.

Mooier en makkelijker is IF vierde karakter is een letter dan.. ELSE als derde karakter is een letter ELSE etc. En dan steeds herhalen wat je doet bij vier, drie, twee, een, nul letters.

Mooier is een FOR loop maken: teller i = 4 (je hebt 4 letters gehad), dan als karakter i + 1 een letter is dan voeg je die bij het volgende blok, dan i = i+1 en herhaal. Zodra je voorbij de letters bent ga je op de bekende manier de cijfers weer splitsen.

[biomass]

Er is niet zo veel mis met die code, er wordt wat herhaald en je gebruikt de & operator. De fragmenten kun je gewoon plakken met de + operator ( tekst + tekst geeft tekst)
In plaats van losse variable namen voor de fragmenten kun je één string array gebruiken.
Met een select case statement voor de lengte van de invoer hoef je ook maar een keer te kijken of je in de IBAN range zit.
En verder zijn de eigenaardigheden van IBAN nummers eigenlijk het belangrijkst, zoals @F_J_K al aangeeft.

[Anoniem: 1256172]

biomass schreef op vrijdag 20 september 2019 @ 08:07:
Er is niet zo veel mis met die code, er wordt wat herhaald en je gebruikt de & operator. De fragmenten kun je gewoon plakken met de + operator ( tekst + tekst geeft tekst)
In plaats van losse variable namen voor de fragmenten kun je één string array gebruiken.
Met een select case statement voor de lengte van de invoer hoef je ook maar een keer te kijken of je in de IBAN range zit.
En verder zijn de eigenaardigheden van IBAN nummers eigenlijk het belangrijkst, zoals @F_J_K al aangeeft.

Kan je dat a.u.b. verduidelijken met een voorbeeld, of dit aanpassen in mijn code.
Zoals al eerder vermeld, ik ben nog redelijk blue in VBA code.
Ik doe mijn best

Dit is eigenlijk wat ik uiteindelijk wil:

BE59 7509 5481 6026
NL72 INGB 0002 5674 22
DE48 1234 5678 9012 3456 00
FR14 2004 1010 0505 0001 3M02 606

[ Voor 9% gewijzigd door Anoniem: 1256172 op 20-09-2019 09:49 . Reden: Spelling fout ]

[Lustucru]

Nah, er is één ding goed mis met die code. Target.value=[waarde] triggert zelf ook weer het worksheet_onchange event. Dus een nederlandse iban wordt eerst verlengd van 18 naar 22 tekens en gaat vervolgens nog een keer door de molen voor het duitse iban. Het resultaat: veel te veel spaties.

Ook de opbouw kan netter:

Visual Basic:

Option Explicit
 'deze flag zorgt ervoor dat hij maar één keer getriggerd wordt
Private onIt As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
    'als macro al loopt: doe niks
    If onIt Then Exit Sub   
    'onthoud dat de macro al loopt
    onIt = True  
    'je hoeft maar één keer te controleren of de wijziging in juiste range zit
    If Not Intersect(Target, Range("A1:A3")) Is Nothing Then
        'onderscheid adhv de eerste twee letters welk land het is
        Select Case UCase(Left(Target.Value, 2))
      'splitsen apart, per land éen caseblok. Je geeft een array mee van het aantal bij elkaar horende tekens.
        Case "NL"
            Target.Value = Split(Target.Value, Array(4, 4, 4, 2)) 
        Case "BE"
            Target.Value = Split(Target.Value, Array(4, 4, 4))
        Case Else
            'geef duidelijk aan dat je de iban niet herkende
            Target.Value = Target.Value & "-?!"
        End Select
    End If
    onIt = False 'klaar voor de volgende
    
End Sub

'het eigenlijke tussenvoegen van spaties adhv een array van getallen
Private Function Split(source As String, format As Variant) As String
     Dim i As Integer, result As String, start As Integer
     start = 1
     'met een loopje, lekker flexibel
     For i = 0 To UBound(format, 1)
        result = result + Mid(source, start, format(i)) + " "
        start = start + format(i)
     Next i
     'haal overbodige spaties weg en geef de string terug
     Split = Trim(result) 
End Function

Omdat alles in blokken van vier wordt opgesplitst zijn die case statements strict genomen niet eens nodig.
Maar voor de helderheid (en als je bv nog een controle wilt toevoegen op correcte lengte) wel handiger. Bijvoorbeeld:

Visual Basic:

Private Function Split(source As String, format As Variant) As String
     Dim i As Integer, result As String, position As Integer

     For i = 0 To UBound(format, 1)
        result = result + Mid(source, position + 1, format(i)) + " "
        position = position + format(i)
     Next i
    
    'check of de input de juiste lengte had. Je zou ook bv de 11-proef in kunnen bouwen etc.
    If position = Len(source) Then
        Split = Trim(result)
    Else
        Split = source & " - lengte onjuist."
    End If
        
End Function

[ Voor 22% gewijzigd door Lustucru op 20-09-2019 10:04 ]

[biomass]

@Lustucru Niet bij stilgestaan, maar die schaar ik maar even onder eigenaardigheden. Als de uitvoer aan de invoercondities gaat voldoen, wordt het inderdaad een rommeltje
Ik zou die private function niet Split noemen, het ding doet wat anders dan VBA.Split . IBANFormat o.i.d

[Anoniem: 1256172]

@Lustucru

Dit werkt uitermate uit de kunst.
Ik weet niet hoe ik u kan bedanken.
Ik heb in ieder geval door, dat het nog een hele tijd zal duren eer dat ik zover ben in VBA.
Maar ik blijf oefenen.

Nogmaals, duizendmaal dank.

[Jorgen]

Als ik het zo zie, dan varieert weliswaar per land de lengte van het IBAN, maar is op Israël na, het format overal hetzelfde. Landcode, controlegetal, bankcode, rekeningnummer. Is het dan niet simpeler om gewoon van links naar rechts alles in blokken van 4 te verdelen, zonder verder rekening te hoeven houden met de lengte, of het precieze landformat?

[pauluzz178]

Anoniem: 586752 schreef op donderdag 2 april 2015 @ 14:26:
Als A2:A100 het bereik is waarin je Ibannummers wil invoeren, klik dan met de rechtermuisknop op de tab met de naam van het blad (bv. Blad1), kies "Programmacode weergeven" en plaats in het rechtervenster deze code:

code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim istr As String, jstr As String, kstr As String, lstr As String, mstr As String, nstr As String
If Not Intersect(Range("A2:A100"), Target) Is Nothing Then
If Len(Target) = 18 Then
istr = Target.Value
jstr = Left(istr, 4): kstr = Mid(istr, 5, 4): lstr = Mid(istr, 9, 4)
mstr = Mid(istr, 13, 4): nstr = Right(istr, 2)
Target.Value = jstr & " " & kstr & " " & lstr & " " & mstr & " " & nstr
End If
End If
End Sub

Dank, ook handig gebruik van kunnen bij een declaratieformulier!

[F_J_K]

pauluzz178 schreef op dinsdag 25 mei 2021 @ 22:02:
[...]

Dank, ook handig gebruik van kunnen bij een declaratieformulier!

Schop ajb geen oude topics omhoog voor een bedankje, daar zijn de duimpjes voor. Nu bestaat het risico dat mensen niet door hebben dat het een oud topic is en gaan meedenken. En da's zonde van hun tijd

Ik sluit dit topic wel een tijdje om dat risico weg te nemen.