Purpose

Return value

Syntax

=RIGHT(text,[num_chars])
  • text - The text from which to extract characters on the right.
  • num_chars - [optional] The number of characters to extract, starting on the right. Default = 1.

Using the RIGHT function

The RIGHT function extracts a given number of characters from the right side of a supplied text string. The first argument, text , is the text string to extract from. This is typically a reference to a cell that contains text. The second argument, called num_chars , specifies the number of characters to extract. If num_chars is not provided, it defaults to 1. If num_chars is greater than the number of characters available, RIGHT returns the entire text string. Although RIGHT is a simple function, it shows up in many more advanced formulas that test or manipulate text in a specific way.

RIGHT function basics

To extract text with RIGHT, just provide the text and the number of characters to extract. The formulas below show how to extract one, two, and three characters with RIGHT:

=RIGHT("apple",1) // returns "e"
=RIGHT("apple",2) // returns "le"
=RIGHT("apple",3) // returns "ple"

If the optional argument num_chars is not provided, it defaults to 1:

=RIGHT("ABC") // returns "C"

If num_chars exceeds the length of the text string, RIGHT returns the entire string:

=RIGHT("apple",100) // returns "apple"

When RIGHT is used on a numeric value, the result is text:

=RIGHT(1500,3) // returns "500" as text

Example - extract state abbreviation

The RIGHT function can be used to extract a specific number of characters from the end of a text string. For example, to extract the last two characters from “Portland, OR” you can use RIGHT like this:

=RIGHT("Los Angeles, CA",2) // returns "CA"

Of course, it doesn’t make sense to extract text from a text string that you have to type into a formula. A more typical example is to work with values that already exist in cells , as seen in the worksheet below. The formula in cell D5, copied down, is:

=RIGHT(B5,2)
RIGHT function example - extract state abbreviation - 1

Notice num_chars is provided as 2 to extract the last two letters from each city and state. The result is the two-letter abbreviation for the state.

Example - extract the last character

An interesting quirk of the RIGHT function is that the number of characters to extract is not required and defaults to 1. This can be useful in cases where you only want to extract the last character of a text string, as seen below. Here, the formula in cell D5 looks like this:

=RIGHT(B5)
RIGHT function example - extract last character - 2

As you can see, without a value for num_chars , RIGHT extracts the last character from each product, which corresponds to the size.

Example - RIGHT with UPPER

You can easily combine RIGHT with other functions in Excel to get a more specific result. For example, you could nest the RIGHT function inside the UPPER function to convert the result from RIGHT to uppercase. You can see this approach in the worksheet below, where the formula in cell D5 looks like this:

=UPPER(RIGHT(B5,3))
RIGHT function example - RIGHT with UPPER - 3

All text in column B is lowercase. The RIGHT function extracts the state code as before and returns the result to the UPPER function, which converts the code to uppercase.

Example - RIGHT with IF

You can easily combine the RIGHT function with the IF function to create “if cell ends with” logic. In the example below, a formula is used to flag codes that end with “HNN” with an “x”. The formula in cell D5 is:

=IF(RIGHT(B5,3)="hnn","x","")
RIGHT function example - IF cell ends with - 4

As the formula is copied down, the RIGHT function returns the last 3 characters in each value, which are compared to “hnn” as a logical test. When the result is TRUE, IF returns “x”. When the result is FALSE, IF returns an empty string “”. The result is that the codes in column B that end with “abc” are clearly marked.

RIGHT is not case-sensitive as you can see in the formula above. To perform a case-sensitive test you can combine RIGHT with the EXACT function. Example here .

Example - RIGHT with FIND

A common challenge with the RIGHT function is extracting a variable number of characters, depending on the location of a specific character in the text string. To handle this situation you can use the RIGHT function together with the FIND function in a generic formula like this:

=RIGHT(text,LEN(text)-FIND(character,text)) // extract text after character

FIND returns the position of the character, and RIGHT returns all text to the right of that position. The screen below shows how this formula can be applied in a worksheet. The formula in cell D5 is:

=RIGHT(B5,LEN(B5)-FIND(" ",B5))
RIGHT function example - get last name from full name - 5

As the formula is copied down the LEN function returns the length of the text string in cell B5. Next, the FIND function returns the position of the space character " " as a number. The result from FIND is then subtracted from the result from LEN and returned to RIGHT as the num_chars argument. Finally, the RIGHT function returns all text after the space, which corresponds to the last name in this simplified example. You can read a more detailed explanation here .

The RIGHT function is used to extract text from the right side of a text string. Use the LEFT function to extract text starting from the left side of the text, and the MID function to extract from the middle of text. The LEN function returns the length of a text string as a count of characters and is often combined with LEFT, MID, and RIGHT.

In the latest version of Excel, newer functions like TEXTBEFORE , TEXTAFTER , and TEXTSPLIT greatly simplify certain text operations and make some traditional formulas that use the RIGHT function obsolete. Example here .

Notes

  • RIGHT is not case-sensitive.
  • RIGHT can extract numbers as well as text.
  • The output from RIGHT is always text.
  • RIGHT ignores number formatting when extracting characters.
  • Num_chars is optional and defaults to 1.

Purpose

Return value

Syntax

=SEARCH(find_text,within_text,[start_num])
  • find_text - The substring to find.
  • within_text - The text to search within.
  • start_num - [optional] Starting position. Optional, defaults to 1.

Using the SEARCH function

The SEARCH function returns the position (as a number) of one text string inside another. In the most basic case, you can use SEARCH to locate the position of a substring in a text string. You can also use SEARCH to check if a cell contains specific text. SEARCH is not case-sensitive , which means it does not distinguish between uppercase and lowercase letters. In addition, SEARCH supports the use of wildcards like *?~, allowing more flexible search patterns. Here are a few key points to remember about the SEARCH function:

  • SEARCH returns the position of one text string inside another as a number .
  • When SEARCH cannot locate the search string, it returns a #VALUE error.
  • If the search string appears more than once, SEARCH returns the first position .
  • SEARCH is not case-sensitive and will treat “Apple” and “apple” as the same text strings.
  • SEARCH does support wildcards like *?~ when searching for text.
  • SEARCH will return 1 if the search string ( find_text ) is empty, which can cause a false positive when find_text is an empty cell.

Note: The SEARCH function is similar to the FIND function. Both functions return the position of one text string inside another. However, unlike FIND, SEARCH is not case-sensitive and does support wildcards.

Basic syntax

The basic syntax of the SEARCH function looks like this

SEARCH(find_text,within_text,[start_num])
  • find_text : The text you want to find (the search string). This is a substring that Excel searches for within another text string. The text must be entered in double quotes if you are hardcoding the value into the formula. Otherwise, you can refer to a cell that contains the text.
  • within_text : The text string that contains the text you want to find. Often, this is a cell reference that contains the text, but you can also hardcode a text string in double quotes.
  • start_num (optional): The character at which to begin searching as a numeric position. The first character in within_text is considered position 1. If omitted, the search starts at the beginning of the within_text .

Basic example

The SEARCH function is designed to look inside a text string for a specific substring. When SEARCH locates the substring, it returns the position of the substring in the text as a number. If the substring is not found, SEARCH returns a #VALUE error. For example:

=SEARCH("p","apple") // returns 2
=SEARCH("z","apple") // returns #VALUE!
=SEARCH("apple","Pineapple") // returns 5

Note that text values entered directly into SEARCH must be enclosed in double quotes (""). Unlike the FIND function, the SEARCH function is not case-sensitive:

=SEARCH("a","Apple") // returns 1
=SEARCH("A","Apple") // returns 1
=SEARCH("Apple","Pineapple") // returns 5

The worksheet below shows the same examples translated into formulas based on cell references:

SEARCH function - basic example - 6

Again, notice that SEARCH is not case-sensitive, as seen in D8 and D10.

Forcing a TRUE or FALSE result

By default, the SEARCH function returns a number when a search string is found and a #VALUE! error when not. This is inconvenient in cases where you simply want to know if the search string has been found or not. To force a TRUE or FALSE result, you can nest the SEARCH function inside the ISNUMBER function . ISNUMBER returns TRUE for numeric values and FALSE for anything else. If SEARCH locates the substring, it returns the position as a number, and ISNUMBER returns TRUE:

=ISNUMBER(SEARCH("p","apple")) // returns TRUE
=ISNUMBER(SEARCH("z","apple")) // returns FALSE

If SEARCH doesn’t locate the substring, it returns an error, and ISNUMBER returns FALSE. This approach allows for support for wildcards in the search. For a more detailed explanation of this approach, with many more examples, see this example .

If cell contains

Once you have a TRUE or FALSE result, you can combine the SEARCH function with the IF function to create “if cell contains” logic. The generic pattern for this formula looks like this:

=IF(ISNUMBER(SEARCH(substring,A1)), "Yes", "No")

Instead of returning TRUE or FALSE, the formula above will return “Yes” if the substring is found and “No” if not. You can use the same idea to mark or “flag” items of interest. For example, in the worksheet below, we are using SEARCH with IF to flag email addresses that contain “abc” with an “x”.

SEARCH function - using SEARCH with the IF function - 7

The formula in C5, copied down, is:

=IF(ISNUMBER(SEARCH("abc",B5)),"x","")

Start number

The SEARCH function has an optional argument called start_num that controls where SEARCH should begin looking for a substring. To find the first match of “x” in upper or lowercase, you can omit start_num , which defaults to 1:

=SEARCH("x","20 x 30 x 50") // returns 4

SEARCH returns 4 since the first “x” appears at position 4. To find the second “x”, enter 5 for start_num :

=SEARCH("x","20 x 30 x 50",5) // returns 9

In this case, SEARCH returns 9 since it starts searching after the first “x”. You can effectively find the second “x” in cell A1 in a single formula by using the SEARCH function twice like this:

=SEARCH("x",A1,SEARCH("x",A1)+1)

The inner SEARCH returns the location of the first “x”. We then add 1, and the result is used as the start_num in the outer SEARCH. The result is the location of the second “x” in cell A1.

Wildcards

Although SEARCH is not case-sensitive, it does support wildcards (*?~), which makes it a versatile tool for finding substrings in text. This allows for more complex searches, including basic pattern matching. For example, the question mark (?) wildcard matches any one character. The formula below looks for a 3-character substring beginning with “x” and ending in “y”:

=ISNUMBER(SEARCH("x?z","xyz")) // TRUE
=ISNUMBER(SEARCH("x?z","xbz")) // TRUE
=ISNUMBER(SEARCH("x?z","xyy")) // FALSE

The asterisk (*) wildcard is not as useful in the SEARCH function because SEARCH already looks for a substring . For example, it might seem like the following formula will test for a value that ends with “z”:

=SEARCH("*z",text)

However, because SEARCH automatically looks for a substring , the following formulas all return 1 as a result, even though the text in the first formula is the only text that ends with “z”:

=SEARCH("*z","XYZ") // returns 1
=SEARCH("*z","XYZXY") // returns 1
=SEARCH("*z","XYZXY123") // returns 1
=SEARCH("x*z","XYZXY123") // returns 1

However, it is possible to use the asterisk (*) wildcard like this:

=SEARCH("x*2*b","AAAXYZ123ABCZZZ") // returns 4
=SEARCH("x*2*b","NXYZ12563JKLB") // returns 2

Here we are looking for “x”, “2”, and “b” in that order, with any number of characters in between. Finally, use the tilde (~) as an escape character to indicate that the next character is a literal like this:

=SEARCH("~*","apple*") // returns 6
=SEARCH("~?","apple?") // returns 6
=SEARCH("~~","apple~") // returns 6

The above formulas use SEARCH to find a literal asterisk (*), question mark (?), and tilde (~) in that order.

More advanced formulas

The SEARCH function shows up in many more advanced formulas that work with text. SEARCH is often used instead of FIND when wildcards are needed or when the goal is to perform a case-insensitive search. Here are a few examples:

  • Cell contains one of many things - tests a cell for more than one text string simultaneously, using wildcards for flexible matching.
  • Sum if cells contain either x or y - sums numbers when associated cells contain one value or another, utilizing wildcard searches.
  • Filter if text contains - extract values from a data set with “contains-type” logic, leveraging wildcards for comprehensive searching.
  • Categorize text with keywords - return an appropriate category based on if a cell contains one of several text values, using wildcards to match patterns.
  • Filter based on partial match - extract records from an Excel Table based on a partial match, using wildcards for pattern recognition.

Notes

  • The SEARCH function returns the location of the first find_text in within_text as a number.
  • SEARCH returns #VALUE if find_text is not found.
  • Start_num is optional and defaults to 1.
  • SEARCH returns 1 when find_text is empty. This can cause a false positive when find_text is an empty cell.
  • SEARCH is not case-sensitive but does support wildcards.
  • Use the FIND function for a case-sensitive search.
  • SEARCH allows the wildcard characters question mark (?) and asterisk (), in find_text . ? matches any single character * matches any sequence of characters. To find a literal ? or * or ~, use a tilde (~) before the character, i.e. ~, ~?,~~.