In answer to the question....
The formula substitutes the nth instance of "/" with ^^ and then uses ^^ to determine starting point of string to return.
n is determined by LEN of string less LEN of string having had all instances of "/" removed
Consider:
A1 = "apples/pears/oranges"
Len of A1 is 20
Determine n
n = 20-18
18 being the LEN of A1 if you take out the "/"
We use n [2] as final argument within SUBSTITUTE call to determine which instance of "/" we wish to replace with "^^"
(when this argument is excluded [optional] all instances are replaced)
SUBSTITUTE(A1,"/","^^",2)
At which point A1 equates to: "apples/pears^^oranges"
To return "oranges" we return right n characters where n is determined by Len of string [20] less position of ^^ within revised string.
There are alternatives to the above but it's a fairly common approach.
Bookmarks