How to extract text before / after the second space or comma in Excel? 

When working with text data in Excel, you may frequently encounter strings containing multiple spaces, commas, or other delimiters. While the Text to Columns feature in Excel can split a cell's content into separate columns based on a chosen delimiter, there are times when you need a more specific solution—such as extracting everything before or after the second space or comma in a cell. This article outlines several practical methods to accomplish this, including formulas that target text before or after the second space/comma, robust solutions using VBA macros for automation or larger datasets, and versatile options using Power Query for advanced data preprocessing.

Extract text before the second space or comma with formula

Extract text after the second space or comma with formula

Extract text before or after the second delimiter (space/comma) with VBA code

Extract text before the second space or comma with formula

If you want to isolate the portion of a cell that appears before the second space, you can use a combination of Excel functions. 

1. Enter the following formula into a blank cell where you wish to display the result (for example, cell C2):

=IF(ISERROR(FIND(" ",A2,FIND(" ",A2,1)+1)),A2,LEFT(A2,FIND(" ",A2,FIND(" ",A2,1)+1)))

2. Press Enter. Then use the fill handle to drag the formula down for all rows you want to process. The result will display all text before the second space in each corresponding row—see screenshot below:

=IF(ISERROR(FIND(",",A2,FIND(",",A2,1)+1)),A2,LEFT(A2,FIND(",",A2,FIND(",",A2,1)+1)))

This approach is quick and efficient for one-off extractions or when working with moderately sized datasets. However, if the text is highly irregular or the number of delimiters varies greatly between rows, additional formula adjustments may be necessary. Also, be mindful of extra spaces or empty cells, which may affect results. Always double-check output when applying to critical data.

Unlock Excel Magic with Kutools AI

• Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
• Custom Formulas: Generate tailored formulas to streamline your workflows.
• VBA Coding: Write and implement VBA code effortlessly.
• Formula Interpretation: Understand complex formulas with ease.
• Text Translation: Break language barriers within your spreadsheets.

Enhance your Excel capabilities with AI-powered tools. Download Now and experience efficiency like never before!

Extract text after the second space or comma with formula

To extract text that appears after the second space delimiter in a cell, you can use the MID and FIND functions together. 

1. Input the following formula into the target cell (for example, D2):

=MID(A2, FIND(" ", A2, FIND(" ", A2)+1)+1,256)

2. Hit Enter. Drag the fill handle down through all rows needing extraction. This will present everything after the second space for each row, as demonstrated in the screenshot:

=MID(A2, FIND(",", A2, FIND(",", A2)+1)+1,256)

Extract text before or after the second delimiter (space/comma) with VBA code

For larger datasets, repeated extractions, or automation scenarios, using a VBA macro is an effective solution. VBA allows you to extract text before or after the second space, comma, or any other delimiter—enhancing efficiency when working with different data structures or needing a repeatable routine.

1. Select Developer > Visual Basic. In the popped-up Microsoft Visual Basic for Applications window, click Insert > Module, and input the following code into the module window:

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. After pasting the code, click the Run button, or press F5 to execute. A dialog will prompt you to select your data range, then input the delimiter, specify whether you want the text “before” or “after” the second occurrence, and lastly, select a cell to out the result. The macro will output the extracted result in your specified cell.