Text und Zahlen trennen

[Noob0815]

Hi,
ich habe eine Tabelle mit ca 20T Datensätzen.
In spalte "B" habe ich einen String im folgenden format:
TextZahl
Nun brauche ich eine Formel um den Text von der Zahl zu trennen, als Ergebnis brauche ich nur den Text.

LG

[Gast 123]

Hallo

ich weiss nicht ob das mit Formeln geht, hier ein kleines makro. Das tuts auch!
PS  würde mich interessieren wie lange es bei 20T Datensätzen braucht.
mfg 

Gast 123

Code:

Sub Text_trennen()
Dim AC As Range, i, lz1 As Long
    lz1 = Cells(Rows.Count, "A").End(xlUp).Row
    For Each AC In Range("A2:A" & lz1)
        For i = Len(AC) To 1 Step -1
            If Not IsNumeric(Mid(AC, i, 1)) Then
               AC.Offset(0, 1) = Left(AC, i): Exit For
            End If
        Next i
    Next AC
End Sub

[Fred11]

Hallo Noob0815,

Formellösung:
https://www.herber.de/excelformeln/pages...ennen.html
https://www.office-hilfe.com/support/thr...hen.52881/

Arbeitsblatt mit dem Namen 'Tabelle3'
	B	C	D
1	Text und Zahl	nur Text 1	nur Text 2
2	Test123	Test	Test
3	Hallo34	Hallo	Hallo
4	a55	a	a
5	Langer Text77	Langer Text	Langer Text
6	ABCDEF 5	ABCDEF	ABCDEF

Zelle	Formel
C2	=LINKS(B2;VERGLEICH(1;ISTZAHL(TEIL(B2;SPALTE(A1:IQ1);1)*1)*1;0)-1)
D2	=WECHSELN(B2;VERWEIS(1E+99;RECHTS(B2;SPALTE(1:1))+0);)

Verwendete Systemkomponenten: [Windows (64-bit) NT 10.00] / MS Excel 2021

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

mit der Blitzvorschau
https://www.pctipp.ch/praxis/office/exce...10242.html

mit Power Query
- Datenbereich selektieren
- Menü Daten -> Gruppe Daten abrufen und transformieren -> aus Tabelle/Bereich
- Power Query Editor öffnet sich
- Menü Start -> Spalte teilen -> Nach Wechsel von Nicht-Ziffer zu Ziffer
- Power Query Editor schliessen und zu Excel zurückkehren: Menü Start-> Schliessen und Laden
https://www.prt.de/power-query023-zeiche...e-trennen/

Gruß
Fred

[Noob0815]

Hallo,

vielen Dank für eure Hilfe, ich habe mich nun für die VBA-Variante entschieden.

LG und schönes WE

[juvee]

@Gast123,

da du 40T mal auf eine Zelle zugreifst, viel länger als wenn du Arrays nutzen würdest.
  darüber hinaus öddelst du auch noch rückwärts über jeden Zelleintrag bis zum Auftreten der ersten NichtZiffer

das kostet Zeit

so würde ich das in einem Makro machen

Code:

Sub x()
Dim rg As Range                    'Datenherkunft
Dim arr As Variant                  'Datenspeicher
'Daten einlesen
  Set rg = ActiveSheet.Range("B1").CurrentRegion
  arr = rg.Offset(1).Resize(rg.Rows.Count - 1).Value2
   
'Daten be-/verarbeiten
Dim i As Long
Dim vOut() As Variant
Dim objRegExp As Object

  ReDim vOut(1 To UBound(arr, 1))

  Set objRegExp = CreateObject("VBScript.RegExp") ' Create a regular expression
  objRegExp.Global = True ' Set global applicability
  objRegExp.MultiLine = True
  objRegExp.ignorecase = True  ' Set case insensitivity
  objRegExp.Pattern = "\D*"

  For i = 1 To UBound(arr, 1)
    vOut(i) = objRegExp.Execute(arr(i, 2))(0)
  Next
  Set objRegExp = Nothing

'Ergebnisse nach Spalte C
  ActiveSheet.Range("C2").Resize(UBound(vOut)) = vOut

End Sub

Wobei der Zeitaspekt bei 20T Datensätzen eher zu vernachlässigen ist.

Kannst ja mal testen. 20T  TestDaten sind ja schnell erstellt.

VG Juvee

[RPP63]

Außer Konkurrenz!
Excel 365, z.Zt. noch Insider-Channel:

	A	B
1	abc2734	abc
2	se999	se
3	kdjshz19283	kdjshz

Zelle	Formel
B1	=REGEXEXTRACT(A1:A3;"\D+")

[Noob0815]

Hi,
@juvee
ich habe deinen code mal durchlaufen lassen (sehr schnell) und leider erzeugt er nicht das ergebnis was er sollte
In der Schleife:

PHP-Code:

  For i = 1 To UBound(arr, 1)
    vOut(i) = objRegExp.Execute(arr(i, 2))(0)
  Next 


bei VOut(i) wird jeder Wert der Spalte "B" durchlaufen, allerdings bei der Ausgabe wird nur der Wert von "B2" in die Spalte "C" geschrieben.
Muss da die ausgabezeile noch angepasst werden

Code:

ActiveSheet.Range("C2").Resize(UBound(vOut)) = vOut

Mein wissen reicht leider noch nicht um dies selbst anzupassen 

LG

[Cadmus]

Du kannst auch PowerQuery benutzen. 

Tabelle über den Tab "Daten" einlesen und im PowerQuery Editor die Spalte trennen.

[Gast 123]

Hallo

@juvee   ich gebe dir Recht das ein Array schneller ist, aber dein Code ist mir zu hoch!  Hut ab vor dir!
Für Rückwärts entschied ich micht wegen der Angabe des TE mit "TextZahl". Da macht rückwärts Sinn!
Bei deinem Code muss ich aber komplett passen, den kann ich auch nicht korigieren.

Fehlt da vielleicht nur die "1" im Code???  Bitte mal testen.   ==> Resize(UBound(vOut, 1)) = vOut

mfg
Gast 123

[juvee]

Hi Noob,

jo, das ist vmtl dem Einlesen  deiner Daten geschuldet.

Ich ging davon aus, dass ebenfalls Daten in Spalte A existieren. Dem ist wohl nicht so, denn sonst hättest du das gewünschte Ergebnis erhalten.

Probiere den folgenden Code, hier werden die Daten der Spalte B ins Array (arr) eingelesen und dann verarbeitet (B2 bis B(letzte gefüllte Zelle) und die Ergebnisse nach C2:C letzte geschrieben

Code:

Sub x()
Dim rg As Range                    'Datenherkunft
Dim arr As Variant                  'Datenspeicher
'Daten einlesen
  Set rg = ActiveSheet.Range("B1:B" & Cells(Rows.Count, 2).End(xlUp).Row)  'nur aus Spalte B:B
  arr = rg.Offset(1).Resize(rg.Rows.Count - 1).Value2
 
'Daten be-/verarbeiten
Dim i As Long
Dim vOut() As Variant
Dim objRegExp As Object

  ReDim vOut(1 To UBound(arr, 1))
'mit reguläremAusdruck alle Nicht-Ziffern als Ergebnis ermitteln
  Set objRegExp = CreateObject("VBScript.RegExp")
  objRegExp.Global = True
  objRegExp.MultiLine = True
  objRegExp.ignorecase = True
  objRegExp.Pattern = "\D*"

  For i = 1 To UBound(arr, 1)
    vOut(i) = objRegExp.Execute(arr(i, 1))(0)  'anfängliche nichtnumerischen Zeichen des Zelltextes nach vOut
  Next
  Set objRegExp = Nothing

'Ergebnisse nach Spalte C
  ActiveSheet.Range("C:C").ClearContents  'Daten im Ausgabebereich löschen
  ActiveSheet.Range("C2").Resize(UBound(vOut, 1)) = Application.Transpose( vOut )  'Ergebnis nach Spalte C schreiben
End Sub

Bei Anfragen ist es immer gut, gleich eine Beispieldatei upzuloaden, die die Struktur der OriginalDatei mit Musterdaten und ggfs 2 -5 händisch eingetragene gewünschte Ergebnisse enthält.

Das vereinfacht den Helfern die Arbeit/das Testen und beugt Fehlern wie oben ersichtlich vor.

VG Juvee