ExcelInsider Team
It’s very common to get into circumstances where you need to extract particular information from text strings when working with data in Excel. We are familiar with functions like LEFT and MID to retrieve text from the beginning or middle of a string. Extracting content from the right side requires a slightly different approach than the others. Knowing how to separate portions of a string from the right can help you save time and improve process when working with file names, codes, IDs, and other structured information.
➤ Suppose your text is in cell B2. Create a separate column C adjacent to it.
➤ Enter the following formula in cell C2: =RIGHT(B2,4)
➤ Press Enter. You’ll see the characters you wanted to extract in cell C2.
➤ Drag the Fill handle down to apply the formula to other rows.
We’ll look at three efficient ways to locate and retrieve data from the right side of a string in Excel in this guide.
Use RIGHT Function to Find from Right Side of String
The RIGHT function is the most direct method to extract a fixed number of characters from the end of a string. This method is ideal when you know exactly how many characters you want to extract and when those characters are always in the same position relative to the end of the string.
Suppose, we have a dataset containing employee names and their IDs like this:
Here, the Employee IDs are inserted as AJ-1234 such format. We only want to extract the last digits of their IDs from the entire string for proper sorting. So, we need to extract 4 characters from the right side of the string.
Follow these steps to find and extract the characters from the right side in Excel very easily:
➤ Create a new column to extract the ID numbers only.
➤ Click on cell C2 and type the formula:
=RIGHT(B2,4)
➥ In this case, the cell part locates the cell where the entire text string is and the num_chars part extracts the 4 digits from the right side of the string.
➤ Click and drag the fill handle beside cell C2 until cell C9 to apply the formula in all the cells of column C.
➤ Now, the last 4 digits of the Employee ID are returned in column C.
Combine FIND and RIGHT Functions to Find from Right Side of String
The basic RIGHT function is insufficient when the amount of characters to be extracted from the right varies. Combining RIGHT with FIND and LEN function is helpful in this situation. This approach is perfect for semi-structured data, such as “Name-ID” forms. It dynamically determines the number of characters that follow a particular delimiter, which is a dash(-) in this case.
In this dataset, the ID numbers have a variety of characters instead of a fixed number of characters. So, we will need to locate the dash and extract up to the dash from the right side of the string.
Combine FIND and RIGHT functions to achieve this by following the steps below:
➤ Create a new column to extract the ID numbers only.
➤ Click on cell C2 and type the formula:
=RIGHT(B2, LEN(B2) - FIND("-", B2))
➥ LEN(B2) - FIND("-", B2)) calculates how many characters are after the dash.
➥ RIGHT function extracts that many characters from the right side of the string.
➤ Click and drag the fill handle beside cell C2 until cell C9 to apply the formula in all the cells of column C.
➤ We can see, despite being of variable lengths, the correct number of characters for IDs are extracted from the right side in column C.
Utilize TEXTAFTER Function for Excel 365/2021+ to Find from Right Side of String
TEXTAFTER is a newer function introduced in Excel 365 and 2021 that makes extracting text after a specific delimiter effortless. Unlike the previous combination of FIND and RIGHT functions, TEXTAFTER function is more readable and easier to use. It’s perfect for users who frequently work with structured data in Excel 365 or 2021+ versions.
Here is a step by step guide on how to specify the delimiter and use the TEXTAFTER function in Excel:
➤ Create a new column to extract the ID numbers only.
➤ Click on cell C2 and type the formula:
=TEXTAFTER(B2, "-")
Here, Excel will find the first occurrence of the delimiter (–) and return everything after it.
➤ Click and drag the fill handle beside cell C2 until cell C9 to apply the formula in all the cells of column C.
➤ The column C now includes everything to the right of the delimiter, giving us the proper ID numbers as we wanted.
Frequently Asked Questions
Can I Find a Character From the Right Side Without Knowing the Exact Number of Characters?
You can use a combination of RIGHT with FIND and LEN to find a character from the right side without knowing the number of characters you want to extract. You can also use modern functions like TEXTAFTER in Excel 365/2021+ to dynamically determine how much text to extract based on a delimiter.
Which Method Is The Best for Fixed-Length IDs?
If the values of the dataset are always the same length, the simplest method is the RIGHT function with a fixed num_chars value. It’s fast and easy to apply for fixed length IDs.
Will These Formulas Update Automatically if My Data Changes?
Yes, all these formulas will update with data changes. As long as your formulas reference the correct cells, they will recalculate automatically when your source data changes.
Wrapping Up
Extracting text from the right side of a string in Excel is a common data-cleaning task. The RIGHT method is the quickest choice for simple scenarios with fixed lengths. Modern functions like TEXTAFTER or combining RIGHT with FIND provide flexibility and better formulas for dynamic settings.
Gaining proficiency in these techniques will enable you to handle anything from basic ID extraction to intricate delimiter text parsing, improving the effectiveness and accuracy of your Excel operation.
