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)))))

## Leave A Comment