Home»Blog»Get Text Before First Space in Excel

# Get Text Before First Space in Excel Have you ever needed to extract text before the first space in Excel? Whether you're working with names, email addresses, or URLs, there are various scenarios where you might want to retrieve only the text before the first space. In this tutorial, we'll explore different methods to accomplish this task using Excel formulas and functions. Let's dive right in!

## Method 1: Using the LEFT Function

The `LEFT` function in Excel allows you to extract a specified number of characters from the beginning of a text string. By combining it with other functions, we can easily retrieve the text before the first space. Here's how you can do it:

1. Assuming your text is in cell A1, enter the following formula in another cell:

``````=LEFT(A1, FIND(" ", A1)-1)
``````

<!-- -->

2. Press Enter to get the result.

Let's break down the formula:

• The `FIND` function is used to locate the position of the first space in the text string.
• We subtract 1 from the result of `FIND` to exclude the space itself.
• The `LEFT` function then extracts the specified number of characters from the beginning of the text string.

### Example 1: Extracting First Name

Suppose you have a list of full names in column A, and you want to extract only the first names. Here's how you can do it using the `LEFT` function:

1. In cell B1, enter the following formula:

``````=LEFT(A1, FIND(" ", A1)-1)
``````

<!-- -->

2. Drag the formula down to apply it to the remaining cells.

Now you have successfully extracted the first names from the full names.

Let's say you have a list of email addresses in column A, and you want to extract only the usernames. Here's how you can achieve that using the `LEFT` function:

1. In cell B1, enter the following formula:

``````=LEFT(A1, FIND("@", A1)-1)
``````

<!-- -->

2. Drag the formula down to apply it to the remaining cells.

### Example 3: Extracting Domain Name from URL

Suppose you have a list of URLs in column A, and you want to extract only the domain names. Here's how you can accomplish that using the `LEFT` function:

1. In cell B1, enter the following formula:

``````=LEFT(A1, FIND("/", A1, 9)-1)
``````

<!-- -->

2. Drag the formula down to apply it to the remaining cells.

Now you have successfully extracted the domain names from the URLs.

## Method 2: Using the SUBSTITUTE and LEFT Functions

Another approach to extract text before the first space in Excel is by combining the `SUBSTITUTE` and `LEFT` functions. This method is useful when you have multiple spaces in the text string and only want to extract the text before the first space. Here's how you can do it:

1. Assuming your text is in cell A1, enter the following formula in another cell:

``````=LEFT(A1, FIND(" ", SUBSTITUTE(A1, " ", CHAR(1)))-1)
``````

<!-- -->

2. Press Enter to get the result.

Let's break down the formula:

• The `SUBSTITUTE` function replaces all spaces in the text string with a special character (in this case, CHAR(1)).
• The `FIND` function then locates the position of the first occurrence of the special character.
• We subtract 1 from the result of `FIND` to exclude the special character itself.
• Finally, the `LEFT` function extracts the specified number of characters from the beginning of the text string.

### Example 1: Extracting First Name

Using the same example as before, let's extract the first names from a list of full names using the `SUBSTITUTE` and `LEFT` functions:

1. In cell B1, enter the following formula:

``````=LEFT(A1, FIND(" ", SUBSTITUTE(A1, " ", CHAR(1)))-1)
``````

<!-- -->

2. Drag the formula down to apply it to the remaining cells.

Now you have successfully extracted the first names from the full names, even if there are multiple spaces in the text.

Let's extract the usernames from a list of email addresses using the `SUBSTITUTE` and `LEFT` functions:

1. In cell B1, enter the following formula:

``````=LEFT(A1, FIND("@", SUBSTITUTE(A1, "@", CHAR(1)))-1)
``````

<!-- -->

2. Drag the formula down to apply it to the remaining cells.

Now you have successfully extracted the usernames from the email addresses, regardless of the number of spaces in the text.

### Example 3: Extracting Domain Name from URL

Let's extract the domain names from a list of URLs using the `SUBSTITUTE` and `LEFT` functions:

1. In cell B1, enter the following formula:

``````=LEFT(A1, FIND("/", SUBSTITUTE(A1, "/", CHAR(1)), 9)-1)
``````

<!-- -->

2. Drag the formula down to apply it to the remaining cells.

Now you have successfully extracted the domain names from the URLs, even if there are multiple spaces in the text.

## Method 3: Using Text to Columns

If you prefer a non-formula approach, Excel's Text to Columns feature can also help you extract text before the first space. Here's how you can do it:

1. Select the range of cells containing the text you want to split.

2. Go to the Data tab in the Excel ribbon and click on Text to Columns.

3. In the Convert Text to Columns Wizard, choose the Delimited option and click Next.

4. Check the Space delimiter and uncheck all other delimiters. You can also choose to treat consecutive delimiters as one if needed. Click Next.

5. Choose the destination for the split text. You can either select an existing column or create a new one. Click Finish.

<!-- -->

Now Excel will split the text before the first space into separate columns, allowing you to extract the desired text easily.

### Example 1: Extracting First Name

Using the same example as before, let's extract the first names from a list of full names using the Text to Columns feature:

1. Select the range of cells containing the full names.
2. Go to the Data tab in the Excel ribbon and click on Text to Columns.
3. Follow the steps in the Convert Text to Columns Wizard as described above.

Now you have successfully extracted the first names from the full names using the Text to Columns feature.

Let's extract the usernames from a list of email addresses using the Text to Columns feature:

1. Select the range of cells containing the email addresses.
2. Go to the Data tab in the Excel ribbon and click on Text to Columns.
3. Follow the steps in the Convert Text to Columns Wizard as described above.

Now you have successfully extracted the usernames from the email addresses using the Text to Columns feature.

### Example 3: Extracting Domain Name from URL

Let's extract the domain names from a list of URLs using the Text to Columns feature:

1. Select the range of cells containing the URLs.
2. Go to the Data tab in the Excel ribbon and click on Text to Columns.
3. Follow the steps in the Convert Text to Columns Wizard as described above.

Now you have successfully extracted the domain names from the URLs using the Text to Columns feature.

## Related Content

In this tutorial, we explored different ways to extract text before the first space in Excel. We learned how to use the `LEFT` function, the `SUBSTITUTE` and `LEFT` functions, as well as the Text to Columns feature. By applying these techniques, you can easily manipulate text strings and extract the desired information. Remember to choose the method that best suits your specific scenario.

Now go ahead and put your newfound knowledge into practice! Happy Excel-ing!