Excel right function

The right function specified the extraction of elements from the right side of the string. The specified can be in the form of numbers, characters, or symbols. It also includes spaces between two or more words or numbers. We can specify the number of characters we want to extract from the middle of the given data element. We can also say that RIGHT() function returns the last characters of the string.

It is given by:

RIGHT(text, Number_of_characters)

Where,

Text refers to the specified cell from which we want to extract the elements.

Number_of_characters refers to the number of characters we want to extract from the given data in excel.

Note: Unlike MID() function, it does not require start_position parameter in the function. It is because RIGHT() function starts from the right end of the string by default.

For example,

Data string = 5674abc123

Here, we want to extract the code in the form of characters from the right end of each string. So, we will use the RIGHT() function.

The syntax for the above string will be:

=RIGHT(5674abc123, 4)

Excel will start extracting from the right end of the given string. There are 10 characters in a string. The specified number_of_characters are 4. So, excel will start from 10th character from the right to the 7th character. It means 10th, 9th, 8th, and 7th position of the characters in the string. Thus the last four characters will appear as the output.

Output: c123

Here, we will also discuss RIGHTB() function, another part of the RIGHT() function that counts double-byte character as 2. The RIGHT() function is used for the languages that uses the single-byte character. It will be discussed later in the topic.

Important points

  • If the number_of_characters specified are greater than the length of the text, excel will return characters upto the end of the text.
    For example,
    Text: 67546003
    Syntax: RIGHT(67546003, 15)
    Here, excel will return 67546003, i.e., characters upto the end of the end starting from the right end to the left end.
  • If the number_of_characters are negative, excel will return ‘#VALUE!’
    For example,
    Text: 1234ABCD
    Syntax: RIGHT(1234ABCD, -4)
    Here, excel will return ‘#VALUE!’

Let’s first consider some examples of the excel RIGHT() function.

Examples

Example 1: To extract the number code from the given string.

Consider the below steps:

  1. Click on the cell in the front of the given string.
  2. Type ‘=RIGHT(A2,4),‘ as shown below:
    Excel right function
  3. Press Enter.
  4. The extracted from starting from the right end upto 4 characters will appear, as shown below:
    Excel right function
  5. Drag and drop down to the last data cell of the column. The RIGHT() function will be applied automatically to the rest of the cells. It is shown below:

Excel right function

The RIGHT() function separately on the specified cells will work as:

Note: Like RIGHT() function, LEFT() and MID() functions are also used to extract the number of characters from the given string. The LEFT() function extracts the characters from the left side of the specified string and MID() function extracts the characters starting from the middle of the specified string.

Example 2: To extract a word from the sentence

Consider the below steps:

  1. Click on the cell in the front of the given sentence.
  2. Type ‘=RIGHT(A2,10),‘ as shown below:
    Excel right function
  3. Press Enter.
  4. The extracted from starting from the right end upto 10 characters will appear, as shown below:
    Excel right function

Example 3: To extract the character code from the given string.

Consider the below steps:

  1. Click on the cell in the front of the given string.
  2. Type ‘=RIGHT(A3,3),‘ as shown below:
    Excel right function
  3. Press Enter.
  4. The extracted from starting from the right end upto 3 characters will appear, as shown below:
    Excel right function
  5. Drag and drop down to the last data cell of the column. The RIGHT() function will be applied automatically to the rest of the cells. It is shown below:

Excel right function

RIGHTB() function in excel

Let’s have a quick look at the RIGHTB() function in excel to eliminate the confusion between the RIGHT() function and RIGHTB() function.

The RIGHTB() function considers the double byte character as 2, when we enable the editing a language that supports DBCS (Double-Byte Character Set) and set it as a default language. Otherwise, it considers 1. In the case of RIGHT() function, excel considers both the double byte character and single byte character as 1.

The double-byte languages are Chinese, Korean, and Japanese. Such languages are difficult to represent in the single-quote.

It is given by:

RIGHTB(text, start position, Number_of_characters)

Where,

Text refers to the specified cell from which we want to extract the elements.

Start_position refers to the position from where we want to start extracting.

Number_of_bytes refers to the number of characters we want to extract from the given data in excel.

The output and the conditions of the RIGHTB() are similar to that of RIGHT() function.

Let’s consider some examples.

Example :

Text: Priya Raheja

Syntax: =RIGHTB(Priya Raheja, 6)

Value returned: Raheja

Important points

  • The number of bytes should be greater than or equal to 0.
  • If the numbers of bytes are excluded, it is assumed as 1.
  • If the specified numbers of bytes are greater than the length of the string, it will return the full string.