## How to use the MID function

The MID function returns a substring from a string based on the starting position and the number of characters you want to extract.

#### Table of Contents

- MID Function Syntax
- MID Function arguments
- MID function example
- MID function from right
- MID function with FIND function
- MID function with search
- MID function between two characters
- MID function - split characters into an array
- MID function - reverse characters in value
- Excel text functions
- Get Excel *.xlsx file

## 1. Excel Function Syntax

MID(*text*, *start_num*, *num_chars*)

## 2. Arguments

text |
Text string or a cell reference to a text string. |

start_num |
The starting point. |

num_chars |
The number of characters you want to extract. |

## 3. MID function example

The first argument is the text string, the second argument is the start position. In this case character number 3, note spaces are counted.

The third argument is how many characters you want to extract counted from the start position.

## 4. MID function from right

The formula shown in cell C3 in the image above extracts the last word from the right based on the last space character.

Formula in cell C3:

### 4.1 Explaining formula

#### Step 1 - Remove space charcaters

The TRIM function removes leading and trailing space characters.

TRIM(B3)

becomes

TRIM("A blue whale")

and returns "A blue whale".

#### Step 2 - Replace space character

The SUBSTITUTE function allows you to replace a given string of characters.

SUBSTITUTE(text, old_text, new_text, [instance_num])

SUBSTITUTE(TRIM(B3), " ", "")

becomes

SUBSTITUTE("A blue whale", " ", "")

and returns "Abluewhale".

#### Step 3 - Count characters

The LEN function returns a number representing the number of characters in a given string.

LEN(SUBSTITUTE(TRIM(B3), " ", ""))

becomes

LEN("Abluewhale")

and returns 10.

#### Step 4 - Subtract total character length with string without space characters.

The minus character lets you subtract numbers in an Excel formula.

LEN(TRIM(B3))-LEN(SUBSTITUTE(TRIM(B3), " ", ""))

becomes

12-10 equals 2. There are two space characters in the string.

#### Step 5 - Replace last space with | character

SUBSTITUTE(B3, " ", "|", LEN(TRIM(B3))-LEN(SUBSTITUTE(TRIM(B3), " ", "")))

becomes

SUBSTITUTE(B3, " ", "|", 2)

becomes

SUBSTITUTE("A blue whale", " ", "|", 2)

and returns "A blue|whale".

#### Step 6 - Find | character in string

The SEARCH function returns a number representing the position of character at which a specific text string is found reading left to right.

SEARCH(find_text,within_text, [start_num])

SEARCH("|", SUBSTITUTE(B3, " ", "|", LEN(TRIM(B3))-LEN(SUBSTITUTE(TRIM(B3), " ", ""))))

becomes

SEARCH("|", "A blue|whale")

and returns 7. Character | is in the seventh positiion in the string.

#### Step 7 - Extract last word

MID(TRIM(B3), SEARCH("|", SUBSTITUTE(B3, " ", "|", LEN(TRIM(B3))-LEN(SUBSTITUTE(TRIM(B3), " ", "")))), LEN(B3))

becomes

MID(TRIM(B3), 7, LEN(B3))

becomes

MID(TRIM(B3), 7, 12)

becomes

MID("A blue whale", 7, 12)

and returns "whale".

## 5. MID function with find

The formula in cell C3 uses the FIND function to search for a string also considering upper and lower letters as well.

Formula in cell C3:

### 5.1 Explaining formula

#### Step 1 - Find position of a given string

The FIND function returns the position of a specific string in another string, reading left to right. Note, the FIND function is case-sensitive.

FIND(*find_text*,*within_text*, [*start_num*])

FIND("a", B3)

becomes

FIND("a", "ABA aFC AGH")

and returns 5.

#### Step 2 - Extract three characters based on position of a given string

MID(B3, FIND("a", B3), 3)

becomes

MID(B3, 5, 3)

becomes

MID("ABA aFC AGH", 5, 3)

and returns "aFC".

## 6. MID function with search

The formula in cell C3 extracts a substring based on a character position. The position is calculated using the SEARCH function.

Formula in cell C3:

### 6.1 Explaining formula

#### Step 1 - Find space charcater position in string

The SEARCH function returns a number representing the position of character at which a specific text string is found reading left to right.

SEARCH(*find_text*,*within_text*, [*start_num*])

SEARCH(" ", B3)

becomes

SEARCH(" ", "Red apple")

and returns 4.

#### Step 2 - Extract substring based on position

MID(B3, SEARCH(" ", B3), 6)

becomes

MID(B3, 4, 6)

becomes

MID("Red apple", 4, 6)

and returns "apple".

## 7. MID function between two characters

The formula in cell C3 extracts the characters between two given characters, in this example, the hastag character.

Formula in cell C3:

### 7.1 Explaining formula

#### Step 1 - Find character in string

The SEARCH function returns a number representing the position of the character at which a specific text string is found reading left to right.

SEARCH(*find_text*,*within_text*, [*start_num*])

SEARCH("#", B3)+1

becomes

4+1 equals 5.

#### Step 2 - Count characters in cell

The LEN function returns a number representing the number of characters in a given string.

LEN(*value*)

LEN(B3)

becomes

LEN("ABA#132#CFG")

and returns 11.

#### Step 3 - Extract string

MID(B3, SEARCH("#", B3)+1, LEN(B3))

becomes

MID(B3, 5, LEN(B3))

becomes

MID(B3, 5, 11)

becomes

MID("ABA#132#CFG", 5, 11)

and returns "132#CFG".

#### Step 4 - Find second instance of character

SEARCH("#", MID(B3, SEARCH("#", B3)+1, LEN(B3)))-1

becomes

SEARCH("#", "132#CFG")-1

becomes

4-1 equals 3.

#### Step 5 - Extract string

LEFT(MID(B3, SEARCH("#", B3)+1, LEN(B3)), SEARCH("#", MID(B3, SEARCH("#", B3)+1, LEN(B3)))-1)

becomes

LEFT("132#CFG", SEARCH("#", MID(B3, SEARCH("#", B3)+1, LEN(B3)))-1)

becomes

LEFT("132#CFG", 3)

and returns "132".

## 8. MID function - split characters

This example demonstrates a formula that splits each character into a vertical array, there is also a formula below that splits characters horizontally.

Formula in cell D3:

### 8.1 Explaining formula

#### Step 1 - Count characters in cell B3

The LEN function returns a number representing the number of characters in a given string.

LEN(*value*)

LEN(B3)

becomes

LEN("strawberry")

and returns 10. There are ten characters in cell B3.

#### Step 2 - Create a list of numbers

The SEQUENCE function creates a list of sequential numbers.

SEQUENCE(*rows*, [*columns*], [*start*], [*step*])

SEQUENCE(LEN(B3))

becomes

SEQUENCE(10)

and returns {1; 2; 3; 4; 5; 6; 7; 8; 9; 10}.

#### Step 3 - Split characters in value

MID(B3, SEQUENCE(LEN(B3)), 1)

becomes

MID("strawberry", {1; 2; 3; 4; 5; 6; 7; 8; 9; 10}, 1)

and returns {"s"; "t"; "r"; "a"; "w"; "b"; "e"; "r"; "r"; "y"}.

Use this Excel 365 dynamic array formula to split characters horizontally:

Array formula for earlier Excel versions:

## 9. MID function - reverse characters in a value

Excel 365 dynamic array formula in cell D3:

### Explaining formula

#### Step 1 - Count characters in cell B3

The LEN function returns a number representing the number of characters in a given string.

LEN(*value*)

LEN(B3)

becomes

LEN("strawberry")

and returns 10.

#### Step 2 - Count characters in cell B3

The SEQUENCE function creates a list of sequential numbers.

SEQUENCE(*rows*, [*columns*], [*start*], [*step*])

SEQUENCE(LEN(B3), , LEN(B3), -1)

becomes

SEQUENCE(10, , 10, -1)

and returns {10; 9; 8; 7; 6; 5; 4; 3; 2; 1}.

#### Step 3 - Split characters in value

MID(B3, SEQUENCE(LEN(B3), , LEN(B3), -1), 1)

becomes

MID(B3, {10; 9; 8; 7; 6; 5; 4; 3; 2; 1}, 1)

becomes

MID("strawberry", {10; 9; 8; 7; 6; 5; 4; 3; 2; 1}, 1)

and returns {"y"; "r"; "r"; "e"; "b"; "w"; "a"; "r"; "t"; "s"}.

#### Step 4 - Join characters

The TEXTJOIN function combines text strings from multiple cell ranges.

TEXTJOIN(*delimiter*, *ignore_empty*, *text1*, *[text2]*, ...)

TEXTJOIN(, FALSE, MID(B3, SEQUENCE(LEN(B3), , LEN(B3), -1), 1))

becomes

TEXTJOIN(, FALSE, {"y"; "r"; "r"; "e"; "b"; "w"; "a"; "r"; "t"; "s"})

and returns "yrrebwarts".

### 'MID' function examples

The following 21 articles contain the MID function.

The formula in cell B6 counts how many times the string (D3) is found in a cell value (B3) even […]

Table of Contents Count a specific text string in a cell Count text string in a range (case sensitive) Count […]

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

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

Table of Contents Extract the last word Extract the last letter Extract the last number Get Excel *.xlsx file 1. […]

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

This article demonstrates formulas that return the last value in a given cell range or column. The image above shows […]

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

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

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

Question: How to extract email addresses from this sheet? Answer: It depends on how the emails are populated in your worksheet? […]

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

This article demonstrates an array formula that extracts all characters except numbers from a cell, cell C3 contains the formula […]

Cell B3 contains a few odd characters and the formula in C3 shows the ANSI equivalent of each character in […]

Table of Contents Identify all characters in a cell value Identify all characters in a cell value - Excel 365 […]

I got a question a while ago about permutations, in essence how to find every permutation between 0 and 9 […]

This article demonstrates a macro that lets you create a list of permutations, repetitions are allowed, based on a number […]

This article demonstrates how to match a value containing both text and digits to ranges. The search value is specified […]

This article demonstrates a formula that filters unique distinct single digits from a cell range containing numbers. Cell range B3:B6 […]

This blog article describes how to split strings in a cell with space as a delimiting character, like Text to […]

What's on this page Reverse text Insert random characters Convert letters to numbers How to shuffle characters in the alphabet […]

## Functions in this article

### Functions in 'Text' category

The MID function function is one of many functions in the 'Text' category.

## How to comment

How to add a formula to your comment<code>Insert your formula here.</code>

Convert less than and larger than signsUse html character entities instead of less than and larger than signs.

< becomes < and > becomes >

How to add VBA code to your comment[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

How to add a picture to your comment:Upload picture to postimage.org or imgur

Paste image link to your comment.

Contact OscarYou can contact me through this contact form