Das Clever-Excel-Forum.de - Treffen
findet vom 15. - 17. September 2017 in Thüringen / Region Großer Inselsberg statt. Hotelbuchung ab sofort möglich.


Text in Spalten
#1
Hallo
Ich suche eine Lösung um Texte in einer Spalte zu teilen, wer kann mir hier einen Tipp geben

Text in Spalte B
Restaurant Sonne 15.02.2014 mit Hermann J.
Rest. obere Krone 28.04.2014 und Kammer W. & K
15.03.2014 war geschlossen
offen ab 31.03.2014
Rest untere Krone noch nicht bereit

Ergebnis müsste wie folgt aussehen.
Spalte B = Text vor Datum oder kein Datum
Spalte C = Datum
Spalte D = Text nach Datum

Bin gespannt wie diese Aufteilung im Excel aussehen könnte
Danke Oski
to top
#2
Hi Oski,

passt das so?

Tabelle1

ABCD
1Restaurant Sonne 15.02.2014 mit Hermann J.Restaurant Sonne 15.02.2014 mit Hermann J.
2Rest. obere Krone 28.04.2014 und Kammer W. & KRest. obere Krone 28.04.2014 und Kammer W. & K
315.03.2014 war geschlossen15.03.2014 war geschlossen
4offen ab 31.03.2014offen ab 31.03.2014
5Rest untere Krone noch nicht bereitRest untere Krone noch nicht bereit
Formeln der Tabelle
ZelleFormel
B1=LINKS(A1;SUCHEN(C1;A1)-1)
C1{=TEIL(LINKS(A1;MAX(ISTZAHL(TEIL(A1;SPALTE(1:1);1)*1)*SPALTE(1:1)));VERGLEICH(1;ISTZAHL(TEIL(A1&0;SPALTE(1:1);1)*1)*1;0);LÄNGE(A1))}
D1=RECHTS(A1;LÄNGE(A1)-LÄNGE(B1)-LÄNGE(C1))
B2=LINKS(A2;SUCHEN(C2;A2)-1)
C2{=TEIL(LINKS(A2;MAX(ISTZAHL(TEIL(A2;SPALTE(2:2);1)*1)*SPALTE(2:2)));VERGLEICH(1;ISTZAHL(TEIL(A2&0;SPALTE(2:2);1)*1)*1;0);LÄNGE(A2))}
D2=RECHTS(A2;LÄNGE(A2)-LÄNGE(B2)-LÄNGE(C2))
B3=LINKS(A3;SUCHEN(C3;A3)-1)
C3{=TEIL(LINKS(A3;MAX(ISTZAHL(TEIL(A3;SPALTE(3:3);1)*1)*SPALTE(3:3)));VERGLEICH(1;ISTZAHL(TEIL(A3&0;SPALTE(3:3);1)*1)*1;0);LÄNGE(A3))}
D3=RECHTS(A3;LÄNGE(A3)-LÄNGE(B3)-LÄNGE(C3))
B4=LINKS(A4;SUCHEN(C4;A4)-1)
C4{=TEIL(LINKS(A4;MAX(ISTZAHL(TEIL(A4;SPALTE(4:4);1)*1)*SPALTE(4:4)));VERGLEICH(1;ISTZAHL(TEIL(A4&0;SPALTE(4:4);1)*1)*1;0);LÄNGE(A4))}
D4=RECHTS(A4;LÄNGE(A4)-LÄNGE(B4)-LÄNGE(C4))
B5=LINKS(A5;SUCHEN(C5;A5)-1)
C5{=TEIL(LINKS(A5;MAX(ISTZAHL(TEIL(A5;SPALTE(5:5);1)*1)*SPALTE(5:5)));VERGLEICH(1;ISTZAHL(TEIL(A5&0;SPALTE(5:5);1)*1)*1;0);LÄNGE(A5))}
D5=RECHTS(A5;LÄNGE(A5)-LÄNGE(B5)-LÄNGE(C5))
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8

Die Matrixformel in C stammt von Excelformeln.de
?mage

Misserfolg ist eine Chance, es beim
nächsten Mal besser zu machen.

(Henry Ford)
http://www.sprueche-zum-nachdenken.eu
to top
#3
HI Oski,

ich glaube, ich habe da vorhin überlesen, dass
Zitat:Spalte B = Text vor Datum oder kein Datum
ohne Datum der Text in die Spalte B soll.

Dann musst du die Formel in B noch durch WENN... ergänzen.

Tabelle1

ABCD
1Restaurant Sonne 15.02.2014 mit Hermann J.Restaurant Sonne 15.02.2014 mit Hermann J.
2Rest. obere Krone 28.04.2014 und Kammer W. & KRest. obere Krone 28.04.2014 und Kammer W. & K
3war geschlossenwar geschlossen
4offen ab 31.03.2014offen ab 31.03.2014
5Rest untere Krone noch nicht bereitRest untere Krone noch nicht bereit
624.12.2014 bleibt die Hütte zu24.12.2014 bleibt die Hütte zu
Formeln der Tabelle
ZelleFormel
B1=WENN(C1="";A1;LINKS(A1;SUCHEN(C1;A1)-1))
C1{=TEIL(LINKS(A1;MAX(ISTZAHL(TEIL(A1;SPALTE(1:1);1)*1)*SPALTE(1:1)));VERGLEICH(1;ISTZAHL(TEIL(A1&0;SPALTE(1:1);1)*1)*1;0);LÄNGE(A1))}
D1=RECHTS(A1;LÄNGE(A1)-LÄNGE(B1)-LÄNGE(C1))
B2=WENN(C2="";A2;LINKS(A2;SUCHEN(C2;A2)-1))
C2{=TEIL(LINKS(A2;MAX(ISTZAHL(TEIL(A2;SPALTE(2:2);1)*1)*SPALTE(2:2)));VERGLEICH(1;ISTZAHL(TEIL(A2&0;SPALTE(2:2);1)*1)*1;0);LÄNGE(A2))}
D2=RECHTS(A2;LÄNGE(A2)-LÄNGE(B2)-LÄNGE(C2))
B3=WENN(C3="";A3;LINKS(A3;SUCHEN(C3;A3)-1))
C3{=TEIL(LINKS(A3;MAX(ISTZAHL(TEIL(A3;SPALTE(3:3);1)*1)*SPALTE(3:3)));VERGLEICH(1;ISTZAHL(TEIL(A3&0;SPALTE(3:3);1)*1)*1;0);LÄNGE(A3))}
D3=RECHTS(A3;LÄNGE(A3)-LÄNGE(B3)-LÄNGE(C3))
B4=WENN(C4="";A4;LINKS(A4;SUCHEN(C4;A4)-1))
C4{=TEIL(LINKS(A4;MAX(ISTZAHL(TEIL(A4;SPALTE(4:4);1)*1)*SPALTE(4:4)));VERGLEICH(1;ISTZAHL(TEIL(A4&0;SPALTE(4:4);1)*1)*1;0);LÄNGE(A4))}
D4=RECHTS(A4;LÄNGE(A4)-LÄNGE(B4)-LÄNGE(C4))
B5=WENN(C5="";A5;LINKS(A5;SUCHEN(C5;A5)-1))
C5{=TEIL(LINKS(A5;MAX(ISTZAHL(TEIL(A5;SPALTE(5:5);1)*1)*SPALTE(5:5)));VERGLEICH(1;ISTZAHL(TEIL(A5&0;SPALTE(5:5);1)*1)*1;0);LÄNGE(A5))}
D5=RECHTS(A5;LÄNGE(A5)-LÄNGE(B5)-LÄNGE(C5))
B6=WENN(C6="";A6;LINKS(A6;SUCHEN(C6;A6)-1))
C6{=TEIL(LINKS(A6;MAX(ISTZAHL(TEIL(A6;SPALTE(6:6);1)*1)*SPALTE(6:6)));VERGLEICH(1;ISTZAHL(TEIL(A6&0;SPALTE(6:6);1)*1)*1;0);LÄNGE(A6))}
D6=RECHTS(A6;LÄNGE(A6)-LÄNGE(B6)-LÄNGE(C6))
Enthält Matrixformel:
Umrandende
{ } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
?mage

Misserfolg ist eine Chance, es beim
nächsten Mal besser zu machen.

(Henry Ford)
http://www.sprueche-zum-nachdenken.eu
to top
#4
Hallo

Ich hätte auch noch ein paar Ideen.
Arbeitsblatt mit dem Namen 'Tabelle1'
 ABCD
1Restaurant Sonne 15.02.2014 mit Hermann J.Restaurant Sonne15.02.2014mit Hermann J.
2Rest. obere Krone 28.04.2014 und Kammer W. & KRest. obere Krone28.04.2014und Kammer W. & K
315.03.2014 war geschlossen 15.03.2014war geschlossen
4offen ab 31.03.2014offen ab31.03.2014 
5Rest untere Krone noch nicht bereitRest untere Krone noch nicht bereit  

ZelleFormel
B1=WENNFEHLER(WENN(C1="";A1;LINKS(A1;SUCHEN(TEXT(C1;"TT.MM.JJJJ");A1)-2));"")
C1=WENNFEHLER(VERWEIS(9^99;--TEIL(WECHSELN(A1;" ";"")&"x";SPALTE(A1:Z1);10));"")
D1=WENNFEHLER(WENN(C1="";"";RECHTS(A1;LÄNGE(A1)-SUCHEN(TEXT(C1;"TT.MM.JJJJ");A1)-10));"")
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg
Wir sehen uns!
... shift-del
Eine Mustertabelle hilft beim Helfen.
Excel Super-Funktionen: VERWEIS(), INDEX(), WAHL()
[-] Folgende(r) 1 Benutzer sagt Danke an shift-del für diesen Beitrag:
oski
to top
#5
Hi Detlef,

Code:
=WENNFEHLER(VERWEIS(9^99;--TEIL(WECHSELN(A1;" ";"")&"x";SPALTE(A1:Z1);10));"")

könntest du mir hierzu eine Erklärung geben?

Ich habe zwar in der Formelauswertung gesehen, wie das Ergebnis im Array aussieht (vorne ein Zeichen weniger, hinten eines mehr), doch ich komme nicht auf die Arbeitsweise deiner Formel.
?mage

Misserfolg ist eine Chance, es beim
nächsten Mal besser zu machen.

(Henry Ford)
http://www.sprueche-zum-nachdenken.eu
to top
#6
Hallo Günter

Die Grundformel ist:
Code:
VERWEIS(9^99;--TEIL(A1;SPALTE(1:1);10))
Damit wird die "letzte Zahl" gefunden.
Leider wird dann aus 15.02.2014 05.02.214 weil irgendwo am Ende "5.02.2014 " steht.
Also entferne ich alle Leerzeichen.
Code:
VERWEIS(9^99;--TEIL(WECHSELN(A1;" ";"");SPALTE(1:1);10))
Dann erhalte ich im vierten Fall den Wert 4 weil der Text mit dem Datum endet.
Also hänge ich an den Text noch ein Zeichen dran.
Code:
VERWEIS(9^99;--TEIL(WECHSELN(A1;" ";"")&"x";SPALTE(1:1);10))
Und WENNFEHLER() ist dann für den Fall dass im Text keine Zahl (kein Datum) steht.
Wir sehen uns!
... shift-del
Eine Mustertabelle hilft beim Helfen.
Excel Super-Funktionen: VERWEIS(), INDEX(), WAHL()
to top
#7
Hi Detlef,

danke für deine Erklärung. Die einzelnen Schritte habe ich mal interessehalber nachvollzogen.

Zitat:Leider wird dann aus 15.02.2014 05.02.214 weil irgendwo am Ende "5.02.2014 " steht.
Also entferne ich alle Leerzeichen.

Falls kein Datum, sondern eine "normale" Ziffernfolge im Text vorhanden ist, "klaut" Excel ebenfalls die erste Ziffer. Aus z.B. 15914 wird 5914, aus 1590272014 wird 590272014. Irgendwie ist das schon verrückt. 16

Aber mit deiner genialen Lösung passt das hinterher. Thumps_up
?mage

Misserfolg ist eine Chance, es beim
nächsten Mal besser zu machen.

(Henry Ford)
http://www.sprueche-zum-nachdenken.eu
to top
#8
Hallo Zusammen,

oder vielleicht einfach so?

=VERWEIS(9^99;--TEIL(A1;SPALTE(1:1);11))

Gruß Uwe
to top
#9
Hallo Uwe

Gute Idee.
Aber wenn ich dann den Fehler im vierten Fall abfangen will läuft es auf einen weiteren Fehler:
Arbeitsblatt mit dem Namen 'Tabelle1'
 AE
4offen ab 31.03.2014#NV

ZelleFormel
E4=VERWEIS(9^99;--TEIL(A4&"x";SPALTE(4:4);11))
Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg
Wir sehen uns!
... shift-del
Eine Mustertabelle hilft beim Helfen.
Excel Super-Funktionen: VERWEIS(), INDEX(), WAHL()
to top
#10
Hi Uwe,

auch bei mir läuft den Vorschlag in einen Fehler. Allerdings wirft mir XL bei Detlefs Beispiel kein #NV aus, sondern eine 4, respektive 04.01.1900. Sofern überhaupt kein Datum vorhanden ist, kommt bei mir #NV, das aber mit einem WENNFEHLER... umgangen werden kann.

Tabelle1

ABCD
1Restaurant Sonne 15.02.2014 mit Hermann J.Restaurant Sonne15.02.2014mit Hermann J.
2Rest. obere Krone 28.04.2014 und Kammer W. & KRest. obere Krone28.04.2014und Kammer W. & K
3war geschlossen#NV
4offen ab 31.03.201404.01.1900
524.12.2014 bleibt die Hütte zu24.12.2014bleibt die Hütte zu
Formeln der Tabelle
ZelleFormel
B1=WENNFEHLER(WENN(C1="";A1;LINKS(A1;SUCHEN(TEXT(C1;"TT.MM.JJJJ");A1)-2));"")
C1=VERWEIS(9^99;--TEIL(A1;SPALTE(1:1);11))
D1=WENNFEHLER(WENN(C1="";"";RECHTS(A1;LÄNGE(A1)-SUCHEN(TEXT(C1;"TT.MM.JJJJ");A1)-10));"")
B2=WENNFEHLER(WENN(C2="";A2;LINKS(A2;SUCHEN(TEXT(C2;"TT.MM.JJJJ");A2)-2));"")
C2=VERWEIS(9^99;--TEIL(A2;SPALTE(2:2);11))
D2=WENNFEHLER(WENN(C2="";"";RECHTS(A2;LÄNGE(A2)-SUCHEN(TEXT(C2;"TT.MM.JJJJ");A2)-10));"")
B3=WENNFEHLER(WENN(C3="";A3;LINKS(A3;SUCHEN(TEXT(C3;"TT.MM.JJJJ");A3)-2));"")
C3=VERWEIS(9^99;--TEIL(A3;SPALTE(3:3);11))
D3=WENNFEHLER(WENN(C3="";"";RECHTS(A3;LÄNGE(A3)-SUCHEN(TEXT(C3;"TT.MM.JJJJ");A3)-10));"")
B4=WENNFEHLER(WENN(C4="";A4;LINKS(A4;SUCHEN(TEXT(C4;"TT.MM.JJJJ");A4)-2));"")
C4=VERWEIS(9^99;--TEIL(A4;SPALTE(4:4);11))
D4=WENNFEHLER(WENN(C4="";"";RECHTS(A4;LÄNGE(A4)-SUCHEN(TEXT(C4;"TT.MM.JJJJ");A4)-10));"")
B5=WENNFEHLER(WENN(C5="";A5;LINKS(A5;SUCHEN(TEXT(C5;"TT.MM.JJJJ");A5)-2));"")
C5=VERWEIS(9^99;--TEIL(A5;SPALTE(5:5);11))
D5=WENNFEHLER(WENN(C5="";"";RECHTS(A5;LÄNGE(A5)-SUCHEN(TEXT(C5;"TT.MM.JJJJ");A5)-10));"")

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
?mage

Misserfolg ist eine Chance, es beim
nächsten Mal besser zu machen.

(Henry Ford)
http://www.sprueche-zum-nachdenken.eu
to top


Möglicherweise verwandte Themen...
Thema Verfasser Antworten Ansichten Letzter Beitrag
  Bestellung/Spalten: nur bestimmte Spalten kopieren teufelchenbs 14 312 25.11.2016, 20:55
Letzter Beitrag: schauan
  Text spalten in alternative hisid1 5 176 12.09.2016, 12:27
Letzter Beitrag: steve1da
  Text in Spalten Fred B 7 269 02.09.2016, 08:07
Letzter Beitrag: Fred B
  Spalten nach Text durchsuchen KJG 3 485 29.04.2016, 14:55
Letzter Beitrag: schauan
  Text in Spalten statistiker 7 438 15.04.2016, 09:57
Letzter Beitrag: schauan
  Text und Zahl vor individuellem Text julian 7 455 19.03.2016, 14:04
Letzter Beitrag: Rabe
  Excel ekennt Text als Zahl, dann wieder als Text atilla 16 1.620 03.03.2016, 19:37
Letzter Beitrag: Castor
  Spalten in CSV als Text shadow-c4 2 455 18.02.2016, 09:40
Letzter Beitrag: shadow-c4
  Text in Spalten in Zellen mit viel Inhalt Bull1909 3 474 21.01.2016, 11:45
Letzter Beitrag: Bull1909
  Verknüpfen von "Text von" und vorgegebenem Text SF91 3 741 10.09.2015, 21:32
Letzter Beitrag: Rabe

Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste