How can I extract characters from the right in Excel until the first space?

By /

The process of extracting characters from the right in Excel until the first space involves using the RIGHT and FIND functions. These functions allow the user to specify the number of characters to be extracted from the right, and the location of the first space in the text string. This method is useful for separating first and last names in a single cell or for extracting specific information from a longer text string. By utilizing this technique, users can efficiently manipulate and organize data in Excel for various purposes.

Excel: Extract Characters from Right Until Space

You can use the following formula in Excel to extract all characters from the right side of a cell until a space is encountered:

=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",255)),255))

This particular example extracts all of the characters on the right side of the string in cell A2 until a space is encountered.

For example, if cell A2 contains The Dallas Mavericks then this formula would return Mavericks since this represents all of the characters on the right side of the cell until a space is encountered.

The following example shows how to use this formula in practice.

Example: Extract Characters from Right Until Space in Excel

Suppose we have the following list of basketball team names in Excel:

We can type the following formula into cell B2 to extract all of the characters on the right side of the team name of cell A2 until a space is encountered:

=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",255)),255))

We can then click and drag this formula down to each remaining cell in column B:

Excel extract characters from right until space

Column B now displays all of the characters on the right side of each cell in column B until a space is encountered.

Notice that if multiple spaces are present in a cell, the formula is capable of identifying the last space and extracts only the characters to the right of it.

Note: The most recent versions of Excel now offer a TEXTAFTER function, which you can use to extract the text from a cell after a specific character.

You can use the following syntax to extract all characters to the right of the last space in a cell:

=TEXTAFTER(A2, " ", -1)

Column B now all of the characters on the right side of each cell in column B until a space is encountered.

Notice that this formula produces the same results as the previous formula.

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

x

Related terms:

  1. Excel: Extract Characters from Right Until Space
  2. How can a formula in Excel be used to extract characters from the right until a specific character is reached?
  3. How can I extract text to the right of a space in Excel?
  4. Excel: Extract Text Right of Space
  5. How do I extract text after the last space in a cell in Excel?
  6. How do I extract text between second and third space in Excel?
  7. Count Specific Characters in Google Sheets How to count specific characters in google sheets?
  8. How to extract text between two characters in Excel?
  9. How do I extract the first 5 characters from a cell in Excel?
  10. How can I keep a cell in Excel blank until data is entered?