Finding the position of the last occurrence of a value inside an Excel cell is a surprisingly hard task. Yet sometimes you need it, for example to remove or replace this occurrence, or to take the text after the last space, slash or comma.
So what’s the problem? The position functions SEARCH and FIND search only from the beginning of the cell and have no option to search from the end. Find and Replace doesn’t help either: it doesn’t search from the end of a string, it simply finds (and replaces) all occurrences.
Short answer for those in a hurry: in Excel 365, the text after the last space is =TEXTAFTER(A1,” “,-1), and its position is =LEN(A1)-LEN(TEXTAFTER(A1,” “,-1)). For older versions and for the position of any character, read on.
Classic formula with SUBSTITUTE
A formula like the one below is covered in detail in the article on how to remove the last word in an Excel cell: finding the last space is exactly what’s needed there. This formula finds the position of the last space in A1:
=SEARCH(UNICHAR(23456),SUBSTITUTE(A1," ",UNICHAR(23456),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))
How it works:
- LEN(A1)-LEN(SUBSTITUTE(A1,” “,””)) counts the spaces in the cell. More about this trick in the description of the SUBSTITUTE function;
- this count becomes the fourth argument of another SUBSTITUTE, which replaces only the last space;
- instead of the space, a rare character is inserted at its position: a Chinese character created by the UNICHAR function. You can also type a character manually, for example a backslash: =SEARCH(“\”,SUBSTITUTE(A1,” “,”\”,LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))));
- SEARCH finds the position of this character. Since it occurs only once and stands where the last space was, we have effectively searched from right to left.
To find another character or text fragment from the end, replace the spaces in the formula with it. If the cell has no spaces at all, the formula returns #VALUE!, so wrap it in IFERROR if needed.
Array formulas to find a character from the end
The SUBSTITUTE approach has a couple of drawbacks. First, SUBSTITUTE is case-sensitive, which is easy to handle by wrapping parts of the formula in LOWER. The second is more serious: the formula isn’t universal, because it relies on a replacement character that might already be in the string, and then it returns a wrong value.
Array formulas solve both problems. They create an array of values and pick the last one.
With MAX
The formula below finds the position of the last letter “a” in any case:
{=MAX((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="a")*ROW(INDIRECT("1:"&LEN(A1))))}
Important: it’s an array formula. In Excel 2019 and earlier, type it without the curly braces and press Ctrl+Shift+Enter, and the braces will appear by themselves. In Excel 365, just press Enter.
Step by step:
- LEN measures the length of the cell;
- INDIRECT turns it into a reference to rows 1 to that length, and ROW returns the array {1;2;3;…;length};
- MID returns the character at each of these positions;
- comparing with “a” returns an array of TRUE and FALSE;
- this array is multiplied by the array of positions: FALSE is 0 and TRUE is 1, so the result has zeros for all other characters and positions for the “a” characters;
- MAX returns the largest number, the position of the last “a”.
With LOOKUP
A slightly trickier version is faster and doesn’t even need Ctrl+Shift+Enter, because LOOKUP handles arrays by itself:
=LOOKUP(2,1/(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="a"),ROW(INDIRECT("1:"&LEN(A1))))
- as before, LEN, ROW, INDIRECT, MID and the comparison create a Boolean array;
- 1 is divided by it: FALSE (zero) gives a #DIV/0! error, TRUE gives 1;
- LOOKUP looks for 2 (any number greater than 1 works) in this array. It can’t find it, ignores the errors and stops at the last 1, returning the matching position from the last argument, the array of positions. That’s the last occurrence of the character.
With XMATCH in Excel 365
Excel 365 has a search mode built for this. XMATCH with −1 as the last argument searches from the last element to the first:
=XMATCH("a",MID(A1,SEQUENCE(LEN(A1)),1),0,-1)
And to find the last occurrence of a whole fragment, not a single character, TEXTBEFORE with −1 does the job: the position is the length of the text before the fragment plus 1.
=LEN(TEXTBEFORE(A1,"ab",-1))+1
Common mistakes
- The SUBSTITUTE or LOOKUP formula returns #VALUE! or #N/A. The character is not in the cell. Wrap the formula in IFERROR.
- The MAX formula returns 0. The same reason: no matches. Unlike the others, it doesn’t return an error, so check for 0.
- The MAX formula returns a wrong number in old Excel. It was entered with Enter instead of Ctrl+Shift+Enter.
See also:
Remove or extract the text after the last space, comma or any other character right in place, without long formulas.
!SEMTools solves this and hundreds of other text tasks in a couple of clicks!
Summary
I hope you found what you were looking for on this page and were able to find the last occurrence of a character in your cells. In Excel 365, TEXTAFTER, TEXTBEFORE and XMATCH with −1 make it easy. In older versions, use the SUBSTITUTE trick or the LOOKUP formula. And for typical tasks like removing the last word, a ready-made procedure is faster than any formula.
You may also be interested in how to find the first letter and make it uppercase and how to remove text up to a certain character.