How to use the RIGHT function in Excel?

The RIGHT function in Excel can extract a set number of characters from the right side. Using the RIGHT command in Excel, you can quickly and efficiently extract specific characters from the right side of a text string, making data manipulation tasks much more efficient.

  1. What is the RIGHT function in Excel? RIGHT function syntax.

The RIGHT function in Excel is a text function that extracts a specified number of characters from the right side of a text string. It creates a substring with the rightmost characters of the specified text, permitting users to edit and retrieve relevant information from text data. The RIGHT function is often used in data processing tasks such as: For example, you can extract a file extension from a file name, get a last name from a full name, or get a specific identifier from code. Provides an easy way to manipulate text in Excel formulas, allowing users to efficiently manage and analyze text data.

RIGHT function syntax:  =RIGHT(text, [num_chars])

Breakdown,

text: Required text string from which characters can be extracted.

num_chars:

  • Starting with the rightmost character, the number of characters to be extracted.
  • By omitting num_chars, the final character of the string is returned as the default value.
  • In case the total number of characters in a string is less than num_chars, then all strings are returned.
  • #VALUE is returned by the Right expression when num_chars is negative.
  1. How to use the RIGHT function in Excel?

Step 1: Take some names and their age, enter them into the table.

Placed the data here.

the RIGHT function in Excel

Step 2: Add the column from C1:C7 to get the output there.

A column has been selected.

the RIGHT function in Excel

Step 3: Here you need to use the formula to get the function. The formula is: =RIGHT(A2)

Entered the formula here.

the RIGHT function in Excel

Step 4: Now, press the enter button and you will get the output.

Here is the result below.

RIGHT function

Step 5: Use the same formula from Step-3 or Drag-sown the cursor from C2.

Here are all the RIGHT function results below.

RIGHT function

  1. How to get a substring after a certain character in Excel?

Step 1: Take some names and enter the full names into your Excel as shown.

Data has been placed here.

RIGHT function

Step 2: Add the column from B1:B5 to get a substring before a certain character there.

A column has been placed here.

RIGHT function

Step 3: Enter the formula into your Excel as shown. To find out t a substring before a certain character, the formula will be: =RIGHT(A2,7)

Here, A2 is the column range and 7 is for how many alphabets the name contains.

Here, Maxwell- it contains 7 characters.

Used the formula here.

RIGHT function

Step 4: Now, Press enter and you will get the result.

The result of substring after a certain character shows.

RIGHT function

 Step 5: Use the same formula from Step-3 and just change the column number and the number of characters in the name or Drag-sown the cursor from C2.

Here are all the outputs below.

RIGHT function

  1. How to Extract Substring Succeeding a Delimiter?

Step 1: Create a table with some codes as shown below.

Created the table here.

RIGHT function

Step 2: Add a column from B2:B6.

Added the column here.

RIGHT function

Step 3: Here is the Extract Substring Succeeding a Delimiter formula. The formula is: RIGHT(cell_reference,LEN(cell_reference)-SEARCH(“Delimiter”, cell_reference)) or =RIGHT(A2,LEN(A2)-SEARCH(“-“,A2))

Entered the formula here.

RIGHT function

Step 4: In the end, hit enter to get the Output.

Here is the Output below.

RIGHT function

Step 5: Use the same formula from Step-3 Drag-sown the cursor from C2.

Here are all the outputs below.

RIGHT function

  1. How to force the Excel RIGHT function to return a number?

Step 1: Here is working with the above example data.

Placed all the information into the table.

RIGHT function

Step 2: Now add another column from B1:B7 to get the result there.

Added another column here.

RIGHT function

Step 3: Now, use the formula. The formula will be: =VALUE(RIGHT(A2,2))

The formula has been placed according to its data.

RIGHT function

Step 4: Now, press the enter and get the result.

Here is the result below.

RIGHT function

Step 5: To get the result of the rest of the ID’s use this formula or drag-down the cursor from B2 to B7.

All the ID numbers are here.

RIGHT function

  1. Reasons of the RIGHT function cannot be used on dates?

  • In Excel, the RIGHT method is exclusively intended for handling text strings, not dates. Although Excel stores dates as serial numbers and can be manipulated with various date-related functions, the RIGHT function is not intended to interpret dates directly.
  • Despite this, a date can be converted to a text string using TEXT or other formatting functions, and then the RIGHT function is applied to the result. This method is not required for conversion.
  • When converted a date can be to a text string using TEXT, the RIGHT function then extracts the last two characters from this text string, which in this case represents the year. When working with dates in Excel, be aware that you need to take into account date formatting, regional settings, and possible errors caused by incorrect date representation.
  1. Why Excel RIGHT function not working?

If the right formula isn’t working correctly in your worksheet, it might be for one of the following reasons:

  1. The original data contains one or more spaces at the end. Extra spaces can be easily eliminated in Excel using the TRIM function or Trim Spaces tool.
  2. Argument ‘num_chars’ is less than zero. Of course, you rarely intentionally insert negative numbers into a formula, but if the num_chars argument is calculated by another Excel function or a combination of various functions, and the Right formula returns #VALUE! then the error If this happens, be sure to check your nested functions for errors.
  3. A date is the initial value. The RIGHT function’s inability to handle dates.

You may be interested:

  1. Financial Dashboards
  2. Sales Dashboards
  3. HR Dashboards
  4. Data Visualization Charts

Leave a Comment

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

Categories