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**..

Table of Contents

## 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 u**ser-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)…

## Leave a Reply

You must be logged in to post a comment.