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

Rearrange cells in a cell range to vertically distributed values

The formula in cell B8 uses a named range to calculate the row and column needed to extract the correct […]

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

Sometimes when you sort values you get unexpected results, the cause is probably unwanted characters in a cell. The same […]

The NETWORKDAYS function allows you to count weekdays or workdays between two dates. It ignores weekends and a custom date list […]

How to remove numbers from a cell value

The array formula in cell C3:C7 extracts everything except numbers from cell B3. The following formula contains the TEXTJOIN function […]

Count cells containing text from list

The array formula in cell F3 counts cells in column B that contains at least one of the values in […]

Count overlapping days in multiple date ranges

The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]

Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]

The ROW function calculates the row number of a cell reference. Excel Function Syntax ROW(reference) Arguments reference Optional. A reference […]

Chirag asks: I want to separate numbers from following text: Abc123bx45 as a result 123 and 45 should be in […]

Calculate average of last 10 data with possible blank cells

Question: Answer: This array formula creates a dynamic range, filtering the 10 last data. Adjust cell ranges $A$1:$A$25 in formula below. […]

Match two criteria and return multiple records

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]

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.

How to create a list of random unique numbers

Question: How do I create a random list of unique numbers from say 1 to 10, without using VBA and […]

Table of Contents Sort a column using array formula Two columns sorting by the second column Sort alphanumeric values I […]

Extract unique distinct values from a multi-column cell range

Question: I have cell values spanning over several columns and I want to create a unique list from that range. […]

Table of Contents Find closest value Find closest values Find closest values and return adjacent values Find closest value with […]