This post explains that how to extract a substring before the first specific character, such as: comma or space character. How to get text before the first comma in a text string using a formula or User defined function with vba code in excel 2013/2016/2019/365.

1. Extract text before first comma or space

If you want to extract text before the first comma or space character in cell B1, you can use a combination of the LEFT function and FIND function to create a formula as follows:

=LEFT(B1,FIND(" ",B1,1)-1)

Let’s see how this formula works:

= FIND(” “,B1,1)

The FIND function returns the position of the first empty string in a text string in Cell B1, it returns 6. And the returned value goes into the LEFT function as its num_chars argument.

=LEFT(B1,FIND(” “,B1,1)-1)

The LEFT function extracts a specified number of the characters from a text string in Cell B1, starting from the leftmost character.

If you want to extract a substring before the first comma in a text string in Cell B2, you just need to change the empty string to comma string in the above formula, like this:

=LEFT(B1,FIND(“,”,B1,1)-1)

2. Extract text before first comma or space Using a User-Defined Function

You can also use a VBA macro to extract the text before the first comma or space in Excel. just do the following steps:

Step1: click on Visual Basic button under Code group.

Step2: In the VBA editor, go to Insert -> Module to create a new module.

Step3: paste the following VBA Macro code into the window. This custom function will extract the text before the first command or space.

Function ExtractTextBeforeCommaOrSpace(text As String) As String
    Dim position As Long
    position = InStr(text, ",")
    If position = 0 Then
        position = InStr(text, " ")
    End If
    If position > 0 Then
        ExtractTextBeforeCommaOrSpace = Left(text, position - 1)
    Else
        ExtractTextBeforeCommaOrSpace = text
    End If
End Function

Step4: Go back to Excel and enter a cell where you want to use the function. Type the following formula in Cell C1:

= ExtractTextBeforeCommaOrSpace(B1)

The function will automatically extract the desired text before the first comma or space.

3. Video: Extract text before first comma or space

This video will show you how to get text before the first comma in a text string using a formula or User defined function with vba code in excel 2013/2016/2019/365.

4. SAMPLE FIlES

Below are sample files in Microsoft Excel that you can download for reference if you wish.

5. Related Formulas

• Extract Text between Parentheses
  If you want to extract text between parentheses in a cell, then you can use the search function within the MID function to create a new excel formula…
• Extract Text between Brackets
  If you want to extract text between brackets in a cell, you need to create a formula based on the SEARCH function and the MID function….
• Extract Text between Commas
  To extract text between commas in Cell B1, you can use the following formula based on the SUBSTITUTE function, the MID function and the REPT function…..
• Split Text String by Line Break in Excel
  When you want to split text string by line break in excel, you should use a combination of the LEFT, RIGHT, CHAR, LEN and FIND functions. The CHAR (10) function will return the line break character, the FIND function will use the returned value of the CHAR function as the first argument to locate the position of the line break character within the cell B1.…
• Split Text and Numbers
  If you want to split text and numbers, you can run the following excel formula that use the MIN function, FIND function and the LEN function within the LEFT function in excel. And if you want to extract only numbers within string, then you also need to RIGHT function to combine with the above functions..…

6. Related Functions

• Excel LEFT function
  The Excel LEFT function returns a substring (a specified number of the characters) from a text string, starting from the leftmost character.The syntax of the LEFT function is as below:= LEFT(text,[num_chars])….
• Excel FIND function
  The Excel FIND function returns the position of the first text string (sub string) within another text string.The syntax of the FIND function is as below:= FIND(find_text, within_text,[start_num])…