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.
Table of Contents
1. Extract text before first comma or space
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.
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:
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:
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])…