How to extract text before first comma or space

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 in excel.

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)

extract text before first comma1

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)

extract text before first comma2

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)

extract text before first comma3


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

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])…

Comments

So empty here ... leave a comment!

Leave a Reply

Your email address will not be published. Required fields are marked *

Sidebar