Excel doesn't offer any function for reverse FIND / SEARCH & MID function. VBA does offer a function INSTRREV but this article is for non-VBA folks or folks who do not want to use VBA in their spreadsheet.
Hence, the only option before us is to build them through formulas. Let's build a reverse FIND / SEARCH function.
Note – There are two differences between FIND and SEARCH which you must make a note of.
Difference 1 – FIND is Case Sensitive whereas SEARCH is not.
Difference 2 – FIND doesn't support wildcard whereas SEARCH supports.
The scope of this article is not to cover wildcard situations but it would cover case-sensitivity.
Test Case 1 – Resultant position of the search string
Test Case 2– If the search string is not found, #VALUE error is returned.
Test Case 3 – If a blank value is looked for, 1 is returned as the answer.
Let's have a quick look on FIND.
A1 = "I have ametrax constitution of America"
If I run =FIND("ame",A1), I get the answer of 8 as "ame" is found at 8th position starting from 1st position of the string.
If I run = FIND("Ame",A1), I get the answer of 32 as FIND is case sensitive and "Ame" is found at 32nd position starting from 1st position of the string.
If I run = SEARCH("Ame",A1), I get the answer of 8 as SEARCH is case in-sensitive and "ame" is found at 8th position starting from 1st position of the string.
Now, I want to start from last position of the string and find the search string. Hence, if I start from last position of the string for search string ="ame", it should find the same in America not in ametrax.
BUILDING A REVERSE FIND / SEARCH FUNCTION
Let's assume that search string is B1.
There will be 4 Scenarios here –
Case Insensitive – Scenario 1 – The string position is calculated from first letter of the string. Hence, in case of search string "ame", the answer would be 32.
The formula in this case would be –
For Excel 2010 and above
If only Test Case 1 is needed then the below formula can be used –
=AGGREGATE(14,6,ROW(A1:INDEX(A:A,LEN(A1)))/
(MID(A1,ROW(A1:INDEX(A:A,LEN(A1))),LEN(B1))=B1),1)
If all 3 Test Cases are needed then the below formula can be used –
=IFERROR(IF(LEN(B1)=0,1,AGGREGATE(14,6,ROW(A1:INDEX(A:A,LEN(A1)))/
(MID(A1,ROW(A1:INDEX(A:A,LEN(A1))),LEN(B1))=B1),1)),"a"/1)
"a"/1 has been introduced to generate #VALUE error.
Above formula passes all 3 Test Cases.
For all versions of Excel
If only Test Case 1 is needed then the below formula can be used –
=MAX(INDEX((MID(A1,ROW(A1
:INDEX(A:A,LEN(A1))),LEN(B1))=B1)*ROW(A1:INDEX(A:A,LEN(A1))),,))
If all 3 Test Cases are needed then the below formula can be used –
=IF(LEN(B1)=0,1,IF(ISNUMBER(SEARCH(B1,A1)),MAX(INDEX((MID(A1,ROW(A1
:INDEX(A:A,LEN(A1))),LEN(B1))=B1)*ROW(A1:INDEX(A:A,LEN(A1))),,)),"a"/1))
Above formula passes all 3 Test Cases.
Case Insensitive – Scenario 2 – The string position is calculated from last letter of the string. Hence, in case of search string "ame", the answer would be 7.
For Excel 2010 and above
If only Test Case 1 is needed then the below formula can be used –
=LEN(A1)+1-AGGREGATE(14,6,ROW(A1:INDEX(A:A,LEN(A1)))/
(MID(A1,ROW(A1:INDEX(A:A,LEN(A1))),LEN(B1))=B1),1)
If all 3 Test Cases are needed then the below formula can be used –
=IFERROR(LEN(A1)+1-AGGREGATE(14,6,ROW(A1:INDEX(A:A,LEN(A1)))/
(MID(A1,ROW(A1:INDEX(A:A,LEN(A1))),LEN(B1))=B1),1),"a"/1)
Above formula passes all 3 Test Cases.
For all versions of Excel
If only Test Case 1 is needed then the below formula can be used –
=LEN(A1)+1-MAX(INDEX((MID(A1,ROW(A1
:INDEX(A:A,LEN(A1))),LEN(B1))=B1)*ROW(A1:INDEX(A:A,LEN(A1))),,))
If all 3 Test Cases are needed then the below formula can be used –
=LEN(A1)+1-IF(ISNUMBER(SEARCH(B1,A1)),MAX(INDEX((MID(A1,ROW(A1
:INDEX(A:A,LEN(A1))),LEN(B1))=B1)*ROW(A1:INDEX(A:A,LEN(A1))),,)),"a"/1)
Above formula passes all 3 Test Cases.
Case Sensitive – Scenario 1 – The string position is calculated from first letter of the string. Hence, in case of search string "ame", the answer would be 8 as this is case sensitive, hence "ame" would not be found in America but in amtrax.
For Excel 2010 and above
If only Test Case 1 is needed then the below formula can be used –
=AGGREGATE(14,6,ROW(A1:INDEX(A:A,LEN(A1)))
/EXACT(MID(A1,ROW(A1:INDEX(A:A,LEN(A1))),LEN(B1)),B1),1)
If all 3 Test Cases are needed then the below formula can be used –
=IFERROR(IF(LEN(B1)=0,1,AGGREGATE(14,6,ROW(A1:INDEX(A:A,LEN(A1)))
/EXACT(MID(A1,ROW(A1:INDEX(A:A,LEN(A1))),LEN(B1)),B1),1)),"a"/1)
For all versions of Excel
If only Test Case 1 is needed then the below formula can be used –
=MAX(INDEX(EXACT(MID(A1,ROW(
A1:INDEX(A:A,LEN(A1))),LEN(B1)),B1)*ROW(A1:INDEX(A:A,LEN(A1))),,))
If all 3 Test Cases are needed then the below formula can be used –
=IF(LEN(B1)=0,1,IF(ISNUMBER(FIND(B1,A1)),MAX(INDEX(EXACT(MID(A1,ROW(
A1:INDEX(A:A,LEN(A1))),LEN(B1)),B1)*ROW(A1:INDEX(A:A,LEN(A1))),,)),"a"/1))
Case Sensitive – Scenario 2 – The string position is calculated from last letter of the string. Hence, in case of search string "ame", the answer would be 31.
For Excel 2010 and above
If only Test Case 1 is needed then the below formula can be used –
=LEN(A1)+1-AGGREGATE(14,6,ROW(A1:INDEX(A:A,LEN(A1)))
/EXACT(MID(A1,ROW(A1:INDEX(A:A,LEN(A1))),LEN(B1)),B1),1)
If all 3 Test Cases are needed then the below formula can be used –
=IFERROR(LEN(A1)+1-IF(LEN(B1)=0,1,AGGREGATE(14,6,ROW(A1:INDEX(A:A,LEN(A1)))
/EXACT(MID(A1,ROW(A1:INDEX(A:A,LEN(A1))),LEN(B1)),B1),1)),"a"/1)
For all versions of Excel
If only Test Case 1 is needed then the below formula can be used –
=LEN(A1)+1-MAX(INDEX(EXACT(MID(A1,ROW(
A1:INDEX(A:A,LEN(A1))),LEN(B1)),B1)*ROW(A1:INDEX(A:A,LEN(A1))),,))
If all 3 Test Cases are needed then the below formula can be used –
=LEN(A1)+1-IF(LEN(B1)=0,1,IF(ISNUMBER(FIND(B1,A1)),MAX(INDEX(EXACT(MID(A1,ROW(
A1:INDEX(A:A,LEN(A1))),LEN(B1)),B1)*ROW(A1:INDEX(A:A,LEN(A1))),,)),"a"/1))
BUILDING A REVERSE MID FUNCTION
Let's have a quick look at MID function.
If I give =MID(A1,7,4), then I would be getting the result of "amet". There are 5 test cases here
Test Case 1 – Result
Test Case 2– If number of characters to extract is 0 or blanks, it should give blank result. If I give =MID(A1,7,0) OR =MID(A1,7,), in both cases, the result should be blanks.
Test Case 3 – If starting position is 0 or blanks, it should give #VALUE error. Hence =MID(A1,0,4) OR =MID(A1,,4) should give #VALUE error.
Let's assume B1 contains 7 (starting position) and C1 contains 4 (number of characters to be extracted)
Now, for Reverse Mid function, there are two scenarios –
Scenario 1 – Start looking from last of the string and whichever is 7th character, extract 4 position forward. Hence, result in this case would be "Amer".
If only Test Case 1 is needed then the below formula can be used –
=MID(A1,MAX(LEN(A1)+1-B1,1),MIN(C1,LEN(A1)+C1-B1))
If all 3 Test Cases are needed then the below formula can be used –
=IF(OR(B1=0,LEN(B1)=0),"a"/1,IF(LEN(C1)=0,"",IF(LEN(A1)>=B1-C1+1,
MID(A1,MAX(LEN(A1)+1-B1,1),MIN(C1,LEN(A1)+C1-B1)),"")))
Scenario 2 – Start looking from last of the string and whichever is 7th character, extract 4 position backward. Hence, result in this case would be "of A".
If only Test Case 1 is needed then the below formula can be used –
=MID(A1,MAX(LEN(A1)+1-B1-C1+1,1),IF(LEN(A1)-B1<C1,MAX(0,LEN(A1)-B1+1),MIN(C1,LEN(A1)+C1-B1)))
If all 3 Test Cases are needed then the below formula can be used –
=IF(OR(B1=0,LEN(B1)=0),"a"/1,IF(LEN(C1)=0,"",MID(A1,MAX(LEN(A1)+1-B1-C1+1,1),IF(LEN(A1)-B1<C1,MAX(0,LEN(A1)-B1+1),MIN(C1,LEN(A1)+C1-B1)))))