Wie kann man Text in separate Spalten nach Großbuchstaben in Excel aufteilen?
Beim Arbeiten mit Daten in Excel stoßen Sie möglicherweise gelegentlich auf Zellen, die mehrere zusammengefügte Wörter enthalten, wobei jedes Wort mit einem Großbuchstaben beginnt, zum Beispiel "VornameNachname" oder "UmsatzMärzApril". Für die Datenbereinigung oder -analyse müssen Sie den Inhalt möglicherweise an jedem Großbuchstaben in separate Spalten aufteilen, wie im folgenden Screenshot dargestellt. Allerdings bietet Excel keine direkte integrierte Funktion, um dies durch Aufteilung nach Großbuchstaben zu erreichen. Welche praktischen Ansätze können Ihnen helfen, die Wörter schnell und genau in einzelne Spalten basierend auf Großbuchstaben aufzuteilen?
Unten finden Sie drei effektive Lösungen für dieses Problem, jede mit ihrem eigenen Anwendungsszenario und ihren Stärken:
Text in separate Spalten nach Großbuchstaben mit Formeln aufteilen
Wenn Ihre Daten aus Zellwerten bestehen, die nur zwei durch Großbuchstaben verbundene Wörter enthalten, können Excel-Formeln helfen, die Inhalte in separate Spalten aufzuteilen. Diese Lösung ist unkompliziert, erfordert keinen Code oder Add-ins und eignet sich für kleinere Datensätze oder weniger komplexe Aufteilungsbedürfnisse.
1. Geben Sie in einer leeren Zelle neben Ihren Daten (z. B. Zelle C2) die folgende Array-Formel ein, um das erste Wort aus Zelle A2 zu extrahieren (wobei A2 den zu trennenden Text enthält):
=LEFT(A2,SMALL(FIND(CHAR(ROW(INDIRECT("65:90"))),A2&"ABCDEFGHIJKLMNOPQRSTUVWXYZ"),2)-1)Nachdem Sie die Formel eingegeben haben, drücken Sie unbedingt gleichzeitig Strg + Umschalt + Enter. Dies ist für Array-Formeln in einigen Excel-Versionen erforderlich. Wenn es richtig gemacht wurde, erscheinen geschweifte Klammern {} um Ihre Formel in der Formelleiste. Diese Formel funktioniert, indem sie nach dem zweiten Großbuchstaben in Ihrer Zelle sucht und dann alle Zeichen vor dieser Position extrahiert, wodurch das erste Wort erhalten wird.
2. Um die Formel auf andere Einträge anzuwenden, wählen Sie Zelle C2 aus, dann ziehen Sie den Ausfüllkästchen nach unten, um die Formel in weitere Zellen in der Spalte zu füllen. Alle entsprechenden ersten Wörter für jede Zeile werden automatisch extrahiert.
3. Als Nächstes geben Sie in der Zelle rechts daneben (z. B. D2) die folgende Formel ein, um den Rest des Textes (das zweite Wort) zu extrahieren:
=REPLACE(A2,1,LEN(C2),"")Diese Formel entfernt das erste Wort, das im vorherigen Schritt extrahiert wurde, sodass nur das zweite Wort übrig bleibt. Drücken Sie einfach die Eingabetaste, um zu bestätigen.
4. Ähnlich dazu wählen Sie Zelle D2 aus und verwenden das Ausfüllkästchen, um diese Formel nach unten in andere Zeilen zu ziehen. Der Text jeder Zelle in Ihrem ursprünglichen Bereich wird nun in zwei Spalten aufgeteilt, getrennt durch die Position des Großbuchstabens.
Entfesseln Sie die Magie von Excel mit Kutools AI
- Intelligente Ausführung: Führen Sie Zellenoperationen durch, analysieren Sie Daten und erstellen Sie Diagramme – alles angetrieben durch einfache Befehle.
- Benutzerdefinierte Formeln: Erstellen Sie maßgeschneiderte Formeln, um Ihre Arbeitsabläufe zu optimieren.
- VBA-Codierung: Schreiben und implementieren Sie VBA-Code mühelos.
- Formelinterpretation: Verstehen Sie komplexe Formeln mit Leichtigkeit.
- Textübersetzung: Überwinden Sie Sprachbarrieren in Ihren Tabellen.
Text in separate Spalten nach Großbuchstaben mit VBA-Code aufteilen
Die oben genannte formelbasierte Methode eignet sich gut für Fälle, in denen nur zwei Wörter geteilt werden müssen, aber nicht für Zellen, die mehr als zwei verkettete Wörter enthalten (z. B. 'VornameZweitnameNachname'). In solchen Fällen können Sie VBA-Code verwenden, um automatisch vor jedem Großbuchstaben ein Leerzeichen einzufügen, um die Wörter zu segmentieren. Nach dem Hinzufügen dieser Leerzeichen können Sie die integrierte Excel-Funktion 'Text in Spalten' verwenden, um den Text in einzelne Spalten aufzuteilen.
Zuerst wenden Sie VBA-Code an, um Leerzeichen vor dem Großbuchstaben hinzuzufügen, um die Wörter zu trennen.
1. Drücken Sie ALT + F11, um das Microsoft Visual Basic for Applications-Editorfenster zu öffnen.
2. Klicken Sie im VBA-Editor auf Einfügen > Modul und fügen Sie den folgenden Code in das neue Modulfenster ein.
VBA-Code: Leerzeichen vor jedem Großbuchstaben in einer Zelle einfügen
Function SplitWords(ByVal Str As String) As String
'updateby Extendoffice 20151128
Dim I As Integer
SplitWords = Left(Str, 1)
For I = 2 To Len(Trim(Str))
If (Asc(Mid(Str, I, 1)) > 64) And _
(Asc(Mid(Str, I, 1)) < 91) And _
(Mid(Str, I - 1, 1) <> " ") Then _
SplitWords = SplitWords & " "
SplitWords = SplitWords & Mid(Str, I, 1)
Next
End Function
Nachdem Sie den Code eingegeben oder eingefügt haben, schließen Sie den VBA-Editor, um zum Arbeitsblatt zurückzukehren. Dieser Code definiert eine neue Funktion namens splitwords, die Sie wie eine reguläre Arbeitsblattformel verwenden können. Beachten Sie, dass möglicherweise Makros aktiviert sein müssen, um diese Funktion zu verwenden, und Sie sollten Ihre Arbeitsmappe als .xlsm-Datei speichern, um den VBA-Code beizubehalten.
3. Geben Sie in einer leeren Zelle neben Ihren Daten die folgende Formel ein, um Leerzeichen vor jedem Großbuchstaben in Zelle A2 einzufügen:
=splitwords(A2)Diese Formel ruft die benutzerdefinierte Funktion auf, um jede Zelle zu verarbeiten. Nach dem Drücken der Eingabetaste sollten Sie Ihren Text mit vor jedem Großbuchstaben eingefügten Leerzeichen sehen, wodurch einzelne Wörter deutlich werden.
4. Kopieren Sie diese Formel für alle relevanten Zellen nach unten, indem Sie das Ausfüllkästchen verwenden, sodass jede Zeile entsprechend verarbeitet wird.
5. Zu diesem Zeitpunkt enthalten die Zellen Formeln. Sie möchten die Werte möglicherweise woanders kopieren und einfügen, um nur den verarbeiteten Text ohne Formelverknüpfung beizubehalten. Wählen Sie dazu die verarbeiteten Zellen aus, kopieren Sie sie, klicken Sie dann mit der rechten Maustaste und wählen Sie 'Inhalte einfügen' > 'Werte' an einer gewünschten Stelle.
Zweitens wenden Sie die Funktion 'Text in Spalten' an, um die Zelleninhalte durch das Leerzeichen in separate Spalten aufzuteilen.
6. Wählen Sie nun die Zellenwerte mit Leerzeichen aus (die Ergebnisse aus dem vorherigen Schritt). Gehen Sie in der Menüleiste zu Daten > Text in Spalten. Wenn der Assistent zum Konvertieren von Text in Spalten erscheint (Schritt 1), wählen Sie die Option 'Trennzeichen'.
7. Klicken Sie auf Weiter, und wählen Sie im Schritt 2 die Option Leerzeichen unter Trennzeichen aus. Dies teilt Excel mit, Leerzeichen als Trennpunkt für die Aufteilung der Daten in Spalten zu verwenden.
8. Im Schritt 3 stellen Sie sicher Allgemein ist für das Spalten-Datenformat ausgewählt (oder wählen Sie ein anderes Format nach Bedarf), und wählen Sie die Zelle, in der die geteilten Daten erscheinen sollen, indem Sie 
. Stellen Sie sicher, dass der Zielbereich keine vorhandenen Daten überschreibt.
9. Klicken Sie auf Fertig, und Excel wird den Text in mehrere Spalten aufteilen, wobei jede eine Wort darstellt, das ursprünglich durch einen Großbuchstaben getrennt war.
Wenn Sie einen Fehler feststellen, bei dem einige Wörter nicht wie erwartet geteilt werden, überprüfen Sie auf Ausnahmesituationen wie Akronyme (mehrere aufeinanderfolgende Großbuchstaben) oder nachstehende Leerzeichen. Erwägen Sie, Ihren VBA-Code zu verfeinern oder Ihre Daten auf Ausreißer zu überprüfen. Denken Sie auch daran, regelmäßig zu speichern, wenn Sie VBA verwenden oder Batch-Datenoperationen durchführen.
Diese Methode ist besonders hilfreich beim Umgang mit inkonsistenter Textlänge oder variabler Wortanzahl pro Zelle. Allerdings fühlen sich einige Benutzer möglicherweise weniger wohl damit, VBA-Code auszuführen, in welchem Fall Sie die untenstehenden alternativen Methoden in Betracht ziehen könnten.
Text in separate Spalten nach Großbuchstaben mit Kutools für Excel aufteilen
Kutools für Excel bietet eine benutzerfreundliche und effiziente Lösung für Benutzer aller Qualifikationsstufen, um Text nach Großbuchstaben aufzuteilen, insbesondere nützlich für große Datensätze oder repetitive Aufgaben. Durch die gemeinsame Verwendung seiner Funktionen 'Text hinzufügen' und 'Zellen aufteilen' können Sie den Vorgang mit minimalem Aufwand und Risiko abschließen.
1. Wählen Sie die Zellen aus, die die Originaldaten enthalten, die Sie aufteilen möchten.
2. Klicken Sie auf Kutools > Text > Text hinzufügen. Dieses Tool hilft Ihnen, ein Zeichen oder Trennzeichen – wie ein Leerzeichen – vor jedem Großbuchstaben einzufügen.
3. Geben Sie im Dialogfeld 'Text hinzufügen' ein Leerzeichen (oder ein anderes Trennzeichen, falls benötigt) in das Texteingabefeld ein. Wählen Sie dann unter 'Erweitert' die Option '1. Buchstabe ist groß'. Dies teilt Kutools mit, Ihr gewähltes Trennzeichen vor jedem Großbuchstaben in den ausgewählten Zellen hinzuzufügen.
4. Klicken Sie auf OK. Kutools fügt automatisch Leerzeichen vor jedem Großbuchstaben ein und bereitet Ihre Daten sofort für die Aufteilung vor.
5. Als nächstes, mit dem weiterhin ausgewählten bearbeiteten Bereich, gehen Sie zu Kutools > Zusammenführen & Aufteilen > Zellen aufteilen, um mit der Trennung des Textes in Spalten durch Trennzeichen zu beginnen.
6. Wählen Sie im Dialogfeld 'Zellen aufteilen' unter dem Abschnitt Typ die Option 'Daten aufteilen' aus, und aktivieren Sie das Kontrollkästchen Leerzeichen (oder dasjenige, das zuvor hinzugefügt wurde), um anzugeben, wo die Trennungen erfolgen sollen.
7. Klicken Sie auf OK. Es wird eine Eingabeaufforderung erscheinen, die es Ihnen ermöglicht, die Startzelle für die geteilten Ergebnisse auszuwählen. Geben Sie das Ziel sorgfältig an und stellen Sie sicher, dass genügend Platz für die geteilten Spalten vorhanden ist.
8. Klicken Sie abschließend auf OK, um den Aufteilungsvorgang abzuschließen. Ihre Daten werden nun an jedem Großbuchstaben in separate Spalten verteilt, effizient und genau.
Diese Methode ist ideal für komplexe, große oder häufig aktualisierte Daten und hilft, manuelle Formelanpassungen oder Codierung zu vermeiden. Denken Sie daran, dass Kutools für Excel seine Nutzbarkeit mit vielen anderen Funktionen erweitert, was die Effizienz bei alltäglichen und fortgeschrittenen Aufgaben erhöht.
Kutools für Excel jetzt herunterladen und kostenlos testen!
Die besten Produktivitätstools für das Büro
Stärken Sie Ihre Excel-Fähigkeiten mit Kutools für Excel und genießen Sie Effizienz wie nie zuvor. Kutools für Excel bietet mehr als300 erweiterte Funktionen, um die Produktivität zu steigern und Zeit zu sparen. Klicken Sie hier, um die Funktion zu erhalten, die Sie am meisten benötigen...
Office Tab bringt die Tab-Oberfläche in Office und macht Ihre Arbeit wesentlich einfacher
- Aktivieren Sie die Tabulator-Bearbeitung und das Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Tabs innerhalb desselben Fensters, statt in neuen Einzelfenstern.
- Steigert Ihre Produktivität um50 % und reduziert hunderte Mausklicks täglich!
Alle Kutools-Add-Ins. Ein Installationspaket
Das Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro und ist ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- All-in-One-Paket — Add-Ins für Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Ein Installationspaket, eine Lizenz — in wenigen Minuten einsatzbereit (MSI-kompatibel)
- Besser gemeinsam — optimierte Produktivität in allen Office-Anwendungen
- 30 Tage kostenlos testen — keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis — günstiger als Einzelkauf der Add-Ins