## LEN function

Excel » Functions » Text » LEN function »

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

How to use the LEN function

The LEN function returns the number of characters in a cell value. Table of Contents LEN function Syntax LEN function […]

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

SMALL function for text

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

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

Count comma separated values

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

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

Count multiple text strings in a cell range

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

Fuzzy vlookup [Array formula]

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

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

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

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.

Sort text values by length

Array formula in B2: =INDEX(\$B\$3:\$B\$20, MATCH(LARGE(LEN(\$B\$3:\$B\$20), ROWS(\$A\$1:A1)), LEN(\$B\$3:\$B\$20)*(COUNTIF(\$F\$2:F2, \$B\$3:\$B\$20)<COUNTIF(\$B\$3:\$B\$20, \$B\$3:\$B\$20)), 0)) copied down as far as needed. To enter an […]

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

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

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

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

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

How to extract email addresses from an Excel sheet

Question: How to extract email addresses from this sheet? (See pic below) Answer: It depends on how the emails are […]