The SHEET function returns the index number of a sheet in Excel. You can use the SHEET function to get a numeric index that represents the order of sheets in an Excel workbook, starting with 1 on the left and ending with N on the right, where N is the total number of sheets in the workbook. The...Read more
The gist of this formula is to replace a given delimiter with a large number of spaces using SUBSTITUTE and REPT, then use the MID function to extract text related to the "nth occurrence" and the TRIM function to get rid of the extra space.
In this snippet, the delimiter (delim...Read more
The SWITCH function compares one value against a list of values and returns a result that corresponds to the first match found. You can use the SWITCH function when you want to perform a "self-contained" exact match lookup with several possible results. When no match is...Read more
The IFNA function is designed to manage #N/A errors and ignore other errors. When a function returns an #N/A, it typically indicates that a value is not available or not found. In many cases, an #N/A error is useful information because it tells you the formula is not able to find a value....Read more
This formula relies on a table with columns for both the full state name and the 2-letter abbreviation. Because we are using VLOOKUP, the full name must be in the first column. For simplicity, the table has been named "states".
VLOOKUP is configured to get the lookup value from column C...Read more
In this example, the goal is to create initials or acronyms with a formula using the data in column B as the source text. The formula should parse the text in column B, build a list of capital letters used to start words and join the capital letters together in a single text string. The article...Read more
In this example, the goal is to count the number of cells in a range that are over a certain number of characters in length, where the number (n) is provided as a variable in cell F4. This problem can be solved with the SUMPRODUCT and LEN functions like this:
...Read more
The formula shown in this example uses a series of nested SUBSTITUTE functions to strip out parentheses, hyphens, colons, semi-colons, exclamation marks, commas, and periods. The process runs from the inside out, with each SUBSTITUTE replacing one character with a single space, then handing off...Read more
Number formats are a key feature in Excel. Their key benefit is that they change how numeric values look without actually changing any data. Excel ships with a huge number of different number formats, and you can easily define your own. This guide explains how custom number formats work in...Read more
The Excel TRUNC function does no rounding, it just returns a truncated number. It has an optional second argument (num_digits) to specify precision. When num_digits is not provided, it defaults to zero. In this formula for data validation, we use TRUNC to get the non-decimal part of a...Read more
