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

Normally, the XLOOKUP function is configured to look for a value in a lookup array that exists on the worksheet. However, when the criteria used to match a value becomes more complex, you can use boolean logic to...

To get the date, we extract the first 10 characters of the value with LEFT:
LEFT(B5,10) // returns "2015-03-01"
The result is text, so to get Excel to interpret as a date, we wrap LEFT in DATEVALUE, which...

To split dimensions that like 100x50x25 into three separate parts, you can use some rather complicated formulas that use LEFT, MID, RIGHT, FIND, LEN, and SUBSTITUTE.
Note: you can also use Flash Fill in Excel 2013 and...

The LEFT function is perfect for extracting characters starting from the left side of a text string. We use LEFT in this formula to extract all characters up to the number of characters we want to trim.
The challenge...

Note: these is an experimental formula that uses a hard coded array constant, set down here for reference and comment. Casually tested only, so take care if you use or adapt.
Sometimes you encounter data that mixes...

At the core, this formula relies on the SUMPRODUCT function to sum values in matching columns in the named range "data" C5:G14. If all data were provided to SUMPRODUCT in a single range, the result would be the sum of...

In this example, we need to construct logic that filters data to include:
account begins with "x" AND region is "east", and month is NOT April.
The filtering logic of this formula (the include argument) is created by...

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

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

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

The DATE function build dates from separate year, month, and day values. One of it's tricks is the ability to roll forward to correct dates when given days and months that are "out of range".
For example, DATE returns...

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

The SUBSTITUTE function can find and replace text in a cell, wherever it occurs.
In this case, we are using SUBSTITUTE to find a character with code number 202, and replace it with an empty string (""), which...