Excel to Python: RIGHT Function - A Complete Guide | Mito
Home Icon






How to Use Excel's RIGHT Function in Pandas

Excel's RIGHT function is used to extract a specific number of characters from the right end of a text string. This can be especially useful in data preprocessing tasks like extracting the last name from a full name or extracting a unit label from a number.

This page explains how to achieve similar functionality in Python using pandas.

In pandas, string operations can be executed on string columns using the `.str` accessor. Below are some common ways to use pandas to replicate the RIGHT function in Excel:

In Excel, you would use a formula like =RIGHT(A1, 3) to extract the last 3 characters of the text in cell A1.

In pandas, you can achieve the same result by using the `.str` accessor followed by the slicing notation. Notice in the code below, that a negative value is used to indicate that the characters should be extracted from the right end of the string instead of the front.

# Extract the last 3 chracters
df['Extracted'] = df['Text'].str[-3:]

Mito is an open source library that lets you write Excel formulas in Python. Either write the formula directly in Python or use the RIGHT formula in the Mito Spreadsheet and generate the equivalent Python code automatically.

Mito's RIGHT function works exactly like it does in Excel. That means you don't need worry about managing data types, handling errors, or the edge case differences between Excel and Python formulas.

Install Mito to start using Excel formulas in Python.

# Import the mitosheet Excel functions
from mitosheet.public.v3 import *;

# Use Mito's RIGHT function
# Note: No need to convert the text column to a string
# because Mito's RIGHT formula does so automatically
df['Extracted'] = RIGHT(df['Text'], 3)

Sometimes you'll want to extract a variable number of characters based on the position of a character in the string. For example, extract the last name from a full name column by finding the position of the space character and extracting the characters after it.

In Excel, the formula would look like =RIGHT(A1, LEN(A1) - FIND(" ", A1))

# Extract the last name from full name
df['Extracted'] = df['Text'].str.split(' ').str[-1]

When trying to replicate the RIGHT function's behavior in pandas, there are a few pitfalls to be aware of. Below are some of the common mistakes:

A common mistake is to attempt to use string functions on non-string columns without first converting them.

In Excel, the RIGHT function works on cells containing text and numbers. In pandas, if a column is not of string type, you'll encounter an error. It's important to ensure that the column you're working with is a string type before applying string operations.

You can convert a column to string using `astype(str)`.

# Convert column to string
df['Text'] = df['Text'].astype(str)

# Extract the last 3 chracters
df['Extracted'] = df['Text'].str[-3:]

String slicing in pandas uses ths syntax `str[start:end]` where `start` and `end` are the positions of the characters to extract. By using a starting position like 3 and omitting the end condition, pandas returns all characters starting from position 3 to the end of the string.

However, if you want to extract characters from the right end of the string, you need to use a negative value for the starting position. This indicates that the characters should be extracted from the right end of the string instead of the front.

# Extract 3 characters from the front of the string
df['Extracted'] = df['Text'].str[3:]

# Extract 3 charactes from the end of the string
df['Extracted'] = df['Text'].str[-3:]

The RIGHT function in Excel takes two arguments: the text from which to extract characters, and the number of characters to extract.

=RIGHT(text, [num_chars])

RIGHT Excel Syntax

ParameterDescriptionData Type
textThe text string that contains the characters you want to extract.string
num_chars(Optional) The number of characters you want to extract. Defaults to 1.number


=RIGHT("Hello World", 5)Extracts the last 5 characters from the text.World
=RIGHT("Python")Extracts the last character from the text.n

Don't re-invent the wheel. Use Excel formulas in Python.

Install Mito

Don't want to re-implement Excel's functionality in Python?

Automate analysis with Mito