Werte in einer Spalte in Excel trennen


Betrifft: Werte in einer Spalte in Excel trennen
von: Tommi
Geschrieben am: 05.10.2017 12:21:55

Hallo zusammen,
ich habe in einer Spalte (Textformat) 3 Zahlen (jede Zahl 5-15 Ziffern lang)
z.B in Spalte A:
1234 847575766 193495959
124324 24353 2352345234525
235 2523523523523 525235235
Gibt es eine Möglichkeit direkt in Excel die letzten beiden Zahlen 'sauber' rauszutrennen?
Also dass in Spalte B die mittlere Zahl und in Spalte C die rechte Zahl steht. Da die SPACES aber an variablen Stellen stehen, komme ich mit rechts(A1;Wert) oder so ja nicht wirklich weiter
LG,
Tommi

Betrifft: AW: Text-in-Spalten owt
von: Fennek
Geschrieben am: 05.10.2017 12:29:43


Betrifft: AW: Werte in einer Spalte in Excel trennen
von: Luschi
Geschrieben am: 05.10.2017 12:33:05
Hallo Tommi,
warum googelst Du nicht: excel daten in spalte trennen
Da kommt alst erstes gleich dieser Link:
https://support.office.com/de-de/article/Aufteilen-von-Text-in-verschiedene-Spalten-mit-dem-Textkonvertierungs-Assistenten-30b14928-5550-41f5-97ca-7a3e9c363ed7
Gruß von Luschi
aus klein-Paris

Betrifft: AW: Werte in einer Spalte in Excel trennen
von: Tommi
Geschrieben am: 05.10.2017 12:49:26
Hallo Luschi,
sorry, mein Fehler, es fehlt noch die Zusatzinformation, dass vor den Zahlen manchmal noch etwas davor steht (Text) der leider auch Leerzeichen enthalten kann.
Damit greift das DATEN-Text in Spalten (mit Trenner SPACE) leider nicht :( - klar, ohne die Info wäre das der schnellste Weg gewesen. Ich suche halte irgendwie nach einer Herangehensweise, von rechts alles bis zum ersten Space zu trennen und danach bis zum übernächsten Space.
Per VBA kriege ich das hin, aber geht das auch irgendwie direkt in Excel?
LG,
Tommi

Betrifft: da freuen sich aber alle
von: Rudi Maintaire
Geschrieben am: 05.10.2017 13:29:42
Lupos Formel mit Zusatz:
=GLÄTTEN(TEIL(WECHSELN($A1;" ";WIEDERHOLEN(" ";99));(SPALTE(B1)+LÄNGE($A1)-LÄNGE(WECHSELN($A1;" ";)) -2)*99-98;99))

Betrifft: mit meiner Formel
von: lupo1
Geschrieben am: 05.10.2017 12:38:53
B1: =GLÄTTEN(TEIL(WECHSELN($A1;" ";WIEDERHOLEN(" ";99));SPALTE(B1)*99-98;99))
Tutorial: http://www.office-hilfe.com/support/showthread.php/22254

Betrifft: seeehr schöne Lösung owT
von: Rudi Maintaire
Geschrieben am: 05.10.2017 12:48:12


Betrifft: AW: Werte in einer Spalte in Excel trennen
von: Rudi Maintaire
Geschrieben am: 05.10.2017 12:44:23
Hallo,
die Mitte:
=--LINKS(WECHSELN(TEIL(A2;FINDEN(" ";A2)+1;99);" ";"#");FINDEN("#";WECHSELN(TEIL(A2;FINDEN(" ";A2)+1; 99);" ";"#"))-1)
rechts ist's leichter:
=--TEIL(A2;FINDEN("#";WECHSELN(A2;" ";"#";2))+1;99)
Gruß
Rudi

Betrifft: es geht um die letzte und vorletzte Zahl
von: WF
Geschrieben am: 05.10.2017 13:14:14
Hi,
wie ich oben las ist das nicht der zweite und dritte Block.
die letzte:
=TEIL(A1;VERWEIS(9^9;FINDEN(" ";A1;ZEILE(1:999)))+1;50)
die vorletzte:
=LINKS(TEIL(A1;FINDEN("##";WECHSELN(A1;" ";"##";LÄNGE(A1)-LÄNGE(WECHSELN(A1;" ";))-1))+1;999); FINDEN(" ";TEIL(A1;FINDEN("##";WECHSELN(A1;" ";"##";LÄNGE(A1)-LÄNGE(WECHSELN(A1;" ";))-1))+1;999))-1)
siehe unsere:
http://www.excelformeln.de/formeln.html?welcher=301
WF

Betrifft: SPALTE(B1) (statt SPALTE(A1) gesehen?
von: lupo1
Geschrieben am: 05.10.2017 13:56:55
Bei insgesamt fix 3 Einträgen beginne ich bei Eintrag 2. Und durch Kopieren nach rechts bekomme ich Eintrag 3 der Zelle.

Betrifft: zurückgerudert
von: lupo1
Geschrieben am: 05.10.2017 13:58:44
Habe jetzt erst den Nachtrag "da kann noch was davor stehen" gesehen. Dann hast Du mit vorletztem und letztem natürlich recht.

Betrifft: AW: den vorletzten Block ermitteln ...
von: ... neopa C
Geschrieben am: 05.10.2017 14:26:23
Hallo WF,
... wenn in C1 mit Deiner Formel der letzte ermittelt wird, dann in D1 einfach analog:

=WECHSELN(TEIL(A1;VERWEIS(9^9;FINDEN(" ";WECHSELN(A1;" "&C1;"");ZEILE(1:999)))+1;99);" "&C1;"")

Gruß Werner
.. , - ...

Betrifft: na ja: vorletzter ohne den letzten zu kennen
von: WF
Geschrieben am: 05.10.2017 14:35:56
Hi,
das ist die Formel von mir (bei uns).
WF

Betrifft: AW: offensichtlich hast Du nicht gelesen ...
von: ... neopa C
Geschrieben am: 05.10.2017 14:50:41
Hallo WF,
... ich hatte geschrieben: "... wenn in C1 mit Deiner Formel der letzte ermittelt wird, dann in D1 einfach analog"
Ich kann allerdings auch gern wieder eine AGGREGAT()-Formel "servieren".
Gruß Werner
.. , - ...

Betrifft: was soll das ?
von: WF
Geschrieben am: 05.10.2017 14:57:22
Du brauchst für den vorletzten also den letzten Eintrag.

Betrifft: AW: ich bezog mich auf Deinen Beitrag von 13:14 ..
von: ... neopa C
Geschrieben am: 05.10.2017 16:59:12
Hallo WF,
... aus der entnahm ich ein Formelangebot für den letzten und vorletzten Block. Darauf bezog ich mich bisher.

Wenn jedoch nur der vorletzte Block gesucht sein sollte
, kann man das z.B. wie von mir und ergänzend von Lupo hier: https://www.herber.de/forum/archiv/1580to1584/t1583769.htm
Dann würde ich diesmal hier die Formel von Lupo nutzen und anpassen:
=GLÄTTEN(TEIL(WECHSELN($A1;" ";WIEDERHOLEN(" ";99));(LÄNGE(GLÄTTEN(A1))-LÄNGE(WECHSELN(GLÄTTEN(A1);" ";"")))*99-98;99))
Die könnte noch kürzer ausfallen, wenn vorausgesetzt wird, dass keine Leerzeichen am Text in A1 an gehangen sind:

=GLÄTTEN(TEIL(WECHSELN($A1;" ";WIEDERHOLEN(" ";99));(LÄNGE(A1)-LÄNGE(WECHSELN(A1;" ";"")))*99-98;99))
Gruß Werner
.. , - ...

Betrifft: Mit UDF-Einsatz wäre das kein Problem und ...
von: Luc:-?
Geschrieben am: 06.10.2017 02:31:39
…die Fml überschaubar kurz, Tommi,
egal wieviel reine (auch leerzeichen-getrennte) Texte da noch zwischengeschoben sind. Diese Texte dürfen nur ihrerseits nicht wieder Ziffern enthalten, die irrelevant sein sollen. D.h., es muss letztlich die ursprünglich von dir gezeigte Struktur herauskommen können. Wären das dann immer konstant 3 (leerzeichen-getrennte) ZiffernBlöcke, käme folgende (plurale) MatrixFml (über 2 Zellen) zum Einsatz:
{=Splint(MaskOn(A1;"num");;2;3)}
Ist das nicht der Fall und die Anzahl der ZiffernBlöcke kann mal 2, 3 oder mehr betragen, aber es sollen stets die beiden letzten ausgewählt wdn, sähe die MatrixFml so aus:
{=Splint(MaskOn(A1;"num");;CountOn(MaskOn(A1;"num");" "))}
Die Pgmm der verwendeten UDFs sind hier zu finden:
CountOn (Vs1.1) https://www.herber.de/forum/archiv/732to736/732035_Zaehlennwenn_mit_Zahlenkombinationen.html#734566
MaskOn (Vs2.1) https://www.herber.de/cgi-bin/callthread.pl?index=1344962#1345181
Splint (Vs1.1) https://www.herber.de/forum/archiv/864to868/865813_Texte_per_VBA_in_einzelne_Teile_aufteilen.html#865877
Für die 2.Fml-Variante wird allerdings Vs1.2 von Splint benötigt:


Rem Bildet aus Einzeltexten Teiltextfelder von…bis…Posit (lfdElemNr) im Text
'   m.d.Möglichk, nur d.Endposition durch d.real letzte Element zu ersetzen.
'   Vs1.2a -LSr.CyWorXxl -CDate:20070427 -1Pub:h20070428(1.1) -LUpd:20080109n
Function Splint(Text, Optional Trenner As String = " ", Optional ByVal AnfPos As Integer, _
                Optional ByVal EndPos As Integer, Optional ByVal LetztEnd As Boolean)
    Dim i As Long, j As Long, l As Long, m As Long, TxtVkt, x, y() As String
    If AnfPos = 0 And EndPos = 0 Then Splint = Split(Text, Trenner): Exit Function
    If InStr(Text, Trenner) = 0 Then
    Else: TxtVkt = Split(Text, Trenner): m = UBound(TxtVkt) + 1 - LBound(TxtVkt)
    End If
    If AnfPos = 0 Then AnfPos = 1
    If EndPos = 0 Then EndPos = m
    l = EndPos - AnfPos: If l < 0 Then Exit Function
    ReDim y(l) As String
    For Each x In TxtVkt
        i = i + 1
        If i >= AnfPos And i <= m + CInt(LetztEnd) And _
            j <= l + CInt(LetztEnd) Then y(j) = x: j = j + 1
        If LetztEnd And (i = m Or j > l) Then y(l) = x: Exit For
    Next x
    Splint = y
End Function
Feedback nicht unerwünscht! Gruß, Luc :-?

Besser informiert mit …

Beiträge aus den Excel-Beispielen zum Thema "Werte in einer Spalte in Excel trennen"