## Archive for MID function

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

Extract k-th word in cell value

The formula displayed above in cell range D3:D9 extracts a word based its position in a cell value. For example, […]

DATEVALUE function not working

The DATEVALUE function returns an Excel date value (serial number) based on a date stored as text. However, it must […]

Match a range value containing both text and numerical characters

Formula in cell C11: =LOOKUP(MID(C10, 4, 999)*1, MID(B3:B8, 4, 999)*1, D3:D8) This formula is an array formula. To enter an array […]

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

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

The MID function returns a substring from a string based on the starting position and the number of characters you want […]

Table of contents Initials from first and last names Initials from first, middle and last names Create middle name initials […]

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

This formula returns multiple values even if they are arranged differently or have minor misspellings compared to the lookup value.

Remove duplicate text strings based on the 4 last characters

Sean asks: The good thing about this formula is that it is short and easy to remember. The main drawback […]

I read an interesting blog post Is A Particular Word Contained In A Text String? on Spreadsheetpage. That inspired me […]