Suche Inhalt aus einer Spalte und füge diese in einer neuen Spalte ein

[kölle123]

Hallo Zusammen,

benötige dringend eure Hilfe.

In Spalte A werden mir PLZ angezeigt. In Spalte B werden mir die gleichen PLZ aus Spalte A, jedoch mit der Zusatzinfo "Stadt & Straße" angezeigt.

Nun möchte ich in Spalte C folgendes angezeigt bekommen:

Suche in Spalte B den Wert (PLZ) aus Spalte A und füge den Inhalte aus Spalte B in Spalte C ein.

Ich benötige eine Formel da ich auch gerne dies lernen möchte.

Im Anhang habe ich eine Excel Liste beigefügt.

Würde mich auf Hilfe sehr freuen.

Grüße aus dem sonnigen Kölle :43:

[shift-del]

Hallo

Seufz...

Der Aufbau ist falsch. Und warum das eine Hilfsspalte sein soll erschließt sich mir nicht.
In Spalte A ist ein Mischmasch aus Text und Zahl. Schlecht für den Datenabgleich.

In Spalte E habe ich die Blitzvorschau verwendet mit der Beispielvorgabe 10179 in E2.

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E
1	PLZ	Adresse		Adresse	PLZ
2	52064	Franzstraße 6   52064  Aachen		Grunerstraße 20   10179  Berlin	10179
3	73431	Carl-Zeiss-Straße 96   73431  Aalen		Tempelhofer Damm 227   12099  Berlin	12099
4	72458	Sonnenstraße 30   72458  Albstadt		Pankstraße 32-39   13357  Berlin-Wedding	13357

Zelle	Formel
B2	=INDEX($D$2:$D$268;VERGLEICH(A2;$E$2:$E$268;0))

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2016

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

[Peter]

Hallo,

mit der Blitzvorschau komme ich im Moment nicht zu Recht, aber folgende Formel in C2 könnte auch zum Ziel führen:

=SVERWEIS(0+TEIL("ζ"&B2&"ζ";1+VERGLEICH(26;MMULT(N(ISTFEHL(0+TEIL(TEIL("ζ"&B2&"ζ";ZEILE(INDEX(B:B;1):INDEX(B:B;LÄNGE(B2)-4));7);{1.2.3.4.5.6.7};1)));{13;1;1;1;1;1;13});0);5);A:B;2;0)

Quelle: http://excelxor.com/2015/04/23/extractin...c-strings/

Nachtrag: Wenn in der Spalte B zweimal eine PLZ auftaucht, z.B. in B12, dann nimmt die Blitzvorschau die letzte PLZ und meine Formel die erste PLZ.

Und ja, ich bin der gleichen Meinung wie shift-del: Der Datenaufbau ist verbesserungswürdig.

[kölle123]

Hallo

Seufz...

Der Aufbau ist falsch. Und warum das eine Hilfsspalte sein soll erschließt sich mir nicht.
In Spalte A ist ein Mischmasch aus Text und Zahl. Schlecht für den Datenabgleich.

In Spalte E habe ich die Blitzvorschau verwendet mit der Beispielvorgabe 10179 in E2.

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E
1	PLZ	Adresse		Adresse	PLZ
2	52064	Franzstraße 6   52064  Aachen		Grunerstraße 20   10179  Berlin	10179
3	73431	Carl-Zeiss-Straße 96   73431  Aalen		Tempelhofer Damm 227   12099  Berlin	12099
4	72458	Sonnenstraße 30   72458  Albstadt		Pankstraße 32-39   13357  Berlin-Wedding	13357

Zelle	Formel
B2	=INDEX($D$2:$D$268;VERGLEICH(A2;$E$2:$E$268;0))

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2016

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

Hallo shift-del,

vielen Dank für deine Anwort. :)

Ich möchte folgendes:

Beispiel Zeile 1

In Spalte A2 wird die PLZ 52064 angezeigt. Jetzt möchte ich das diese PLZ in der kompleten Spalte B gesucht wird. Falls gefunden dann soll der gefundene Inhalt (in diesem Beispiel B25) in Spalte C eingefügt werden.

Grüße :)

[Klaus-Dieter]

Hallo,

dieser Lösungsvorschlag steht seit 7:09 im MS-Office-Forum, was gefällt dir daran nicht?

Tabelle1

	A	B	C
1	PLZ	Hilfsspalte	Ergebnis
2	52064	Grunerstraße 20   10179  Berlin	Franzstraße 6   52064  Aachen
3	73431	Tempelhofer Damm 227   12099  Berlin	Carl-Zeiss-Straße 96   73431  Aalen
4	72458	Pankstraße 32-39   13357  Berlin-Wedding	Sonnenstraße 30   72458  Albstadt
5	55232	Prerower Platz 1   13051  Berlin-Hohenschönhausen	Karl-Heinz-Kipp-Straße 23   55232  Alzey
6	92224	An den Freiheitswiesen 5   13597  Berlin-Spandau	Am Bergsteig 1   92224  Amberg
7	91522	Karl-Marx-Straße 66   12043  Berlin-Neukölln	Rothenburger Straße 15   91522  Ansbach
8	63741	Märkische Spitze 11   12681  Berlin-Biesdorf	Mainaschaffer Straße 115   63741  Aschaffenburg
9	63739	Am Borsigturm 2   13507  Berlin Tegel	Goldbacherstraße 2   63739  Aschaffenburg
10	86154	Johannisthaler Chaussee 309   12351  Berlin-Gropiusstadt	Schönbachstraße 192   86154  Augsburg
11	86159	Schnellerstraße 21   12439  Berlin-Schöneweide	Eichleitnerstraße 34   86159  Augsburg

Formeln der Tabelle

Zelle Formel C2 =SVERWEIS("*"&A2&"*";B:B;1;FALSCH) C3 =SVERWEIS("*"&A3&"*";B:B;1;FALSCH) C4 =SVERWEIS("*"&A4&"*";B:B;1;FALSCH) C5 =SVERWEIS("*"&A5&"*";B:B;1;FALSCH) C6 =SVERWEIS("*"&A6&"*";B:B;1;FALSCH) C7 =SVERWEIS("*"&A7&"*";B:B;1;FALSCH) C8 =SVERWEIS("*"&A8&"*";B:B;1;FALSCH) C9 =SVERWEIS("*"&A9&"*";B:B;1;FALSCH) C10 =SVERWEIS("*"&A10&"*";B:B;1;FALSCH) C11 =SVERWEIS("*"&A11&"*";B:B;1;FALSCH)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

[kölle123]

Hallo,

mit der Blitzvorschau komme ich im Moment nicht zu Recht, aber folgende Formel in C2 könnte auch zum Ziel führen:

=SVERWEIS(0+TEIL("ζ"&B2&"ζ";1+VERGLEICH(26;MMULT(N(ISTFEHL(0+TEIL(TEIL("ζ"&B2&"ζ";ZEILE(INDEX(B:B;1):INDEX(B:B;LÄNGE(B2)-4));7);{1.2.3.4.5.6.7};1)));{13;1;1;1;1;1;13});0);5);A:B;2;0)

Quelle: http://excelxor.com/2015/04/23/extractin...c-strings/

Nachtrag: Wenn in der Spalte B zweimal eine PLZ auftaucht, z.B. in B12, dann nimmt die Blitzvorschau die letzte PLZ und meine Formel die erste PLZ.

Und ja, ich bin der gleichen Meinung wie shift-del: Der Datenaufbau ist verbesserungswürdig.

Hallo Peter,

vielen Dank für deine Antwort. Die Formel ist super.
Mir werden jetzt fast alle Werte in Spalte C richtig angezeigt. Die PLZ aus A15 (55543) ist in Spalte B38 vertreten. Jedoch wird mir bei Zelle C15 "#NV" angezeigt.

Ich bin wie folgt vorgegangen:

Deine Formel habe ich in Zelle C2 eingefügt. Dann Doppelklick auf C2 unten recht damit in anderen Zellen in Spalte C die Formel übernommen wird.

Ich finde jedoch nicht den Fehler warum in C15 nicht der richtige Wert angezeigt wird. Hast du eine Idee?

Ich habe hier nochmal die geänderte Excel Datei "Test1" beigefügt.

Grüße

[kölle123]

Hallo,

dieser Lösungsvorschlag steht seit 7:09 im MS-Office-Forum, was gefällt dir daran nicht?

Tabelle1

	A	B	C
1	PLZ	Hilfsspalte	Ergebnis
2	52064	Grunerstraße 20   10179  Berlin	Franzstraße 6   52064  Aachen
3	73431	Tempelhofer Damm 227   12099  Berlin	Carl-Zeiss-Straße 96   73431  Aalen
4	72458	Pankstraße 32-39   13357  Berlin-Wedding	Sonnenstraße 30   72458  Albstadt
5	55232	Prerower Platz 1   13051  Berlin-Hohenschönhausen	Karl-Heinz-Kipp-Straße 23   55232  Alzey
6	92224	An den Freiheitswiesen 5   13597  Berlin-Spandau	Am Bergsteig 1   92224  Amberg
7	91522	Karl-Marx-Straße 66   12043  Berlin-Neukölln	Rothenburger Straße 15   91522  Ansbach
8	63741	Märkische Spitze 11   12681  Berlin-Biesdorf	Mainaschaffer Straße 115   63741  Aschaffenburg
9	63739	Am Borsigturm 2   13507  Berlin Tegel	Goldbacherstraße 2   63739  Aschaffenburg
10	86154	Johannisthaler Chaussee 309   12351  Berlin-Gropiusstadt	Schönbachstraße 192   86154  Augsburg
11	86159	Schnellerstraße 21   12439  Berlin-Schöneweide	Eichleitnerstraße 34   86159  Augsburg

Formeln der Tabelle

Zelle Formel C2 =SVERWEIS("*"&A2&"*";B:B;1;FALSCH) C3 =SVERWEIS("*"&A3&"*";B:B;1;FALSCH) C4 =SVERWEIS("*"&A4&"*";B:B;1;FALSCH) C5 =SVERWEIS("*"&A5&"*";B:B;1;FALSCH) C6 =SVERWEIS("*"&A6&"*";B:B;1;FALSCH) C7 =SVERWEIS("*"&A7&"*";B:B;1;FALSCH) C8 =SVERWEIS("*"&A8&"*";B:B;1;FALSCH) C9 =SVERWEIS("*"&A9&"*";B:B;1;FALSCH) C10 =SVERWEIS("*"&A10&"*";B:B;1;FALSCH) C11 =SVERWEIS("*"&A11&"*";B:B;1;FALSCH)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Hallo Klaus-Dieter,

sorry übersehen...vielen Dank für deine Hilfe :)

Grüße

[Klaus-Dieter]

Hallo,

bitte verzichte auf das dämliche Zitieren vorangegangener Beiträge.

[Peter]

Hallo,

die Sverweis-Formel aus dem MOF-Forum macht das was du willst wesentlich einfacher und fehlerfreier als die von mir gespostete Formel.

Die von mir gepostete Formel hat ein Problem wenn die PLZ mit einer 0 beginnt. Ich habe nur die ersten Zeilen getestet, da ist mir das nicht aufgefallen.

Und damit ist für mich das Thema durch - Crossposting mag ich nicht sonderlich.

[Dieter63]

Hallo,

D1 = "*"

=INDEX($B:$B;VERGLEICH($D$1&A2&$D$1;$B:$B;0))