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 // returns 6
In this example...

The first expression uses LEFT and UPPER to capitalize the first letter:
=UPPER(LEFT(B5))
No need to enter 1 for num_chars in LEFT, since it will default to 1. The second expression extracts the remaining characters...

At the core, this formula uses the LEFT function to return text starting from the left. To work out how many characters should be returned, the formula uses this expression:
LEN(B5)-(RIGHT(B5)="/")
Here,...

Data validation rules are triggered when a user adds or changes a cell value.
In this formula, the LEFT function is used to extract the first 3 characters of the input in C5.
Next, the EXACT function is used to...

The cell function is used to get the full file name and path:
CELL("filename",A1)
The result looks like this:
path[workbook.xlsm]sheet
The full file name and path are fed into the LEFT function, which...

For a name like "Tim Brown", this formula builds an email address like "tbrown@domain.com".
First, the LEFT function is used to get the first letter from the first name in column C. Usually, the LEFT function gets a "...

The core of this formula is the DATE function, which is used to assemble a proper Excel date value. The DATE function requires valid year, month, and day values, so these are parsed out of the original text string as...

The MATCH function has a limit of 255 characters for the lookup value. If you try to use longer text, MATCH will return a #VALUE error.
To workaround this limit you can use boolean logic and the LEFT, MID, and EXACT...

FIND returns the position (as a number) of the first occurrence of a space character in the text. This position, minus one, is fed into the LEFT function as num_chars.
The LEFT function then extracts characters...

In the first part of the formula, feet are extracted and converted LEFT and FIND like this:
=LEFT(B5,FIND("'",B5)-1)*12
Working from the inside out, FIND is used to locate the position of the single quote...

Overview
The formula looks complex, but the mechanics are in fact quite simple.
As with most formulas that split or extract text, the key is to locate the position of the thing you are looking for. Once you have the...

The key to this formula is the array or TRUE and FALSE values constructed with this expression:
LEFT(code,1)<>"N"
Here, each value in the named range "code" is evaluated with the logical test "first...

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

This formula uses the REPLACE function to replace the first character in a cell with an empty string (""). The arguments for REPLACE are configured as follows:
old_text is the original value from column B
start_num...

In this example, the values in column A are "stored as text". This means if you try to SUM column A, you'll get a result of zero.
The VALUE function will try to "coerce" a number stored as text to a true number. In...