You can use the following formula with the LEFT and FIND function to extract all of the text before a space is encountered in some cell in Excel:
=LEFT(A2, FIND(" ", A2)-1)
This particular formula extracts all of the text in cell A2 that occurs before the first space is encountered.
The following example shows how to use this formula in practice.
Example: Using LEFT to Extract Text Before Space
Suppose we have the following list in Excel that provides a brief description of different basketball players including their team, position, and ranking:
Now suppose that we would like to extract only the team name for each player.
To do so, we can use the following formula with the LEFT and FIND functions in Excel to extract the text from the left side of each cell until a space is encountered:
=LEFT(A2, FIND(" ", A2)-1)
We can type this formula into cell B2 and then click and drag this formula down to each remaining cell in column B:
Column B now displays only the team name for each player in column A.
It’s worth noting that if no space is found in the Player Description column, the formula will return #VALUE! as a result.
To return a different value, simply use the IFERROR() function.
For example, we can use the following IFERROR() function to return “No space” if a space is not found in a given player description:
=IFERROR(LEFT(A2, FIND(" ", A2)-1), "No space")
The following screenshot shows how to use this formula in practice:
Note that you can also return a different value by simply replacing “No space” with some other value in the IFERROR() function.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: A Formula for LEFT Until Specific Character
Excel: How to Use MID Function to End of String
Excel: How to Use MID Function for Variable Length Strings