In the previous post, we talked that how to extract substring before the first comma or space or others specific characters in excel. And this post will guide you how to extract text after the first comma or space character in a text string using a formula and VBA code..

1. Extract Text after First Comma or Space Using Formula

If you want to get substring after the first comma character from a text string in Cell B1, then you can create a formula based on the MID function and FIND function or SEARCH function as follows:

=MID(B1,FIND(",",B1)+1,LEN(B1))

or

=MID(B1,SEARCH(",",B1)+1,LEN(B1))

Let’s see how this formula works:

=LEN(B1)

The LEN function returns the number of characters in a text string in Cell B1. The returned result goes into the MID function as its num_chars argument.

=FIND(“,”,B1)+1

The FIND function returns the position of the first comma character in Cell B1. It returns 7. And then add 1 to get the position of the first character after comma character. The returned value goes into the MID function as its start_num argument.

=MID(B1,FIND(“,”,B1)+1,LEN(B1))

So far, you got the values of the start_num and num_chars arguments from above FIND and LEN formula. And then the MID function extracts a substring based on the starting position and the number of the characters that you want to extract from a text string in Cell B1.

Last, if you want to extract a string after the first space character or others specific characters in a text string in cell B2, then you just need to change the comma character to space character in the above MID function, like this:

=MID(B1,FIND(" ",B1)+1,LEN(B1))

2. Extract Text after First Comma or Space using a User Defined Function with VBA Code

You can use the following VBA code to create a user-defined function in Excel that extracts text after the first comma or space in a cell. Just do the following steps:

Step1: Press Alt + F11 to open the VBA editor in your current worksheet.

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

Step3: Paste the below code into the module. Save the module and close the editor.

Function ExtractTextAfterCommaOrSpace_Excelhow(text As String) As String
    Dim pos As Integer
    pos = InStr(text, ",")
    If pos = 0 Then
        pos = InStr(text, " ")
    End If
    If pos = 0 Then
        ExtractTextAfterCommaOrSpace_Excelhow = ""
    Else
        ExtractTextAfterCommaOrSpace_Excelhow = Trim(Mid(text, pos + 1))
    End If
End Function

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

=ExtractTextAfterCommaOrSpace_Excelhow(B1)

Step5: press Enter to apply this formula. And the extracted text would be returned.

3. Video: Extract Text after First Comma or Space

This video will demonstrate how to extract text after the first comma or space in Excel using both a formula and VBA code.

4. 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…..
• Extract word that starting with a specific character
  Assuming that you have a text string that contains email address in Cell B1, and if you want to extract word that begins with a specific character “@” sign, you can use a combination with the TRIM function, the LEFT function, the SUBSTITUTE function ….
• 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….

5. Related Functions

• Excel MID function
  The Excel MID function returns a substring from a text string at the position that you specify.The syntax of the MID function is as below:= MID (text, start_num, 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])…
• Excel SEARCH function
  The Excel SEARCH function returns the number of the starting location of a substring in a text string.The syntax of the SEARCH function is as below:= SEARCH  (find_text, within_text,[start_num])…
• Excel LEN function
  The Excel LEN function returns the length of a text string (the number of characters in a text string).The LEN function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEN function is as below:= LEN(text)…