Workaround for the TEXTSPLIT function – LAMBDA function
The TEXTSPLIT function works only with single cells. If you try to use a cell range the TEXTSPLIT function returns […]

How to decode URL-encoded strings
This formula decodes a URL-encoded string, replacing specific percentage symbol (%) and a hexadecimal number with non-alphanumeric characters. Excel 365 […]

Extract values between two given delimiting strings
The image above demonstrates a rather small formula in cell D3 that extracts values in cell B3 based on two […]

Rearrange values in a cell range to a single column
This article demonstrates formulas that rearrange values in a cell range to a single column. Table of Contents Rearrange cells […]

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))), […]

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 […]

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 […]

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 […]

If cell contains text from list
This article demonstrates several ways to check if a cell contains any value based on a list. The first example […]

Concatenate unique distinct values
This article demonstrates Excel formulas that extract unique distinct values from a cell range and concatenates the values using any […]

Reverse two-way lookups
This article demonstrates a few different formulas that extract values from the table column header names and the right-most column. […]

Split search value using a delimiter and search for each substring
This article demonstrates formulas and a UDF that searches for values in a table based on concatenated values and returns […]

How to use the TEXTJOIN function
The TEXTJOIN function is a relatively new function introduced in Excel 2019, it is like the CONCATENATE function on steroids […]

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 […]

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 […]

Identify rows of overlapping records
This article demonstrates a formula that points out row numbers of records that overlap the current record based on a […]

Lookup multiple values in one cell
This article explains how to perform multiple lookups based on values in one cell with a delimiting character using a […]

VLOOKUP and return multiple values across columns
This article demonstrates a formula that lets you extract non-empty values across columns based on a condition. The image above […]

How to use VLOOKUP/XLOOKUP with multiple conditions
I will in this article demonstrate how to use the VLOOKUP function with multiple conditions. The function was not built […]

How to create name initials
The array formula in cell C3 extracts the first character from first, middle and last name. The formula works fine […]

Unique distinct records sorted based on count or frequency
This article demonstrates how to sort records in a data set based on their count meaning the formula counts each […]

Rearrange data
Sean asks: Sheet1A B C D 8 Country Europe 9 Lights 100 10 Type A 200 11 12 Country USA […]

Filter unique distinct values, sorted and blanks removed from a range
EEK asks: I am looking for the same formula on this page, but targeting a range of MxN (spanning multiple […]

Concatenate cell values
Joining multiple cell values in Excel is not easy, for example, the CONCATENATE function allows you to only reference a […]

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 […]

Filter unique strings from a cell range
This blog post describes how to create a list of unique words from a cell range. Unique words are all […]

Break up values in a cell range into separate cells based on a space character as a delimiter
This article demonstrates how to convert a range of cells containing strings separated by a delimiter into a range of […]

Populate cells dynamically in a weekly schedule
In this post I am going to add one more function to the weekly schedule I built in a previous […]

Filter duplicate values in a range using “contain” condition
The array formula in cell B10 extracts duplicate values from cell range B2:D4 if they contain string specified in cell […]

Extract unique distinct text values containing string in a range
The formula in cell B10 extracts unique distinct values from cell range B2:d4 that contains the string specified in cell […]

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 […]

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 […]

Filter unique values from a cell range
Unique values are values occurring only once in cell range. This is what I am going to demonstrate in this blog […]

Sort a range based on value frequency
The formula in cell B8 extracts a list sorted based on frequency. Array formula in B8: =TEXTJOIN("", TRUE, IF(MIN(IF((MAX(IF(COUNTIF($B$7:B7, $B$2:$E$5)=0, […]

Filter common values between two ranges
The image above shows an array formula in cell B12 that extracts values shared by cell range B2:D4 (One) and […]

Filter values occurring in range 1 but not in range 2
The formulas above extracts values that exists only in one or the other cell range, if you are looking for […]

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? […]