Office: Format für Vorwahlen

Hallo zusammen!
Habe ein eher großes problem,denke ich^^.
und zwar muss ich spalten mit telefonnummern(geschäftl./privat/fax) und über 22000 datensätze eine einheitliche form erstellen. also +49(vorwahl ohne 0 am anfang)nummer. bei hdy halt +49(171)nummer. in den zellen mit den nummern sind auch teilweise buchstaben drin, wie NEU oder Sohn usw.
habe anhand einer vorwahl-ort tabelle jetzt jeden eine vorwahl korrekt vergeben,aber da wo zwar ort z.b Berlin drin steht aber dahinter nur hdynr existiert, macht er logischerweise +49(30)0171xxxxxx und da soll er halt nicht machen. dann gibt es fälle, wo die korrekte vorwahl schon drin steht aber durch meine formatierung halt dann so +49(vorwahl)+49(vorwahl)xxxxxx aussieht. habe schon rumgedüftelt mit LINKS,RECHTS, ERSETZEN usw. aber nix funktioniert. hoffe ihr könnt mir da weiter helfen. Danke euch. Gruß Nimus

 

nur wenige sehen Deine Datei.

 

was meinst du damit?

 

hi Nimus, kannst du nicht mal (d)eine (beispiel)datei hochladen?

 

Du siehst Deine Datei und viele Leute die hier Antworten sehen Sie nicht. Ein Nachbau sieht meist anders aus als das Original.

 

die war zu groß^^ als ich die hochladen wollte^^ich versuchs nochmal oder specke sie ein wenig ab

 

so, danke euch, hab sie extrem abgespeckt und hochgeladen, danke euch

 

Hallo,

hier mal ein Versuch:


Gruß

Edgar

 

re

super, erstmal so funktioniert es, aber bei nummern die leerzeichen enthalten, kopiert er einfach nur die nummer ohne neues format und vorwahl. ka woran es liegt. auf jeden fall geile arbeit von dir und danke für deine bemühungen und die damit verbundene zeit. mal schaun woran es liegt mit den leerzeichen und so.

Gruß

 

Hallo,

das ist das Dumme, wenn nur XXXX in den Zellen steht, man kann nicht prüfen, ob es klappt.

Gruß

Edgar

 

re

Hallo!
Hab mal eine Tabelle genommen, welche nummern beinhalten, vlt klappts ja dann dadurch. danke dir schonmal.


LG Nimus

 

Hallo,

die Formel ist korrigiert:

Code:

=WENN(UND(LINKS(Tabelle1!F2;2)<>"01";ISTZAHL(WECHSELN(Tabelle1!F2;" ";"")*1));"+49("&WECHSELN(INDEX(Tabelle3!$A:$A;VERGLEICH($E2;Tabelle3!$B:$B;0));"0";"";1)&")"&WECHSELN(Tabelle1!F2;" ";"");WENN(LINKS(GLÄTTEN(Tabelle1!F2);1)="+";WECHSELN(Tabelle1!F2;" ";"");WENN(LINKS(GLÄTTEN(Tabelle1!F2);2)="01";"+49("&TEIL(GLÄTTEN(Tabelle1!F2);2;3)&")"&TEIL(GLÄTTEN(WECHSELN(Tabelle1!F2;" ";""));5;99);WECHSELN(Tabelle1!F2;" ";""))))

Es wäre aber an Dir, die gravierenden Fehler in der Tabelle 3 zu beheben!
1. Orte mit Bad stehen in zwei Zellen, damit läuft der Vergleich in einen Fehler.
2. Ortsnamen mit ß finden sich in Deutschland nicht so häufig wie in Deiner Tabelle (Essen, Düsseldorf).
Oe ist auch kein Ö: Oer-Erkenschwick
3. Es gibt viele Orte mit einer Kennzeichnung wie (Thür.), Das steht bei Dir in zwei Zellen. Tödlich für den Vergleich!

Hier noch zwei Formate für nicht korrekt gefüllte Zellen:

Arbeitsblatt mit dem Namen 'Tabelle2'
	F
15	DIENSTL.2131992
38	#NV

Zelle	Formel
F15	=WENN(UND(LINKS(Tabelle1!F15;2)<>"01";ISTZAHL(WECHSELN(Tabelle1!F15;" ";"")*1));"+49("&WECHSELN(INDEX(Tabelle3!$A:$A;VERGLEICH($E15;Tabelle3!$B:$B;0));"0";"";1)&")"&WECHSELN(Tabelle1!F15;" ";"");WENN(LINKS(GLÄTTEN(Tabelle1!F15);1)="+";WECHSELN(Tabelle1!F15;" ";"");WENN(LINKS(GLÄTTEN(Tabelle1!F15);2)="01";"+49("&TEIL(GLÄTTEN(Tabelle1!F15);2;3)&")"&TEIL(GLÄTTEN(WECHSELN(Tabelle1!F15;" ";""));5;99);WECHSELN(Tabelle1!F15;" ";""))))
F38	=WENN(UND(LINKS(Tabelle1!F38;2)<>"01";ISTZAHL(WECHSELN(Tabelle1!F38;" ";"")*1));"+49("&WECHSELN(INDEX(Tabelle3!$A:$A;VERGLEICH($E38;Tabelle3!$B:$B;0));"0";"";1)&")"&WECHSELN(Tabelle1!F38;" ";"");WENN(LINKS(GLÄTTEN(Tabelle1!F38);1)="+";WECHSELN(Tabelle1!F38;" ";"");WENN(LINKS(GLÄTTEN(Tabelle1!F38);2)="01";"+49("&TEIL(GLÄTTEN(Tabelle1!F38);2;3)&")"&TEIL(GLÄTTEN(WECHSELN(Tabelle1!F38;" ";""));5;99);WECHSELN(Tabelle1!F38;" ";""))))

Zelle	bedingte Formatierung...	Format
F15	1: =LINKS(F15;3)<>"+49"	abc
F15	2: =TYP(F15)=16	abc
F38	1: =LINKS(F38;3)<>"+49"	abc
F38	2: =TYP(F38)=16	abc

<table><tr><td>Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg</td></tr></table>[/parsehtml]
Gruß

Edgar

 

re

Hallo!

Also zum ersten: voll genial von dir, klappt wunderbar, bin voll begeistert wie ein kleines schulkind^^. nice von dir und hochachtung.
also wenn ich deine formeln so anschau, weiß ich, dass ich nicht viel drauf habe was excelformeln angeht, dachte bis eben das ich normal wissen habe,lol, aber naja, habsch wohl nicht^^. deswegen frage ich dich wegen den fällen F15 & F38. ka wat ich da machen muss. die formeln sind ja die selben wie auch in den anderen zellen,ka wo da der unterschied ist. achja, kann man irgendwo noch einbauen, dass wenn die vorwahl mit der nummer, in der form 03451234566, man rausnehmen kann, da nun in solchen fällen +49(345)03451234566 steht? ja und das mit den buchstaben am anfang bzw am ende funktioniert noch nicht mit der vorwahl(BH03012345566 o. 0304235556BC). danke dir bis dahin viel mals und ziehe meinen hut vor dir^^.

Gruß Nimus

 

Hallo,

wenn Du die bedingte Formatierung einbaust, dann siehst Du diese Zellen sofort und kannst von Hand nacharbeiten. Was den Fehler in F38 betrifft, da findet die Formel den Ort nicht, weil es den in Deiner PLZ-Liste nicht gibt. Also auch von Hand nacharbeiten. Es gäbe evtl. noch die Möglichkeit, die Buchstaben zu entfernen, aber dann wäre die Formel nochmal so lang.
Hier noch die Korrektur die gefehlt hat:

Code:

=WENN(UND(LINKS(Tabelle1!F2;2)<>"01";ISTZAHL(WECHSELN(Tabelle1!F2;" ";"")*1));"+49("&WECHSELN(INDEX(Tabelle3!$A:$A;VERGLEICH($E2;Tabelle3!$B:$B;0));"0";"";1)&")"&WECHSELN(Tabelle1!F2;" ";"");WENN(LINKS(GLÄTTEN(Tabelle1!F2);1)="+";WECHSELN(Tabelle1!F2;" ";"");WENN(LINKS(GLÄTTEN(Tabelle1!F2);2)="01";"+49("&TEIL(GLÄTTEN(Tabelle1!F2);2;3)&")"&TEIL(GLÄTTEN(WECHSELN(Tabelle1!F2;" ";""));5;99);WECHSELN(Tabelle1!F2;" ";""))))

Gruß

Edgar

 

re

Hallo!
irgendwie verändert sich nix mit der neuen formel. sehe auch keinen unterschied zu der vorigen formel^^. bekommt man nicht noch irgendwie wenn in der nummer die vorwahl schon drin steht die vorher raus bevor das neue format angewandt wird? also +49(345)0345123456 und daraus halt normales format.

Gruß Nimus