Breaking Down the Reverse String Search Syntax

If you want to understand how the reverse search formula works, or if you need to customize it to look for something other than spaces, look at each line in turn:

=IF(
	ISERROR(
		FIND(" ",A1)

The IF–ISERROR combination looks for an error in the FIND function. FIND is looking for a space character (” “) inside the string. If it can’t find one, the IF statement will be TRUE. These lines are to catch the case that the string doesn’t have the character you are looking for.

NOTE: If you are looking for something other than a space character, replace ” ” with the character you are looking for.

	),A1,RIGHT(A1,

Still inside the IF function, if there is an error caught by ISERROR, the entire formula just returns the original string. Otherwise, we start the reverse FIND operation with the RIGHT function.

		LEN(A1)-FIND("~",
			SUBSTITUTE(A1," ","~",
				LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
			)
		)

This section needs to be read from the inside out to be understood, so we’ll begin with the line with the largest indent.

LEN(A1)-LEN(SUBSTITUTE(A1," ",""))

NOTE: If you are looking for something other than a space character, replace ” ” with the character you are looking for.

This uses a neat trick to find out how many space characters there are in the string. It does this by using the SUBSTITUTE function to remove all the spaces and then compare the length of the string with and without spaces using the LEN function. The syntax for the LEN and SUBSTITUTE functions is as follows:

=LEN(text)

=SUBSTITUTE(text, old_text, new_text, [instance_num])

			SUBSTITUTE(A1," ","~",
				LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
			)

NOTE: If you are looking for something other than a space character, replace ” ” with the character you are looking for.

Since we are looking for the final space (we’re searching from right to left, remember), the SUBSTITUTE function replaces the last space (which we just found  using the LEN functions) with a wild character that doesn’t appear anywhere else in the string. In this case we are using a tilde (~), but you can change it to any character that won’t be duplicated in the string.

		LEN(A1)-FIND("~",
			SUBSTITUTE(A1," ","~",
				LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
			)
		)

Now that the internal functions have replaced the last space character with a tilde, we can find it using a traditional FIND function. This entire section is telling the RIGHT function how many characters to take from the end of the string, so we need to subtract the location of the last space from the total length of the string.

Test the reverse string formula against our sample string, and it returns “dog.” – the final word in the string!