Google Sheets RIGHT, LEN, and Find Functions | How to Use RIGHT, LEN, and Find Together
Use Google Sheets RIGHT, LEN, and FIND functions together to perform tasks that are not possible using one or two of these functions. For example, say you want to extract the number(s) after the last dash (-) from the part number 783-53277-5.
The number(s) after the last dash is 5. It can be easily extracted using only the RIGHT function. However, if the part numbers have a different format, then the Google Sheets RIGHT function alone will not work.
Consider these part numbers for example: 56-338-23, 8435-15-2345, 3245-1-89000. In these numbers, the digits before and after the last dash are different. This means, we have to use Google Sheets RIGHT, LEN, and FIND functions to extract the numbers(s) after the last dash.
Here's a quick look at the three functions:
1. FIND
It returns the position of the specified character in the text.
Here is the format of the FIND function:
=FIND(search_for, text_to_search, [starting_at])
Example:
Let's say that we want to find the position of the second -(dash) in the number 783-53277-5.
The formula is:
=FIND("-", 783-53277-5, 5)
Because we want the FIND function to return the position of the second -(dash) and not of the first, we have specified 5 as the value of the starting_at attribute. That is, FIND will begin to search starting from the fifth digit which is also incidentally 5.
The FIND function will return 10.
2. LEN
It returns the length of text. That is, the number of characters in the text string.
The format of Google Sheets LEN function is:
=LEN(text)
Example:
Assume that you want to find the length of the text 783-53277-5.
The formula is:
=LEN("783-53277-5")
The LEN function will return 11.
3. RIGHT
It returns the specified number of characters from the right of the text string.
The format of Google Sheets RIGHT function is:
=RIGHT(string, [number_of_characters])
Example:
=RIGHT("783-53277-5", 1)
The RIGHT function will return 5, because it is the first, single-digit character from the right.
Now, the question is how do we combine the FIND, LEN, and RIGHT functions to extract the number(s) after the last dash. Note that the number of digits after the last dash could be one (like in the above example) or more.
Here is a solution:
Combine the three functions.
The formula with such a combination is:
=RIGHT(string, [number_of_characters])
The number of characters after the last dash is obtained by subtracting the position of the last dash in the string by the length of the text.
The position of the last dash is found by using the FIND function. So, let's modify the above formula to include the LEN and FIND functions:
=RIGHT(string, LEN(text)-FIND(search_for, text_to_search, starting_at))
Note the attribute of LEN, which is text, is same as the string, the first attribute of RIGHT.
For starting_at attribute, substitute the FIND function and add 1 to it to make FIND start the search from the first character after the first dash.
Do take a look at this video tutorial, which gives the steps to extract the data from part numbers in the format 783-53277-5, combining Google Sheets RIGHT, LEN, and FIND functions. Note that the number of characters before the first dash, between the two dashes, and after the last dash can vary.