Split Text and Numbers

Split Text and Numbers

Use the SplitNumbers function to split text and numbers with different parameters depending on whether you want to extract the text or the number.

Remove text by position

Formulas to remove text by position

Learn how to remove text from a cell by position in Excel using the REPLACE, SUBSTITUTE, and TEXTRIGHT functions.

Remove text by matching

Remove text by matching

To remove text from a cell based on matching content in Excel (or replace multiple characters), use the SUBSTITUTE or MSUB functions.

Sum multiple rows or columns with XLOOKUP

sum multiple rows or columns using xlookup

To sum multiple rows or columns based on a lookup value, you can use the SUM function with the XLOOKUP function. Even if we have a definitive XLOOKUP guide, we learn something special daily. Today’s example will be on how to find the matching value in a table and sum multiple rows or columns. The … Read more

Nested XLOOKUP

nested xlookup using two way lookup

Learn how to create a nested XLOOKUP formula if you need two lookup values to find the matching record in Excel. We will show you how to use XLOOKUP to replace the INDEX and MATCH combination when you need a two-way lookup. For example, sometimes, we need two inputs to find the matching record in … Read more

Find the 2nd match using XLOOKUP

find-the-2nd-match-using-xlookup

This guide will explain how to find the 2nd match or the nth occurrence in an array using the XLOOKUP function. There is a common problem with all lookup functions in Excel: how to skip the exact match and get the 2nd, 3rd, or nth match. We will use the XLOOKUP function in the example … Read more

Lookup first negative value

lookup first negative value in Excel

Learn how to lookup the first negative value in a range using the XLOOKUP function or INDEX and MATCH functions. Generic Formula Here is the generic formula to lookup the first negative value in a range: In the formula, “rng” is the range where we find the first negative value; enter the formula in cell … Read more

Get first text value with XLOOKUP

get first text value in a range with xlookup

To lookup and get the first text value in a range, use the XLOOKUP function with a wildcard character match mode. From time to time, you want to find and extract the first text value from a range that contains numbers and text values. Use the XLOOKUP function and apply the wildcard character (*) as … Read more

Count cells that contain numbers

count cells that contain numbers excel

This guide will explain how to count cells that contain numbers in a range using the Excel COUNT function. In the example, we want to count all cells in a range where the cells are numbers. There are two common methods: using the COUNT function or applying the SUMPRODUCT function. If you want to learn … Read more

Highlight Every Other Row in Excel

highlight-every-other-row-excel

This tutorial will explain how to highlight every other row in Excel. Then, to improve your spreadsheet’s readability, alternate shade rows! Working with large data tables and organizing ranges in Excel is sometimes not too easy. In this Excel guide, I will cover the following topics: At first, you’ll learn how to highlight every other … Read more

Count unique values and distinct values

count unique values excel and distinct values

To count unique values in a list or range, use the UNIQUE function and configure the function’s third argument to TRUE. In Microsoft365 for Excel, you can create powerful formulas. Today’s guide will be on how to count unique and distinct values in a range using COUNT and UNIQUE functions. Differences between unique values and … Read more