MID Function Examples – Excel, VBA, & Google Sheets

Download Example Workbook

Download the example workbook

This tutorial demonstrates how to use the Excel MID Function in Excel to extract text from a cell.

MID Formula Main

 

How to use the MID Function in Excel:

The MID function extracts a specific number of characters you specify from anywhere you want.

=MID(B3,C3,D3)

MID 01

It can start from any number seen in column C and return any number of characters in column D.

MID to Grab String Between Different Delimiter

If the string you want is between two different delimiters, this is a pretty straightforward one. For instance, we might want the string between dash (“-”) and slash (“/”).

=MID(B3,FIND("-",B3)+1,FIND("/",B3)-FIND("-",B3)-1)

MID 02

In this case here, we can use FIND to check which character position the dash starts and add 1 to start extracting from there.

Use FIND again to check which character position the slash starts and minus the position of the dash and 1 to obtain the length of the string we want.

You can also use SEARCH. The difference between FIND and SEARCH is that the former is case-sensitive. Finding a symbol such as space wouldn’t matter.

=MID(B3,FIND("-",B3)+1,SEARCH("/",B3)-SEARCH("-",B3)-1)

MID 03

MID to Grab String Between the Same Delimiter

If the string has the same delimiter, it is a little tougher than the one above because FIND grabs the first occurrence. For instance, we might want the string between the first and second spaces. But you can indicate a starting position in FIND as well.

=MID(B3,FIND(" ",B3)+1,FIND(" ",B3,FIND(" ",B3)+1)-FIND(" ",B3)-1)

MID 04

It uses FIND just like the above example to check which character position the space starts and add 1 to start extracting from there.

Use FIND again to check which character position the second space starts by starting to find 1 character after the position of the first space.

And lastly minus the position of the first space and 1 to obtain the length of the string we want.

You can also use SEARCH as well.

=MID(B3,FIND(" ",B3)+1,SEARCH(" ",B3,FIND(" ",B3)+1)-SEARCH(" ",B3)-1)

MID 05

MID with Numbers / Dates

Note that MID is a text function and will always return a text value, Numerical functions won’t work:

MID with Number

To overcome the issues above, you can use the VALUE Function to convert from text to values.

=VALUE(MID(B3,FIND(":",B3)+2,FIND(" ",B3,FIND(" ",B3)+1)-FIND(":",B3)-2))

MID 09

MID 10

MID to Split by Common Delimiter

You might want to split all the words in different rows. Hence:

=TRIM(MID(SUBSTITUTE(B$3," ",REPT(" ",LEN(B$3))),(ROWS(B3:B$3)-1)*LEN(B$3)+1,LEN(B$3)))

Excel is really awesome

What is does is use SUBSTITUTE to substitute all the spaces with an influx of repeated spaces using REPT. It results in the text looking like this:
“Excel                       is                       really                       awesome”

The influx of repeated is carefully repeated with a total length of the original text by using LEN. That means the position number to start extracting from is a multiple of the total length. For the first word, extract from position 1. For the second, it will be total length + 1. For the third, total length x 2 + 1. Etc. To get the nth word, use ROWS.

Lastly, the number of characters is always using the total length of the original text.

Find Nth word in the String

We could combine TRIM, LEN, SUBSTITUTE, REPT  with MID as follows to get last word of the string.

=TRIM(MID(SUBSTITUTE(A$2," ",REPT(" ",LEN(A$2))), (B2-1)*LEN(A$2)+1, LEN(A$2)))

Which would give us the following results:

Find Nth Word

Reverse a text string

You can reverse a text string by combining MID function with TEXTJOIN as follow.

=TEXTJOIN("",1,MID(A2,{10,9,8,7,6,5,4,3,2,1},1))

Which would give us the following results:

reverse string

MID in Google Sheets

The MID Function works exactly the same in Google Sheets as in Excel:

MID G Sheet

 

MID Examples in VBA

You can also use the MID function in VBA. Type:
application.worksheetfunction.mid(text,start_num,num_chars)
For the function arguments (text, etc.), you can either enter them directly into the function, or define variables to use instead.