Hallo,
ich habe das folgende Problem.Ich bräuchte eine Formel, die Telefonnummern umwandelt.
Beispiel:
In Spalte B befinden sich verschiedene Telefonnummern die mit 955 beginnen (z.B. 9552646).
Nun möchte ich in Spalte C die Telefonnummern umrechnen, so dass alle Telefonnummern die mit 955 beginnen, die Vorwahl „+49 611 404“ bekommen und der Rest der Nummer (hier 2646) hinten an gehängt wird.
In jeder Zeile befindet sich aber eine andere Nummer.
Vielen Dank schon einmal.
Hallo Galaktika,
eine etwas ungewöhnliche Frage, finde ich, da ich nicht so recht weiß, wie man über den Anfang einer Telefonnummer dessen Vorwahl herausfinden kann.
Aber: Wenn Du eine Übersetzungstabelle vorliegen hast müsste das folgender maßen funktionieren:
=Verketten(sverweis(links(ZELLE mit der Telefonnummer,3);Übersetzungstabelle!A:C;2;0);ZELLE mit der Telefonnummer;Sverweis(links(ZELLE mit der Telefonnummer;3);Übersetzungstabelle!A:C;3;0))
Die Übersetzungstabelle muss 3 Spalten haben mit
A: Die ersten 3 Ziffern der Telefonnummer
B: Die Vorwahl, die Du dann dazufügen möchtest
C: Die Nummer, die Du anhängen möchtest.
Falls Du noch Fragen hast, gerne melden.
Das kommt dem was ich gerne hätte schon sehr nahe.
Aber:
Ich habe eine ausgangstabelle die so aufgebaut ist:
Spalte A: Zusatzinfo
Spalte B: Telefonnummern mit verschiedenen „Vorwahlen“ wie z.B. 955
Spalte C: aktuell leer
Dann habe ich eine Tabelle wo eine Übersetzung der Vorwahlen drin ist (z.B. aus 955 wird 069 265)
Nun möchte ich in der ersten Tabelle in Spalte C, dass dann die vollständig umgerechnete Telefonnummer (z.B. +49 69 265 12345) erscheint.
Das 12345 ist dann in jeder Zeile eben unterschiedlich.
Oder so:
=WENN(LINKS(B1;3)=„955“;ERSETZEN(B1;1;3;"+49 611 404" & RECHTS(B1;LÄNGE(B7)-3));B1)
Also wenn in Zelle B1 die Rufnummer steht, diese Formel in Zelle C1 eingeben und nach unten ausfüllen!
Die ‚WENN-Funktion‘ sucht nach Telefonnummern, die mit „955“ beginnen!
Wenn gefunden, ersetzt die ‚Ersetzen-Funktion‘ die ersten 3 Zeichen durch den Ersatztext (in Anführungszeichen) und hängt dann die Restzeichen der Nummer (Länge(B1) ohne die ersetzten Zeichen (-3) hinten an.
Hoffe das war ein nachvollziehbarer Tipp!
Danke für eine kurze Rückmeldung und viel Spaß beim umsetzen!
Gruß Excel-Fan
Hallo Excel-Fan,
ich möchte nicht gerne „klug scheißen“, aber du hattest ja geschrieben
=WENN(LINKS(B1;3)=„955“;ERSETZEN(B1;1;3;"+49 611 404" & RECHTS(B1;LÄNGE(B7)-3));B1)
Danke für eine kurze Rückmeldung und viel Spaß beim umsetzen!
Nun sind mir bei deiner Formel ein paar Dinge aufgefallen.
1) In deiner Formel wird bei LÄNGE die Zelle B7 angegeben. Hier vermute ich mal dass du dich verschrieben hast, das soll wahrscheinlich B1 sein.
2) Außerdem ergibt deine Formel (B7 verbessert in B1) als Ergebnis in Zelle C1 +49 611 40426462646. Die Formel sollte aber nur +49 611 4042646 ergeben.
Vergleiche mal die Formel von dir:
WENN(LINKS(B1;3)=„955“;ERSETZEN(B1;1;3;"+49 611 404" & RECHTS(B1;LÄNGE(B7)-3));B1)
mit dieser Formel
=WENN(LINKS(B1;3)=„955“;ERSETZEN(B1;1;3;"+49 611 404");B1)
Und teste beide Formeln mal auf ihr Ergebnis.
3) Du hast bei deiner Formel außer Acht gelassen dass auch andere „Vorwahlen“ als 955 möglich sind, und sich dadurch auch die Übersetzungen ändern müssen. Siehe hierfür die Aussage von „Galaktika1983“ ( 17.11.2014 12:00 )
Dann habe ich eine Tabelle wo eine Übersetzung der Vorwahl en drin ist ( z.B. aus 955 wird 069 265)
Dort ist von „Vorwahlen“ (also Mehrzahl) die Rede, und die „955“ wird nur als Beispiel genannt. Also gehe ich davon aus dass es unterschiedliche Vorwahlen und unterschiedliche „Übersetzungen“ gibt, die dann auch unterschiedliche Ergebnisse in der Spalte C ergeben müssten.
Durch deine WENN Abfrage würden aber nur die Telefonnummern mit 955 aus der Spalte B in der Spalte C geändert, die Telefonnummern mit anderen Anfängen in der Spalte B blieben in der Spalte C unverändert.
Wie gesagt, es liegt mir fern hier als „Klugscheißer“ aufzutreten, aber du selber hattest ja um Rückmeldung gebeten.
Gruß
N.N
Neuer Ansatz für das Problem
Hallo Galaktika1983,
ich habe eine ausgangstabelle die so aufgebaut ist:
Spalte A: Zusatzinfo
Spalte B: Telefonnummern mit verschiedenen „Vorwahlen“ wie z.B. 955
Spalte C: aktuell leer
Dann habe ich eine Tabelle wo eine Übersetzung der Vorwahlen drin ist (z.B. aus 955 wird 069 265)
Wir wissen ja nicht wo die Übersetzung der Vorwahlen (z.B. aus 955 wird 069 265) herkommt, also woher Excel „weiß“ dass aus der Vorwahl 955 die Vorwahl 069 265 wird.
Nun möchte ich in der ersten Tabelle in Spalte C, dass dann die vollständig umgerechnete Telefonnummer (z.B. +49 69 265 12345) erscheint.
Da mir die Herkunft der neuen Vorwahl (069 265 statt 955) unbekannt ist, habe ich eine Beispieltabelle wie folgt aufgebaut.
1) In Spalte B steht die Original Telefonnummer (95512345)
2) In Spalte C steht die neue Vorwahl 069 265 (woher die kommt, und nach welchem System aus der 955 die 069 265 wird, kannst nur du wissen)
3) In Spalte D wird die Vorwahl 069 265 umgewandelt in das Format +49 69 265
4) In Spalte E wird dann die umgewandelte neue Vorwahl mit den restlichen Ziffern der original Telefonnummer zusammen gefügt angezeigt.
Siehe hier den Screenshot http://abload.de/img/excelz5ub4.jpg
Hier die entsprechenden Formeln
A2 Zusatzinfo
B2 Original Telefonnummern mit verschiedenen „Vorwahlen“ wie z.B. 955
C2 Übersetzung der Vorwahlen z.B. aus 955 wird 069 265 (Wie und woher du zu der Übersetzung kommst, kannst nur du selber wissen)
D2 Die übersetzte Vorwahl 069 265 wird in das Format +49 69 265 umgewandelt. Die Formel dafür lautet
=ERSETZEN(C2;1;1;"+49 ")
E2 Die umgewandelte Vorwahl (+49 69 265) aus D2 wird mit den Restziffern (12345) der original Telefonnummer aus B2 zusammen gefügt und als vollständig umgerechnete Telefonnummer angezeigt. Die Formel dafür lautet
=WECHSELN(B2;LINKS(B2;3);D2)
Wenn dich die Hilfsspalten C und D stören, kannst du diese nach Fertigstellung deiner Tabelle ausblenden, so dass sie zwar noch vorhanden sind, aber nicht mehr angezeigt werden.
Viel Erfolg und Gruß
N.N
