Reversing "SEARCH" Formula in Google Sheet (Array Formula)

Question

SEARCH formula in google sheet will return a number of position of specific character in a cell.

But I wonder, can we do this similar thing but the other way around? SEARCH will count characters in a cell from left, but instead, is there any way to count the characters from right??

Here is the case sample:

https://docs.google.com/spreadsheets/d/1vMAVqOyVCxTiwpBFUpCQcpTVgslda8aFh7AHs_5hP18/edit#gid=0

In this sample, I tried to give the position of most right space (" ") manually, from the right. There are two possibility of the desired outcome. It can be either column B or column C.

Is there any way with array formula to be able to give such feats?

Answer 1

What I think is to find the amount of elements, by splitting by your delimiter. I'll use LET in order to name different parts of the process. If you're not familiar with it, think it as naming variables per step:

=LET(delim," ",
splitted,SPLIT(A2,delim),
splitted)

Then you find the last element, by counting the amount of "columns" determined by the prior splitting. With INDEX and that amount you'll be able to find the last substring:

=LET(delim," ",
splitted,SPLIT(A2,delim),
amount,COLUMNS(splitted),
INDEX(splitted,1,amount))

And, finally, all this is because the last "substring" and its length +1, you'll be able to get the position of the last "delimiter":

=LET(delim," ",
splitted,SPLIT(A2,delim),
amount,COLUMNS(splitted),
LEN(INDEX(splitted,1,amount))+1)

And as an arrayformula you can use BYROW or MAP:

=BYROW(A2:A,LAMBDA(string,
IF(string="","",
LET(delim," ",
splitted,SPLIT(string,delim),
amount,COLUMNS(splitted),
LEN(INDEX(splitted,1,amount))+1))))