Hoe tekst te extraheren voor/na de tweede spatie of komma in Excel?
Bij het werken met tekstgegevens in Excel, kom je vaak strings tegen die meerdere spaties, komma's of andere scheidingstekens bevatten. Hoewel de functie Tekst naar Kolommen in Excel de inhoud van een cel kan splitsen in afzonderlijke kolommen op basis van een gekozen scheidingsteken, zijn er momenten dat je een specifiekere oplossing nodig hebt - zoals het extraheren van alles voor of na de tweede spatie of komma in een cel. Dit artikel beschrijft verschillende praktische methoden om dit te bereiken, inclusief formules die tekst richten voor of na de tweede spatie/komma, robuuste oplossingen met VBA-macros voor automatisering of grotere datasets, en veelzijdige opties met Power Query voor geavanceerde datapreprocessing.
Extraheer tekst voor de tweede spatie of komma met formule
Extraheer tekst na de tweede spatie of komma met formule
Extraheer tekst voor of na het tweede scheidingsteken (spatie/komma) met VBA-code
Extraheer tekst voor de tweede spatie of komma met formule
Als je het deel van een cel dat verschijnt voor de tweede spatie wilt isoleren, kun je een combinatie van Excel-functies gebruiken.
1. Voer de volgende formule in een lege cel in waar je het resultaat wilt weergeven (bijvoorbeeld, cel C2):
=IF(ISERROR(FIND(" ",A2,FIND(" ",A2,1)+1)),A2,LEFT(A2,FIND(" ",A2,FIND(" ",A2,1)+1)))2. Druk op Enter. Gebruik vervolgens het vulgreepje om de formule naar beneden te slepen voor alle rijen die je wilt verwerken. Het resultaat zal alle tekst voor de tweede spatie in elke bijbehorende rij weergeven - zie onderstaande schermafbeelding:
Tip: Als je tekst wilt extraheren voor de tweede komma of een ander scheidingsteken, vervang dan het spatie-teken (" ") in de formule door je gewenste scheidingsteken. Voor een komma bijvoorbeeld:
=IF(ISERROR(FIND(",",A2,FIND(",",A2,1)+1)),A2,LEFT(A2,FIND(",",A2,FIND(",",A2,1)+1)))Deze aanpak is snel en efficiënt voor eenmalige extracties of bij het werken met matig grote datasets. Echter, als de tekst zeer onregelmatig is of het aantal scheidingstekens sterk varieert tussen rijen, kunnen extra formuleaanpassingen nodig zijn. Let ook op overtollige spaties of lege cellen, wat de resultaten kan beïnvloeden. Controleer altijd de output dubbel bij het toepassen op kritieke gegevens.
Ontdek de Magie van Excel met Kutools AI
- Slimme Uitvoering: Voer celbewerkingen uit, analyseer gegevens en maak diagrammen – allemaal aangestuurd door eenvoudige commando's.
- Aangepaste Formules: Genereer op maat gemaakte formules om uw workflows te versnellen.
- VBA-codering: Schrijf en implementeer VBA-code moeiteloos.
- Formule-uitleg: Begrijp complexe formules gemakkelijk.
- Tekstvertaling: Overbrug taalbarrières binnen uw spreadsheets.
Extraheer tekst na de tweede spatie of komma met formule
Om tekst te extraheren die verschijnt na het tweede scheidingsteken in een cel, kun je de functies MID en VIND samen gebruiken.
1. Voer de volgende formule in de doelcel in (bijvoorbeeld D2):
=MID(A2, FIND(" ", A2, FIND(" ", A2)+1)+1,256)2. Druk op Enter. Sleep het vulgreepje naar beneden door alle rijen die extractie nodig hebben. Dit zal alles na de tweede spatie voor elke rij tonen, zoals in de onderstaande schermafbeelding wordt gedemonstreerd:
Tip: Om tekst te extraheren na de tweede komma of elk ander scheidingsteken, vervang dan het spatie-teken binnen de formule door je vereiste scheidingsteken. Voor een komma zou de formule zijn:
=MID(A2, FIND(",", A2, FIND(",", A2)+1)+1,256)Extraheer tekst voor of na het tweede scheidingsteken (spatie/komma) met VBA-code
Voor grotere datasets, herhaalde extracties of automatiseringsscenario's is het gebruik van een VBA-macro een effectieve oplossing. VBA stelt je in staat om tekst te extraheren voor of na de tweede spatie, komma of elk ander scheidingsteken - wat de efficiëntie verhoogt bij het werken met verschillende datastructuren of wanneer een herhalende routine nodig is.
1. Selecteer Ontwikkelaar > Visual Basic. In het pop-upvenster Microsoft Visual Basic for Applications, klik op Invoegen > Module, en voer de volgende code in het modulevenster in:
Sub ExtractTextSecondDelimiter()
Dim rng As Range
Dim cell As Range
Dim sep As String
Dim direction As String
Dim arr As Variant
Dim result As String
Dim pos1 As Long
Dim pos2 As Long
Dim xTitleId As String
Dim outputCell As Range
Dim i As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.Selection
Set rng = Application.InputBox("Select text range to extract from", xTitleId, rng.Address, Type:=8)
If rng Is Nothing Then Exit Sub
sep = Application.InputBox("Enter delimiter (e.g. space or comma)", xTitleId, " ", Type:=2)
If sep = "" Then Exit Sub
direction = Application.InputBox("Type 'before' for text before, 'after' for text after the 2nd delimiter", xTitleId, "before", Type:=2)
If direction = "" Then Exit Sub
Set outputCell = Application.InputBox("Select the first cell to output the result", xTitleId, Type:=8)
If outputCell Is Nothing Then Exit Sub
Application.ScreenUpdating = False
i = 0
For Each cell In rng
If Not IsEmpty(cell.Value) Then
pos1 = InStr(1, cell.Value, sep)
If pos1 > 0 Then
pos2 = InStr(pos1 + 1, cell.Value, sep)
If pos2 > 0 Then
If LCase(direction) = "before" Then
result = Left(cell.Value, pos2 - 1)
ElseIf LCase(direction) = "after" Then
result = Mid(cell.Value, pos2 + Len(sep))
Else
result = cell.Value
End If
Else
result = cell.Value
End If
Else
result = cell.Value
End If
outputCell.Offset(i, 0).Value = result
End If
i = i + 1
Next
Application.ScreenUpdating = True
MsgBox "Extraction completed.", vbInformation, xTitleId
End Sub
2. Nadat je de code hebt geplakt, klik op 
Uitvoeren knop, of druk op F5 om uit te voeren. Er verschijnt een dialoogvenster waarin je wordt gevraagd je gegevensbereik te selecteren, voer vervolgens het scheidingsteken in, specificeer of je de tekst “voor” of “na” de tweede keer wilt, en selecteer tot slot een cel om het resultaat uit te zetten. De macro zal het geëxtraheerde resultaat in je gespecificeerde cel uitvoeren.
Beste productiviteitstools voor Office
Verbeter je Excel-vaardigheden met Kutools voor Excel en ervaar ongeëvenaarde efficiëntie. Kutools voor Excel biedt meer dan300 geavanceerde functies om je productiviteit te verhogen en tijd te besparen. Klik hier om de functie te kiezen die je het meest nodig hebt...
Office Tab brengt een tabbladinterface naar Office en maakt je werk veel eenvoudiger
- Activeer tabbladbewerking en -lezen in Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster, in plaats van in nieuwe vensters.
- Verhoog je productiviteit met50% en bespaar dagelijks honderden muisklikken!
Alle Kutools-invoegtoepassingen. Eén installatieprogramma
Kutools for Office-suite bundelt invoegtoepassingen voor Excel, Word, Outlook & PowerPoint plus Office Tab Pro, ideaal voor teams die werken met Office-toepassingen.
- Alles-in-één suite — invoegtoepassingen voor Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Eén installatieprogramma, één licentie — in enkele minuten geïnstalleerd (MSI-ready)
- Werkt beter samen — gestroomlijnde productiviteit over meerdere Office-toepassingen
- 30 dagen volledige proef — geen registratie, geen creditcard nodig
- Beste prijs — bespaar ten opzichte van losse aanschaf van invoegtoepassingen