'LEN function' category
Count a given pattern in a cell value
The formula in cell B6 counts how many times the string (D3) is found in a cell value (B3) even […]
The formula in cell B6 counts how many times the string (D3) is found in a cell value (B3) even […]
How to extract numbers from a cell value
The following array formula, demonstrated in cell C3, extracts all numbers from a cell value: =TEXTJOIN(, 1, TEXT(MID(B3, ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))), […]
The following array formula, demonstrated in cell C3, extracts all numbers from a cell value: =TEXTJOIN(, 1, TEXT(MID(B3, ROW($A$1:INDEX($A$1:$A$1000, LEN(B3))), […]
How to remove unwanted characters in a cell
Cell B3 contains a few odd characters and the formula in C3 shows the ANSI equivalent of each character in […]
Cell B3 contains a few odd characters and the formula in C3 shows the ANSI equivalent of each character in […]
How to use the LEN function
What is the LEN function? The LEN function returns a number representing the total count of characters in a given […]
What is the LEN function? The LEN function returns a number representing the total count of characters in a given […]
Identify all characters in a cell value
Table of Contents Identify all characters in a cell value Identify all characters in a cell value - Excel 365 […]
Table of Contents Identify all characters in a cell value Identify all characters in a cell value - Excel 365 […]
How to remove numbers from a cell value
This article demonstrates an array formula that extracts all characters except numbers from a cell, cell C3 contains the formula […]
This article demonstrates an array formula that extracts all characters except numbers from a cell, cell C3 contains the formula […]
SMALL function for text
This article demonstrates a formula that sorts text values based on character length, the Excel 365 dynamic array formula is […]
This article demonstrates a formula that sorts text values based on character length, the Excel 365 dynamic array formula is […]
Find last value in a column
This article demonstrates formulas that return the last value in a given cell range or column. The image above shows […]
This article demonstrates formulas that return the last value in a given cell range or column. The image above shows […]
List all permutations with a condition
I got a question a while ago about permutations, in essence how to find every permutation between 0 and 9 […]
I got a question a while ago about permutations, in essence how to find every permutation between 0 and 9 […]
How to count the number of values separated by a delimiter
This article demonstrates formulas that count values in cell based on a delimiting character. The image above shows a formula […]
This article demonstrates formulas that count values in cell based on a delimiting character. The image above shows a formula […]
Working with classic ciphers in Excel
What's on this page Reverse text Insert random characters Convert letters to numbers How to shuffle characters in the alphabet […]
What's on this page Reverse text Insert random characters Convert letters to numbers How to shuffle characters in the alphabet […]
Sort and return unique distinct single digits from cell range
This article demonstrates a formula that filters unique distinct single digits from a cell range containing numbers. Cell range B3:B6 […]
This article demonstrates a formula that filters unique distinct single digits from a cell range containing numbers. Cell range B3:B6 […]
Count specific multiple text strings in a given cell range
This article demonstrates an array formula that counts how many times multiple text strings exist in a cell range. The […]
This article demonstrates an array formula that counts how many times multiple text strings exist in a cell range. The […]
Partial match and return multiple adjacent values
This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]
This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]
Analyze word frequency in a cell range
This article demonstrates two ways to calculate the number of times each word appears in a given range of cells. […]
This article demonstrates two ways to calculate the number of times each word appears in a given range of cells. […]
Fuzzy lookups
In this post I will describe a basic user defined function with better search functionality than the array formula in […]
In this post I will describe a basic user defined function with better search functionality than the array formula in […]
Fuzzy VLOOKUP
This formula returns multiple values even if they are arranged differently or have minor misspellings compared to the lookup value.
This formula returns multiple values even if they are arranged differently or have minor misspellings compared to the lookup value.
Text to columns: Split words in a cell
This blog article describes how to split strings in a cell with space as a delimiting character, like Text to […]
This blog article describes how to split strings in a cell with space as a delimiting character, like Text to […]
Lookup and return multiple values concatenated into one cell
This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]
This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]
Filter duplicate values from a range that begins with string
The array formula in cell B10 extracts duplicate values from cell range B2:D4 if they begin with the condition specified […]
The array formula in cell B10 extracts duplicate values from cell range B2:D4 if they begin with the condition specified […]
Extract unique distinct values from cell range that begins with string
The array formula in cell B10 extracts unique distinct values from cell range B2:D4 that begins with a given condition […]
The array formula in cell B10 extracts unique distinct values from cell range B2:D4 that begins with a given condition […]
Formula for matching a date within a date range
This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]
This article demonstrates how to match a specified date to date ranges. The image above shows a formula in cell […]
5 easy ways to VLOOKUP and return multiple values
This post explains how to lookup a value and return multiple values. No array formula required.
This post explains how to lookup a value and return multiple values. No array formula required.
Sort text values by length
The image above demonstrates a formula in cell D3 that sorts values based on character length, the value with the […]
The image above demonstrates a formula in cell D3 that sorts values based on character length, the value with the […]
Create unique distinct list sorted based on text length
The formula in cell D3 extracts unique distinct values from B3:B20 sorted based on the number of characters, it works […]
The formula in cell D3 extracts unique distinct values from B3:B20 sorted based on the number of characters, it works […]
Sum cells containing numbers and text based on a condition
Question: I want to sum cells that have a "C" and a decimal number. The cells have other numbers and […]
Question: I want to sum cells that have a "C" and a decimal number. The cells have other numbers and […]
Count text string in a range (case sensitive)
Question: How do I count the number of times a text string exists in a column? The text string may […]
Question: How do I count the number of times a text string exists in a column? The text string may […]
Count a specific text string in a cell
Table of Contents Count a specific text string in a cell Count text string in a range (case sensitive) Count […]
Table of Contents Count a specific text string in a cell Count text string in a range (case sensitive) Count […]
Count how many times a string exists in a cell range (case insensitive)
Question: How do I count how many times a word exists in a range of cells? It does not have […]
Question: How do I count how many times a word exists in a range of cells? It does not have […]
How to extract email addresses from an Excel sheet
Question: How to extract email addresses from this sheet? Answer: It depends on how the emails are populated in your worksheet? […]
Question: How to extract email addresses from this sheet? Answer: It depends on how the emails are populated in your worksheet? […]
Excel formula categories
AverageChooseCombine MergeCompareConcatenateConditional FormattingCountCount valuesDatesDuplicatesExtractFilterFilter recordsFiltered valuesFilterxmlHyperlinkIf cellIndex MatchLogicLookupsMatchMaxMinNumbers in sumOR logicOverlappingPartial matchRecordsSequenceSmallSort bySort valuesSumSumifsSumproductString manipulationTimeUnique distinct recordsUnique distinct valuesUnique recordsUnique valuesVlookupVlookup return values