IBAN zerlegen

[Couch81]

Hallo zusammen,

meine Formel funktioniert zwar, ich frage mich nur, ob es ohne VBA möglich ist, sie zu verkürzen.

Zelle A1 enthält die unstrukturierte IBAN (internationale Kontonummer):
z.B. DE99888877770033200000  und soll zu DE99 8888 7777 0033 2000 00 werden

Code:

=TEIL(A1;1;4)&" "&TEIL(A1;5;4)&" "&TEIL(A1;9;4)&" "&TEIL(A1;13;4)&" "&TEIL(A1;17;4)&" "&TEIL(A1;21;4)

Von links beginnend soll die IBAN nach jedem 4. Zeichen ein Leerzeichen erhalten um sie in Form (Lesbarkeit) zu bringen.

Hätte man einen String von nicht nur 22 Zeichen sondern z.B. 200 Zeichen, wäre meine Formel doch etwas sperrig.
Daher meine Frage eher aus Interesse

Danke und Grüße
Sascha

[Elex]

Hi

evtl. so in die Richtung.

Länge A1 bekannt.
b1   =LINKS(A1;2)&TEXT(TEIL(A1;3;200);"?? ???? ???? ???? ???? ??")

Länge A2 unbekannt. Ergebnis anpassen durch die Anzahl der Letzten ?
b2   =LINKS(A2;2)&GLÄTTEN(TEXT(TEIL(A2;3;200);WIEDERHOLEN("???? ";15)&"??"))

Länge A3 unbekannt. Von Links immer mit vierer Block beginnen.
b3   =LINKS(A3;2)&GLÄTTEN(TEXT(TEIL(A3;3;200);WIEDERHOLEN("???? ";15)&WIEDERHOLEN("?";REST(LÄNGE(A3);4))))

Bei mir in Excel 2007 ist aber schon bei 15 Wiederholungen schluss, also 75 Zeichen.

[Fennek]

geht das nicht auch mit NumberFormat?

[GMG-CC]

Moin,
mit 2016 ist das wirklich wesentlich komfortabler machbar als mit "ellenlangen" Formeln ...
Daten | Abrufen und transformieren und dann Spalte teilen|nach 4 Zeichen
Alles als Text formatieren
Anschließend alle Spalten wieder zusammenfügen mit Leerzeichen als Trenner.
Feddisch.  :05: Ohne eine einzige (geschriebene) Formel.

[LCohen]

Zu bedenken außerdem:

EU-national gibt es verschiedene Nummernsysteme im 18stelligen Nummernteil der 22stelligen IBAN, die mit 4 Nationalitäts- und Kontrolzahl-Stellen beginnt. Also nicht überall BLZ 8 + Kto. 10, wie bei uns.

18 Stellen lassen sich nicht als Zahl formatieren.

[losgehts]

Hallo Günther,

manchmal habe ich fast das Gefühl, du willst mir eine neue Excelversion verkaufen (da du so häufig Lösungen mit PowerQuery vorschlägst). Dein hier genannter Lösungsweg geht aber auch schon mit Excel 2003 (da weiß ich es sicher) und bestimmt auch schon frühere Versionen:
(Extras =>) Daten => Text in Spalten => Feste Breite ....

Grüße, Ulrich

[Jockel]

Hallo Günther,

... Lösungen mit PowerQuery ... Dein hier genannter Lösungsweg geht aber auch schon mit Excel 2003 (da weiß ich es sicher) und bestimmt auch schon frühere Versionen:
(Extras =>) Daten => Text in Spalten => Feste Breite ....

Grüße, Ulrich

HalloUllrich, dieser vorgeschlagene Weg geht nicht in XL2007 und schon gar nicht vorher...!!! Das, was du daraus machst, ist etwas völlig anderes. Günthers Variante (ab XL2010 verfügbar) macht etwas anderes, ist flexibel, nachdem man die Schritte für PQ eingegeben oder angeklickt hat. Hast du bei "deiner" Variante trennen und danach mit Trennzeichen zusammenfügen können in einem Rutsch..? Sicher nicht. Aber danke für deine Anfrage, ich habe eine kleine Übung daraus für mich mit PQ gemacht und sie erweitert. (Mit join zu allen Banken in Deutschland Bankname, Ort, PLZ und BIC usw. - alles ohne Formeln...)

[GMG-CC]

Moin Ulrich,
es kann naturgemäß nicht schaden, eine aktuelle Version einer genutzten Software zu haben.   
Und: Ich verkaufe keine kommerzielle Software (beispielsweise Office), empfehle diese aber meinen Kunden. 

Und was Power Query angeht: Der TE hat Excel 2016 und da ist es hilfreich, sich mit dem (neuen) Möglichkeiten auseinanderzusetzen. Richtig, ich bin einer der (noch) Wenigen, die Power Query forcieren. Aber ich denke, dass dieses Tool ein ähnliches Schattendasein fristet wie (noch) die Aggregat-Funktion. Andere Lösungswege werden von vielen anderen Helfern vorgestellt; und das Gesamt-Angebot führt dazu, dass Hilfe suchende und Mitleser den für sie best geeigneten Weg finden können. Und ich finde es erheblich besser, wenn ein User durch PQ selbstständig Lösungen erstellen kann, die sonst nur durch VBA möglich wären; wobei es ja oft genug der Fall ist, dass der Code nur übertragen wird und bei anderen Anforderungen wieder das (kostenlose) Forum gefragt ist, weil nicht jeder eine Affinität zu Programmiersprachen (dafür aber andere Qualitäten) hat.

Und was das hier genannte Problem angeht: Ja klar, das geht auch mit antiquarischen Versionen des Excel, wie auch mit den Umsonst-Versionen diverser anderer Anbieter. Und das splitten ist aus meiner Sicht ja auch nicht das, was für "Normalos" problematisch ist. Das anschließende Verketten mit den Leerzeichen führt dann schon eher zu etwas komplexeren Denkprozessen, wenn die Ursprungs-Spalte mal eine andere ist.

[snb]

Ganz kompliziert:

Code:

Function F_snb(c00)
  F_snb = Format(c00, "@@@@ @@@@ @@@@ @@@@ @@@@ @@")
End Function

[RPP63]

Wobei ich (da unterschiedliche Stellen pro Land) gerne noch ein ! in den Ring werfen würde …
Siehe dazu auch:
https://www.moneytoday.ch/lexikon/iban/
Die Griechen lassen wir mal außen vor, die ticken ja in Bezug auf Zahlungsverkehr ohnehin anders …