RIGHT function in Excel, syntax and examples

The Right function in Excel is not a complicated function, but its usage is not well known. Basically, the Right function in Excel is used to get the character to the right of a certain string. However, this is not the whole meaning of this function, follow the article below to learn more about the Right function in Excel.

There are many problems that require users to use the application RIGHT function to do it faster, like taking the last 2 letters in the username of a list of customer data, for example ….

Instructions for using the RIGHT function in Excel, syntax and examples

RIGHT function used to cut the character to the right of the string, has a very simple syntax, is easy to understand, applies to all versions of Microsoft Excel and especially applicable to Google’s online Excel tool or has called Google Sheets.

How to use the RIGHT function in Excel – Example illustrations

Syntax: RIGHT (text, n)

– Inside: + Text: (Required parameter) string.

+ n: The number of characters to cut from the string.

Function: Cut out n characters in string text from the right.

A few notes:

– The parameter n must always be greater than or equal to 0
– If there is no n parameter, Excel will default to this value first
– If the parameter n is greater than the length of the string, the result will be returned whole string of characters
RIGHT function Always return text results, the characters may be numbers and you will misunderstand that these are numbers. This is not true, although the return values ​​look like numbers but it is always Text, depending on the particular case when combined with other functions, you will need to reformat the values. This result so that it is consistent when calculating and looking up.

Example 1: Cut the string with no parameter n:

Apply the function RIGHT to get column characters “HS Code” The case has no parameters n.

– On G5 We type the formula as follows: E6 = RIGHT (D6) and press Enter.

– Oh D6 is the cell containing the data you want to cut the string.

– Results will appear on the box E5

ham right

Example 2: Cut out 2 characters from the string in the “HS code”From the right.

We apply the function RIGHT to get the 2 characters of the column “HS code”.

– On E5 We type the formula as follows: E5 = RIGHT (D6, 2) and press Enter.

– Oh D6 is the cell containing the data you want to cut the string.

– Results will appear on the box E5

use right excel

How to use the Right function, example formula

In fact, the RIGHT function is rarely used on its own. Often in most cases the RIGHT function is used in conjunction with other Excel functions in complex formulas.

Extracts the string that follows a specific character

In case if you want to extract the string following a specific character, use the SEARCH function or the FIND function to locate that character, except for the position of the selected character in the total string returned. Using the LEN function, drag the number of characters you want to select from the right of the initial string.

General formula:

RIGHT (string, LEN (string) – SEARCH (character, string))

For example, cell A2 contains first and last names separated by spaces, your goal is to drag the same first name to another column. Apply the above formula, then enter A2 in the space of the string, and enter the characters in the space “”, as the formula below

= RIGHT (A2, LEN (A2) -SEARCH (“”, A2))

The above formula returns the following result:

ham right in excel

Similarly, you can extract the string that follows any other character, such as a comma, colon, or hyphen, etc. For example, to extract the string following the (-) sign, you apply the formula:

= RIGHT (A2, LEN (A2) -SEARCH (“-“, A2))

The above formula returns the following result:

ham right in excel

Extract string after final delimiter

When dealing with complex strings with a delimiter appearing multiple times, often you have to extract the text string after the final delimiter. To make things easier to understand, let’s refer to the example below:

ham right in excel

In the screenshot above, column A contains a list of error names. Your goal is to drag the error description after the colon to another column. However, one thing to note is that the number of colons in the original string is different. For example, cell A3 has 3 colons, and column A5 has only one.

The key here is to locate the last delimiter (i.e., the colon in this example) in the original string. To do this, you will have to use a combination of combined functions:

first. Get the number of separated characters in the original string.

First calculate the total length of the string using the LEN function:

LEN (A2)

The next step is to calculate the length of the string without the separator using the SUBSTITUTE function, replacing the colon:

LEN (SUBSTITUTE (A2, “:”, “”))

Finally subtract the original string length without a separator from the total string length:

LEN (A2) -LEN (SUBSTITUTE (A2, “:”, “”))

Make sure the above formulas work. You can enter these formulas in separate cells, and the result will return 2, the number of colons in A2.

2. Replace the final delimiter with a unique character. To extract the text after the last delimiter in the string, you will have to mark the final delimiter with a certain character. To do this, you replace the last delimiter (i.e., the colon) with the new character that does not appear in the original string, such as (#).

If you are familiar with the Excel SUBSTITUTE function syntax, this function has an optional fourth argument (instance_num) that allows the replacement of a specified character in a text string. And since the number of delimiters in the string has been calculated, just add the above function as the fourth argument in the other SUBSTITUTE function:

= SUBSTITUTE (A2, “:”, “#”, LEN (A2) -LEN (SUBSTITUTE (A2, “:”, “”)))

If you enter the above formula in another cell, the result will be the string: ERROR: 432 # Connection timed out.

3. Get the position of the last delimiter in the string. Depending on the character you use to replace the final delimiter, use the SEARCH function or the FIND function is not case-sensitive to determine the position of that character in the string. In this example, the delimiter (i.e., the colon) is replaced with the #, below is the formula used to find the position of the # sign:

= SEARCH (“#”, SUBSTITUTE (A2, “:”, “#”, LEN (A2) -LEN (SUBSTITUTE (A2, “:”, “”))))

In this example, the result is 10, which is the position of the # in the string that has been replaced.

4. Returns the string to the right of the last delimiter.

After knowing the position of the last delimiter in a string, all you need to do now is subtract the position of the delimiter, and RIGHT returns the string to the right of the separator. Last in the original string:

= RIGHT (A2, LEN (A2) -SEARCH (“$”, SUBSTITUTE (A2, “:”, “$”, LEN (A2) -LEN (SUBSTITUTE (A2, “:”, “”)))))

As you can see in the screenshot below, the formula works quite well:

ham right in excel

If you’re working on a large data set, with multiple cells containing different separators, you can nest the above formula in the IFERROR function to prevent possible errors:

= IFERROR (RIGHT (A2, LENa (A2) -SEARCH (“$”, SUBSTITUTE (A2, “:”, “$”, LEN (A2) -LEN (SUBSTITUTE (A2, “:”, “”)))) )), A2)

In case if a particular string does not contain the specified delimiter, the original string will be returned, as in line 6 in the screenshot below:

ham right in excel

How to remove the first N characters in a string

In addition to extracting a substring from the original string, the Excel RIGHT function is also used to remove the number of characters from the original string.

Assuming in the data set used in the previous example, if you want to delete the word ERROR appears at the beginning of each string and only leave the error code number and description. To do this, simply subtract the number of characters removed from the original total string length and use that number as the num_chars argument in the Excel RIGHT function:

RIGHT (string, LEN (string) -number_of_chars_to_remove)

In this example the first 6 characters are removed (including 5 letters and 1 colon) from the text string in cell A2, using the following formula:

= RIGHT (A2, LEN (A2) -6)

ham right in excel

Can Excel RIGHT function return a numeric value?

As mentioned above, the Excel RIGHT function always returns a text string value even if the original value is a number.

However, if you work with numbers and you want the results to be returned as numbers. The simplest solution is to nest the RIGHT function in the VALUE function, specifically designed to convert text strings into numbers.

For example, to pull the last 5 characters (postal code) from the string in cell A2 and convert the extracted characters into numbers, you use this formula:

= VALUE (RIGHT (A2, 5))

The screenshot below shows the results that show right-aligned numbers in column B, which differ from the left-aligned text string in column A:

ham right in excel

Why doesn’t the RIGHT function work with date values?

Because the Excel RIGHT function is designed to work with text strings, dates are represented by numbers in the Excel system. The RIGHT function cannot be partially retrieved as day, month or year. If you try to use the RIGHT function to do this, all you will get is the last numbers in the original series representing the date.

Suppose in cell A1 is January 18, 2017. If you try to use the RIGHT formula (A1,4) to extract the year, the result will return 2753, these are the last 4 numbers of 42753 representing January 18, 2017 in the Excel system.

ham right in excel

So how to get a specific part of a day? Simply use one of the following Excel functions:

The DAY function extracts 1 day: = DAY (A1)

MONTH function to get the month: = MONTH (A1)

YEAR function to drag by year: = YEAR (A1)

The screenshot below shows the result:

ham right in excel

If your date is represented by a text string, which usually happens when exporting data from an external source, you can use the RIGHT function to drag the last few characters in the string that represent a specific part of the day. month, such as day, month, year:

ham right in excel

Some Excel RIGHT function errors are not working and workarounds

If an Excel RIGHT function doesn’t work in a spreadsheet, it could be because:

first. In the original data, there is one or more spaces. To remove spaces and spaces in cells, use the Excel TRIM function or the Cell Cleaner add-in.

2. The num_chars argument is less than 0. Of course you won’t use negative numbers in your formula, but if the num_chars argument is calculated using another Excel function or nested Excel functions together, the RIGHT function will return the #VALUE error. !

Try checking nested functions again to find and fix the error.

3. The initial value is the date. As mentioned above, the RIGHT function cannot work with dates, so if the original values ​​are dates, the RIGHT function will return an error.

Above we have introduced to you the Right function in Excel, this function is used to get the right character of the string and through the illustrative examples, you will understand the syntax and usage of the Right function more.

In addition, if you want to get the middle character, you can use the Mid function in Excel, in a long character depth, getting the middle characters is relatively difficult if it is a list of thousands of columns, Using the Mid function in this case would be most effective

CONCATENATE function supports concatenating strings in columns, no matter how much data you have in the column to concatenate, with CONCATENATE function, everything will be solved in a neat way.

Do you often use the Right function in your actual work? If you have, please share with us some experience and tips on using the Right function with us by commenting below.


Add a Comment

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