Formula Forensic No. 021 – Find the 4th Slash !

Formula Forensic No. 021 – Find the 4th Slash !:
Last week at the Chandoo.org Forums, Senthilkumar_rm posed the question:
“I Have file name as
D:\Data\Personal\sramasam\desktop\Exceldata.xls
I want to find the position of the 4^th slash “\”
What is a simple way ? ”

I proposed 3 answers being:

=FIND("\",A1,FIND("\",A1,FIND("\",A1,FIND("\",A1)+1)+1)+1)

=SEARCH("\",A1,FIND("\",A1,FIND("\",A1,FIND("\",A1)+1)+1)+1)

=FIND(CHAR(135),SUBSTITUTE(G5,"\",CHAR(135),4))

and Faseeh proposed a 4th answer:

=(LARGE(((MID($A1,ROW(INDIRECT("1:"&1024)),1))="\")*ROW(INDIRECT("1:"&1024)),2))

So today at Formula Forensics we will examine all these and see what and why they all work.

As usual at Formula Forensics you can follow along by downloading a Sample File.

Using the Find() function

The Initial formula I proposed was:

=FIND("\",A1,FIND("\",A1,FIND("\",A1,FIND("\",A1)+1)+1)+1)

This uses the Excel Find() function repeatedly inside itself
The Excel Find() function uses the following syntax

The second solution I proposed was the same as the Find solution except that it used the Excel Search( ) function.

You can see above that Find and Search have exactly the same syntax.
Why have 2 functions that are effectively similar ?
Search is Case Insensitive.
eg: Chandoo = cHanDoo
Find is Case Sensitive.
eg: Chandoo <> cHanDoo
We will discuss the solution using:

=FIND("\",A1,FIND("\",A1,FIND("\",A1,FIND("\",A1)+1)+1)+1)

But the solution and description are equally applicable to the search based solution:

=SEARCH("\",A1,FIND("\",A1,FIND("\",A1,FIND("\",A1)+1)+1)+1)

So Find has the syntax: =Find( Text, Within Text, [Start No.])
But our formula has 4 Find() functions, where do we start?

Start in the Middle.

In our example: =FIND(“\”,A1,FIND(“\”,A1,FIND(“\”,A1,FIND(“\”,A1)+1)+1)+1)

FIND("\",A1)

Text:                      ”\”
Within Text:      A1
Start No:              Optional = 0
This will find the first \ in out text string and return the value 3.
Checkout Cell D13.

Stepping out one find =FIND(“\”,A1,FIND(“\”,A1,FIND(“\”,A1,3+1)+1)+1)

FIND("\",A1,3+1)

Text:                      “\”
Within Text:      A1
Start No:              3 + 1
This will find the Second \ in out text string by starting at position 3 + 1 and hence return the value 8.
Checkout Cell D15.

Stepping out one more find =FIND(“\”,A1,FIND(“\”,A1,8+1)+1)

FIND("\",A1,8+1)

Text:                      “\”
Within Text:      A1
Start No:              8 + 1
This will find the Third \ in out text string by starting at position 8 + 1 and hence return the value 17.
Checkout Cell D17.

Finally we arrive at the outer Find, =FIND(“\”,A1,17+1)

=FIND("\",A1,17+1)

Text:                      “\”
Within Text:      A1
Start No:              17 + 1
This will find the fourth \ in out text string by starting at position 17 + 1 and hence return the answer of 26.
Checkout Cell D19.

Advantages:

Relatively simple formula

Disadvantage:

This formula must be manually re-made if you want to find either the 3^rd, 5^th or another occurrences.

Using the Find() & Substitute() functions

=FIND(CHAR(135),SUBSTITUTE(A1,"\",CHAR(135),4))

Using the Find() & Substitute() formula shown above take a different approach to solving the problem to the pure Find() based solution.
This solution works by using a feature of the substitute function that allows for the substitution of the Nth chosen character with another character.
The Excel Substitute() function has the following syntax :

In our example:

=FIND(CHAR(135),SUBSTITUTE(A1,"\",CHAR(135),4))

Text:             A1
Old_Text:    "\"
New_Text:   Char(135)
Instance_Num:     
So substitute will replace the 4^th Slash with the character Ascii code 135. Char 135 or a ‡ character was chosen as it is unlikely to be used in normal text. If it is used another character code should be chosen.
The Find Function will then look for Char(135) in the Substituted text and return the position number of it.
Replacing the Char(135) with a Char(5) characters reduces this formula to 44 characters!
Advantages:
This is a very clear formula to understand
You can easily look for the 3^rd or 5^th character without changing the formula
Disadvantage:
If the Text String already contains the Char(135) character then another must be chosen or the formula will be wrong.

Using Faseeh’s Array Formula.

={(LARGE(((MID($A1,ROW(INDIRECT("1:"&1024)),1))="\")*ROW(INDIRECT("1:"&1024)),2))}
Faseeh’s Formula is based around the Large() function
=(LARGE(((MID($A1,ROW(INDIRECT("1:"&1024)),1))="\")*ROW(INDIRECT("1:"&1024)),2)) Ctrl Shift Enter
The Excel Large() function has the following syntax:

In Faseeh’s Formula we see
=LARGE(((MID($A1,ROW(INDIRECT("1:"&1024)),1))="\")*ROW(INDIRECT("1:"&1024)),2)
Array: ((MID($A1,ROW(INDIRECT("1:"&1024)),1))="\")*ROW(INDIRECT("1:"&1024))
K:     2
So we can see that the Large() function is looking for the Second Largest value (K=2) in the array.
Lets pull the Array apart
The Array consists of two parts separated by a Multiplication sign.
((MID($A1,ROW(INDIRECT("1:"&1024)),1))="\")*ROW(INDIRECT("1:"&1024))
We can look at each part separately and then combine them at the end.
The first part MID($A1,ROW(INDIRECT("1:"&1024)),1)="\"
Consists of MID($A1,ROW(INDIRECT("1:"&1024)),1) and an = "\"
In a Blank cell Say E25 enter =MID($A1,ROW(INDIRECT("1:"&1024)),1) and press F9 Not Enter
Excel responds with an Array: = {"D";":";"\";"D";"a";"t";"a";"\";"P";"e";"r";"s";"o";"n";"a";"l";"\";"s";"r";"a";"m";"a";"s";"a";"m";"\";"d";"e";"s";"k";"t";"o";"p";"\";"E";"x";"c";"e";"l";"d";"a";"t";"a";".";"x";"l";"s";""; … ;"" ;""}
We can see here that Excel has taken the value of cell A1 and broken it up as text and put each character into an element in the array.
So the whole line: MID($A1,ROW(INDIRECT("1:"&1024)),1)="\"
Is evaluating this array against a "\" and should return an array of True/False values
In a Blank cell E27 put the following: =MID($A1,ROW(INDIRECT(“1:”&1024)),1)=”\” and press F9 Not Enter
Excel responds with a an array of 1024 Falses
={FALSE;FALSE;FALSE; …. ;FALSE;FALSE}
That’s not quite what we expected ?
The Function is =MID($A1,ROW(INDIRECT(“1:”&1024)),1)=”\”
Which is a Boolean way of saying if the Middle 1 Character of Cell A1 from position 1 to 1024  is = “\” return an Array of the values.
What is going on here?
If we modify the formula slightly to =(MID($A1,ROW(INDIRECT(“1:”&1024)),1)=”\”)*1
And evaluate (F9) that in cell E28
Excel returns an Array ={0;0;1;0;0;0;0;1;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;1;0; … ;0;0}
The array consists of 1024 0’s except for the positions where the Array = ”\” where it has 1’s.

Lets go inside this part of the formula and see what is happening.
=(MID($A1,ROW(INDIRECT(“1:”&1024)),1)
INDIRECT(“1:”&1024) returns a Reference of 1:1024 as Text which Indirect converts to a Range from row 1 to row 1024
=(MID($A1,ROW(INDIRECT(“1:”&1024)),1)
Row() returns the row number of the array
=(MID($A1,ROW(INDIRECT(“1:”&1024)),1)
Mid takes the Character from A1 starting at Position Row(INDIRECT(“1:”&1024)) and returns 1 character.
This effectively allows the array to retrieve each character from the Text of a1.

The Second Part

((MID($A1,ROW(INDIRECT("1:"&1024)),1))="\")*ROW(INDIRECT("1:"&1024))

We have seen that the first part of the equation
((MID($A1,ROW(INDIRECT("1:"&1024)),1))="\")
Returns an Array of 1’s and 0’s where the formula matched the "\" character.
The second part of the equation: ROW(INDIRECT("1:"&1024)) is used to return the position of the characters.
In a blank cell E30 type
=ROW(INDIRECT("1:"&1024)) and press F9 Not Enter
Excel will respond with ={1;2;3;4;5;6;7;8; … ;1023;1024}
This is a list of the Rows from the Range 1:1024

We can now return to the original function
((MID($A1,ROW(INDIRECT(“1:”&1024)),1))=”\”)*ROW(INDIRECT(“1:”&1024))
In a blank cell say E32 type: =((MID($A1,ROW(INDIRECT(“1:”&1024)),1))=”\”)*ROW(INDIRECT(“1:”&1024)) and press F9 Not Enter
Excel will respond with:
={0;0;3;0;0;0;0;8;0;0;0;0;0;0;0;0;17;0;0;0;0;0;0;0;0;26;0;0;0;0;0;0;0;34;0; … ;0;0;0;0}
This array is the combination of the two arrays discussed above.
That is it is the 1’s and 0’s where the \’s are multiplied by the Row Numbers.
We can see that the positions where the \’s are have the position numbers listed
={0;0;3;0;0;0;0;8;0;0;0;0;0;0;0;0;17;0;0;0;0;0;0;0;0;26;0;0;0;0;0;0;0;34;0; … ;0;0;0;0}
The Large Function in the original Formula:

=LARGE(((MID($A1,ROW(INDIRECT("1:"&1024)),1))="\")*ROW(INDIRECT("1:"&1024)),2)

Then extracts the second largest number, which in this case is the value 26

Advantages:

To assign the position number you need to know how many \’s are in the formula to start with

Disadvantages:

This is an Array Formula which some people struggle with
How the formula works is difficult to workout or explain.

Modified Formula

With a small modification you can introduce a constant to allow a variable n’th character to be retrieved without knowing how many \’s there were originally.
=LARGE(((MID($A1,ROW(INDIRECT("1:"&LEN(A1))),1))="\")*ROW(INDIRECT("1:"&LEN(A1))),LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))-4+1) Ctrl Shift Enter
Or
=LARGE(((MID($A1,ROW(INDIRECT("1:"&LEN(A1))),1))="\")*ROW(INDIRECT("1:"&LEN(A1))),LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))-B1+1) Ctrl Shift Enter
Where cell B1 contains 4
Where 4 is the 4th character you want to find the location for.
You can examine how the replacement of 2 with LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))-4+1 works

How Else Can You Solve Senthilkumar_rm’s Problem?

Your task is to find the location of the 4^th Slash "\" in the text string: D:\Data\Personal\sramasam\desktop\Exceldata.xls
Can you solve Senthilkumar_rm’s problem another way?
Can you beat my 44 characters ?
Let us know in the comments below:

Download

You can download a copy of the above file and follow along, Download Here.

Formula Forensics “The Series”

This is the 21^st post in the Formula Forensics series.
You can learn more about how to pull Excel Formulas apart in the following posts
Formula Forensic Series

Formula Forensics Needs Your Help

I need more ideas for future Formula Forensics posts and so I need your help.
If you have a neat formula that you would like to share and explain, try putting pen to paper and draft up a Post like above or;
If you have a formula that you would like explained, but don’t want to write a post, send it to Hui or Chandoo.