When you sum cells with "OR" criteria, you need to be careful not to double count when there is a possibility that both criteria will return true. In the example shown, we want to sum values in Column C when cells in...

The FIND function finds the first space character (" ") in the name and returns the position of that space in the full name. The number 1 is subtracted from this number to account for the space itself. This number is...

At the core, this formula builds a level 1 and level 2 number and concatenates the two numbers together with a period (".") as a separator. The result is a value like "1.1". The "level 1" number is generated with...

The core of this formula is the LEFT function which simply extracts text from the file name, starting at the left, and ending at the character before the first period (".").
=LEFT(filename,characters)
The FIND...

The COUNTIF function counts cells in a range that meet criteria. For example, to count the number of cells in a range that contain "a" you can use:
=COUNTIF(range,"a") // exact match
However, note this is...

The FIND function returns the position of the text when it's found in the cell, and the #VALUE! error if not. In the example, the cell B4 contains the number "2" in the 29th position. So, if we just used this formula...

The cell function is used to get the full file name and path:
CELL("filename",A1)
The result looks like this:
path[workbook.xlsm]sheetname
At the highest level, the MID function simply extracts the file...

At the core, this formula uses the fact that the SUBSTITUTE function understands "instance", supplied as an optional forth argument called "instance_num". This means you can use the SUBSTITUTE function to replace a...

In the first formula above, we are using the FIND function to locate the starting position of the number. For the find_text, we are using the array constant {0,1,2,3,4,5,6,7,8,9}, this causes the FIND function to...

The core of this formula is the MID function, which extracts the text in a URL starting with the character after "//", and ending with the character before the trailing slash ("/"):
=MID(url,start,chars)
The url...

When you use a formula to apply conditional formatting, the formula is evaluated relative to the active cell in the selection at the time the rule is created. In this case, the rule is evaluated for each of the 10...

When you count cells with "OR logic", you need to be careful not to double count. For example, if you are counting cells that contain "abc" or "def", you can't just add together two COUNTIF functions, because you may...

At the core, this formula uses the MID function to extract characters starting at the second to last space. The MID function takes 3 arguments: the text to work with, the starting position, and the number of...

Working from the inside out, this part of the formula searches each cell in B4:B11 for all values in the named range "things":
--ISNUMBER(SEARCH(things,B4)
The SEARCH function returns the position of the value if...

The first formula uses the FIND function to locate the underscore(_) in the text, then we subtract 1 to move back to the "character before the special character".
FIND("_",B5)-1
In this example , FIND...