Office: (Office 2013) IBAN in Excel automatisch berechnen

Hallo zusammen, ich benötige Hilfe bei der Automatisierung der IBAN in einer Tabelle. Ich möchte in einer Zelle aus der vorhandenen Kontonummer und BLZ, die IBAN automatisch erzeugen. Dabei soll die Anzeige der IBAN in folgendem Format erscheinen: DE00 0000 0000 0000 0000 00. Zusätzlich möchte ich in einem weiteren Feld dei zugehörige BIC automatisch anzeigen lassen, wenn das geht.
Kann mir jemand helfen ?
Danke im voraus und einen schönen Tag.
Rolf

 

Hi,

In A2 steht die BLZ in B2 steht die Kontonummer und in C2 dann die Formel für die IBAN:

="DE69 "&TEXT(A2&B2;"0000 0000 0000 0000 00")

Nach welchem Muster die BICs erstellt werden weiß ich nicht.

 

Hola,

http://www.excelformeln.de/formeln.html?welcher=482

Gruß,
steve1da

 

steve1da Lösung ist die bessere, ich wusste nicht wie die Prüfziffern zustande kommen.

 

Hallo Icebreak, danke für Deine Antwort. Deine Lösung funktioniert genauso wie ich es brauche. Jedoch bringt das Berechnungsergebnis eine falsche Prüfziffer zurück. Da muß ich nochmal schauen wie ich das lösen kann. Die Lösung von steve1da funktioniert zwar, bringt aber ein komplett falsches Ergebnis.
Gruß Rolf

 

Hola,

was denn nun? Zeig doch mal eine Beispieldatei mit Wunschergebnis.

Gruß,
steve1da

 

Hallo steve, danke für Deine Hilfe. Ich habe es getestet und leider kommt ein ganz anderes Ergebnis zurück. Auch Dein Vorschlag für die Prüfung bringt nur #WERT! zurück. Die vorgeschlagene Lösung von Icebreak funktioniert bis auf die Prüfziffer. Diese wird nicht korrekt zurück gegeben. Trotzdem danke
Gruß Rolf

 

[TABLE="width: 404"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Konto- Nummer:[/TD]
[TD]8565624520[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B L Z:[/TD]
[TD]31010833[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]IBAN[/TD]
[TD]BIC[/TD]
[/TR]
[TR]
[TD]DE76 3101 0833 8565 6245 20 [/TD]
[TD]CCBADE31XXX[/TD]
[/TR]
</tbody>[/TABLE]

 

[TABLE="width: 265"]
<colgroup><col></colgroup><tbody>[TR]
[TD]DE69 3101 0833 8565 6240 00 >>> Das ist die Formel von Icebreak [/TD]
[/TR]
[TR]
[TD="align: center"]#WERT! > Das ist dein Vorschlag für die Prüfung

[/TD]
[/TR]
</tbody>[/TABLE]

 

="DE69 "&TEXT(C47&C45;"0000 0000 0000 0000 00")
Formelvorschlag von ICEBREAK

 

Hola,

die Formel aus dem Link ergibt mit deinen Daten bei mir:

Code:

="DE"&TEXT((98-REST((62*(1+REST(A2;97))+27*REST(A1;97));97));"00")&A2&TEXT(A1;"0000000000")

#WERT erscheint, wenn deine Daten als TEXT vorliegen - das kannst aber nur du beurteilen.

Gruß,
steve1da

 

Hi Steve, vielen Dank für Deine Hilfe, das Ergebnis ist jetzt korrekt. Den Fehler mit #Wert habe ich auch gefunden, da war der Bezug auf ein anderes Feld gelegt statt auf die Ziffern. Wie Kann ich mit deinem Vorschlag denn die Anzeige im Block erscheinen lassen (wie der Volrschlag von Icebreak) statt in einer komplettzusammenhängenden Ziffer?

 

Hola,

Code:

="DE"&TEXT((98-REST((62*(1+REST(A2;97))+27*REST(A1;97));97));"00")&A2&TEXT(A1;"0000 0000 0000 0000 00")

Gruß,
steve1da

P.S. Farbig und kursiv sieht nicht schön aus ;-)

 

[TABLE="width: 404"]
<tbody>[TR]
[TD="colspan: 2"]DE7631010833 0000 8565 6245 20

Das ist das Ergebnis mit deinem letzten Vorschlag.
Keine Trennung nach "DE76"; keine Trennung nach dem ersten 4er-Block und 4 Nullen zu viel. Ansonsten ok. [/TD]
[/TR]
</tbody>[/TABLE]

 

Hola,

das wird über eine Zellformatierung so nicht möglich sein. Versuch mal das (IBAN steht in C1):

Code:

=LINKS(C1;4)&" "&TEIL(C1;5;4)&" "&TEIL(C1;9;4)&" "&TEIL(C1;13;4)&" "&TEIL(C1;17;4)&" "&RECHTS(C1;2)

Gruß,
steve1da