PLZ aus Textzeile mit führender Null extrahieren und als Zahl weiterverwenden

[Objektscout]

Hallo zusammen.

Folgendes Problem:

In einer Zelle mit Text steht irgendwo eine PLZ die ich auslesen möchte und mit diesem Ergebnis als Zahl eine SVERWEIS-Funktion benutzen.
Da mit dieser PLZ auch der Name des Ortes aus dem Text ausgelesen wird, kommt es bei PLZ mit führender Null zum Verlust des ersten Buchstabens.
Siehe Beispieldatei.
Wer hat eine Lösung?

MfG
Joe

[steve1da]

Hola,

Code:

{=text(text(summe.........)))*1;"00000")}

Gruß,
steve1da

[Objektscout]

Hola,

Code:

{=text(text(summe.........)))*1;"00000")}

Gruß,
steve1da

Danke für die schnelle Antwort steve1da.

Nur leider kommt das gleiche Ergebnis ohne die zusätzliche TEXT-Funktion heraus wenn ich die *1 weglasse. Da habe ich dann wieder das Problem einer Textausgabe der PLZ so wie bei deiner Formel und die kann ich nicht in einer SVERWEIS-Funktion weiterverwenden.

Ich benötige also unbedingt eine Zahl für die weitere Auswertung. Allerdings habe ich dann wieder das Problem mit dem Auslesen des Ortsnamens. Eine Umformatierung der PLZ in der Quelldatei in ein Textformat hat leider auch nichts gebracht. Komischerweise werden die PLZ mit führender Null in der SVERWEIS-Funktion erkannt aber die anderen PLZ nicht. Woran kann das liegen?

Gruß
Joe

[Rabe]

Hi,

warum muß in der SVERWEIS-Formel als Suchkriterium eine Zahl stehen und kein Text? Bei meinem Excel geht das genauso auch mit Text.

[chris-ka]

Hi,

das gibt Dir deine "Zahl" gleich als Text aus
{=TEIL(A1;MIN(FINDEN(ZEILE($1:$10)-1;A1&1234567890));ANZAHL(((TEIL(A1;MIN(FINDEN(ZEILE($1:$10)-1;A1&1234567890));SPALTE(1:1))*1)>=0)*1))}

ist um einiges kürzer als deine Variante ;)

  Beispieldatei25_10.xlsx (Größe: 9,46 KB / Downloads: 3)

[BoskoBiati]

Hi,

da gibts doch was von Excelformeln:

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C
1	Dies ist ein immer unterschiedlich langer Text mit 06731 Ortsname	Ortsname	06731

Zelle	Formel
B1	=TEIL(A1;SUCHEN(C1;A1)+6;99)
C1	{=TEXT(VERWEIS(9^99;1*TEIL(A1;MIN(WENN(ISTZAHL(1*TEIL(A1;SPALTE(1:1);1));SPALTE(1:1)));SPALTE(1:1)));"00000")}

Achtung, Matrixformel enthalten!

Die geschweiften Klammern{} werden nicht eingegeben.

Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

[Objektscout]

Hi,

warum muß in der SVERWEIS-Formel als Suchkriterium eine Zahl stehen und kein Text? Bei meinem Excel geht das genauso auch mit Text.

Hallo Ralf.

Habe den Fehler gefunden. Die Umwandlung der PLZ in der Quelldatei war fehlerhaft. Jetzt funktionierst es. Danke für deine Nachricht.

Gruß Joe

[Objektscout]

Hi,

das gibt Dir deine "Zahl" gleich als Text aus
{=TEIL(A1;MIN(FINDEN(ZEILE($1:$10)-1;A1&1234567890));ANZAHL(((TEIL(A1;MIN(FINDEN(ZEILE($1:$10)-1;A1&1234567890));SPALTE(1:1))*1)>=0)*1))}

ist um einiges kürzer als deine Variante ;)

Hallo Chris.

Danke für die "Abkürzung".

Jetzt funktioniert alles.

Gruß Joe

[BoskoBiati]

Hi,

da habe ich aber noch etwas kürzeres:

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C
1	Dies ist ein immer unterschiedlich langer Text mit 06731 Ortsname	Ortsname	06731
2		Ortsname	06731

Zelle	Format	Wert
C1	00000	6731

Zelle	Formel
B1	=GLÄTTEN(TEIL($A$1;SUCHEN(C1;$A$1)+5;99))
C1	{=VERWEIS(9^99;1*TEIL(A1;MIN(WENN(ISTZAHL(1*TEIL(A1;SPALTE(1:1);1));SPALTE(1:1)));SPALTE(1:1)))}
B2	=TEIL($A$1;SUCHEN(C2;$A$1)+6;99)
C2	{=TEIL(A1;MIN(WENN(ISTZAHL(TEIL(A1;SPALTE(2:2);5)*1);SPALTE(2:2)))+1;5)}

Achtung, Matrixformel enthalten!

Die geschweiften Klammern{} werden nicht eingegeben.

Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

[Objektscout]

Hi,

da habe ich aber noch etwas kürzeres:

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C
1	Dies ist ein immer unterschiedlich langer Text mit 06731 Ortsname	Ortsname	06731
2		Ortsname	06731

Zelle	Format	Wert
C1	00000	6731

Zelle	Formel
B1	=GLÄTTEN(TEIL($A$1;SUCHEN(C1;$A$1)+5;99))
C1	{=VERWEIS(9^99;1*TEIL(A1;MIN(WENN(ISTZAHL(1*TEIL(A1;SPALTE(1:1);1));SPALTE(1:1)));SPALTE(1:1)))}
B2	=TEIL($A$1;SUCHEN(C2;$A$1)+6;99)
C2	{=TEIL(A1;MIN(WENN(ISTZAHL(TEIL(A1;SPALTE(2:2);5)*1);SPALTE(2:2)))+1;5)}

Achtung, Matrixformel enthalten!

Die geschweiften Klammern{} werden nicht eingegeben.

Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

Hallo Opa Edgar.

Danke für die Formeln.
Habe deine noch um einen Zusatz ergänzt und dann hat es gepasst.
Nutze jetzt folgende Formel für die PLZ:
{=TEXT(VERWEIS(9^99;1*TEIL(A1;MIN(WENN(ISTZAHL(1*TEIL(A1;SPALTE(1:1);1));SPALTE(1:1)));SPALTE(1:1)));"00000")}
Damit klappt es prima und ist auch die kürzeste Formel .

Gruß Joe