MID is one of the Text functions that Microsoft Excel provides for manipulating text strings. At the most basic level, it is used to extract a substring from the middle of the text string. In this tutorial, we will discuss the syntax and specificities of the Excel MID function, and then you will learn a few creative uses to accomplish challenging tasks.

## Excel MID function - syntax and basic uses

Generally speaking, the MID function in Excel is designed to pull a substring from the middle of the original text string. Technically speaking, the MID function returns the specified number of characters starting at the position you specify.

The Excel MID function has the following arguments:

Where:

**Text**is the original text string.**Start_num**is the position of the first character that you want to extract.**Num_chars**is the number of characters to extract.

All 3 arguments are required.

For example, to pull 7 characters from the text string in A2, starting with the 8^{th} character, use this formula:

`=MID(A2,8, 7)`

The result might look something similar to this:

### 5 things you should know about Excel MID function

As you have just seen, there's no rocket science in using the MID function in Excel. And remembering the following simple facts will keep you safe from most common errors.

- The MID function always returns a
**text string**, even if the extracted substring contains only digits. This may be critical if you wish to use the result of your Mid formula within other calculations. To convert an output into a number, use MID in combination with the VALUE function as shown in this example. - If
*start_num*is greater than the overall length of the original text, an Excel Mid formula returns an empty string ("").

- If
*start_num*is less than 1, a Mid formula returns the #VALUE! error. - If num_chars is less than 0 (negative number), a Mid formula returns the #VALUE! error. If num_chars is equal to 0, it outputs an empty string (blank cell).
- If the sum of
*start_num*and*num_chars*exceeds the total length of the original string, the Excel MID function returns a substring starting from*start_num*and up to the last character.

## Excel MID function - formula examples

When dealing with real-life tasks in Excel, you will most often need to use MID in combination with other functions as demonstrated in the following examples.

### How to extract first and last name

If you've had a chance to read our recent tutorials, you already know how to pull the first name using the LEFT function and get the last name with the RIGHT function. But as is often the case in Excel, the same thing can be done in a variety of ways.

#### MID formula to get the first name

Assuming the full name is in cell A2, first and last names separated with a space character, you can pull the first name using this formula:

`=MID(A2,1,SEARCH(" ",A2)-1)`

The SEARCH function is used to scan the original string for the space character (" ") and return its position, from which you subtract 1 to avoid trailing spaces. And then, you use the MID function to return a substring beginning with the fist character and up to the character preceding the space, thus fetching the first name.

#### MID formula to get the last name

To extract the last name from A2, use this formula:

`=TRIM(MID(A2,SEARCH(" ",A2),LEN(A2)))`

Again, you use the SEARCH function to determine the starting position (a space). There is no need for us to calculate the end position exactly (as you remember, if *start_num* and *num_chars* combined is bigger than the total string length, all remaining characters are returned). So, in the *num_chars* argument, you simply supply the total length of the original string returned by the LEN function. Instead of LEN, you can put a number that represents the longest surname you expect to find, for example 100. Finally, the TRIM function removes extra spaces, and you get the following result:

Tip. To extract the first and last word from a string with a simpler formula, you can use the custom ExtractWord function.

### How to get substring between 2 delimiters

Taking the previous example further, if besides first and last names cell A2 also contains a middle name, how do you extract it?

Technically, the task boils down to working out the positions of two spaces in the original string, and you can have it done in this way:

- Like in the previous example, use the SEARCH function to determine the position of the first space (" "), to which you add 1 because you want to start with the character that follows the space. Thus, you get the
**start_num**argument of your Mid formula: SEARCH(" ",A2)+1 - Next, get the position of the 2
^{nd}space character by using nested Search functions that instruct Excel to start searching from the 2^{nd}occurrence of the space character: SEARCH(" ",A2,SEARCH(" ",A2)+1)To find out the number of characters to return, subtract the position of the 1

^{st}space from the position of the 2^{nd}space, and then subtract 1 from the result since you don't want any extra spaces in the resulting substring. Thus, you have the**num_chars**argument: SEARCH (" ", A2, SEARCH (" ",A2)+1) - SEARCH (" ",A2)

With all the arguments put together, here comes the Excel Mid formula to extract a substring between 2 space characters:

**=MID(A2, SEARCH(" ",A2)+1, SEARCH (" ", A2, SEARCH (" ",A2)+1) - SEARCH (" ",A2)-1)**

The following screenshot shows the result:

In a similar manner, you can extract a substring between any other delimiters:

*string*, SEARCH(

*delimiter*,

*string*)+1, SEARCH (

*delimiter*,

*string*, SEARCH (

*delimiter*,

*string*)+1) - SEARCH (

*delimiter*,

*string*)-1)

For example, to pull a substring that is separated by a comma and a space, use this formula:

**=MID(A2,SEARCH(", ",A2)+1,SEARCH(", ",A2,SEARCH(", ",A2)+1)-SEARCH(", ",A2)-1)**

In the following screenshot, this formula is used to extract the state, and it does the job perfectly:

### How to extract Nth word from a text string

This example demonstrates an inventive use of a complex Mid formula in Excel, which includes 5 different functions:

- LEN - to get the total string length.
- REPT - repeat a specific character a given number of times.
- SUBSTITUTE - replace one character with another.
- MID - extract a substring.
- TRIM - remove extra spaces.

The generic formula is as follows:

*string*," ",REPT(" ",LEN(

*string*))), (

*N*-1)*LEN(

*string*)+1, LEN(

*string*)))

Where:

*String*is the original text string from which you want to extract the desired word.*N*is the number of word to be extracted.

For instance, to pull the 2^{nd} word from the string in A2, use this formula:

`=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))), (2-1)*LEN(A2)+1, LEN(A2)))`

Or, you can input the number of the word to extract (N) in some cell and reference that cell in your formula, like shown in the screenshot below:

#### How this formula works

In essence, the formula wraps each word in the original string with many spaces, finds the desired "spaces-word-spaces" block, extracts it, and then removes extra spaces. To be more specific, the formula works with the following logic:

- The SUBSTITUTE and REPT functions replace each space in the string with multiple spaces. The number of additional spaces is equal to the total length of the original string returned by LEN: SUBSTITUTE(A2," ",REPT(" ",LEN(A2)))
You can think of an intermediate result as of "asteroids" of words drifting in space, like this:

*spaces-word1-spaces-word2-spaces-word3-…*This "spacious" string is supplied to the**text**argument of our Mid formula. - Next, you work out the starting position of the substring of interest (
**start_num**argument) using the following equation: (N-1)*LEN(A1)+1. This calculation returns either the position of the first character of the desired word or, more often, the position of some space character in the preceding space separation. - The number of characters to extract (
**num_chars**argument) is the easiest part - you simply take the overall length of the original string: LEN(A2). At this point, you are left with*spaces-desired word-spaces*substring. - Finally, the TRIM function gets rid of leading and trailing spaces.

The above formula works fine in most situations. However, if there happen to be 2 or more **consecutive spaces between words**, it yields wrong results. To fix this, nest another TRIM function inside SUBSTITUTE to remove excess in-between spaces except for a single space character between words, like this:

`=TRIM(MID(SUBSTITUTE(TRIM(A2)," ",REPT(" ",LEN(A2))), (B2-1)*LEN(A2)+1, LEN(A2)))`

The following screenshot demonstrates the improved formula in action:

If your source strings contain multiple spaces between words as well as very big and very small words, additionally embed a TRIM function into each LEN, just to keep you on the safe side:

`=TRIM(MID(SUBSTITUTE(TRIM(A2)," ",REPT(" ",LEN(TRIM(A2)))), (B2-1)*LEN(TRIM(A2))+1, LEN(TRIM(A2))))`

I agree that this formula looks a bit cumbersome, but it impeccably handles all kinds of strings.

Tip. See how to extract any Nth word from text using a more compact and straightforward formula.

### How to extract a word containing a specific character(s)

This example shows another non-trivial Excel Mid formula that pulls a word containing a specific character(s) from anywhere in the original text string:

*string*," ",REPT(" ",99)),MAX(1,FIND(

*char*,SUBSTITUTE(

*string*," ",REPT(" ",99)))-50),99))

Assuming the original text is in cell A2, and you are looking to get a substring containing the "$" character (the price), the formula takes the following shape:

`=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",99)),MAX(1,FIND("$",SUBSTITUTE(A2," ",REPT(" ",99)))-50),99))`

In a similar fashion, you can extract email addresses (based on the "@" char), web-site names (based on "www"), and so on.

#### How this formula works

Like in the previous example, the SUBSTITUTE and REPT functions turn every single space in the original text string into multiple spaces, more precisely, 99 spaces.

The FIND function locates the position of the desired character ($ in this example), from which you subtract 50. This takes you 50 characters back and puts somewhere in the middle of the 99-spaces block that precedes the substring containing the specified character.

The MAX function is used to handle the situation when the desired substring appears in the beginning of the original text string. In this case, the result of FIND()-50 will be a negative number, and MAX(1, FIND()-50) replaces it with 1.

From that starting point, the MID function collects the next 99 characters and returns the substring of interest surrounded by lots of spaces, like this: *spaces-substring-spaces*. As usual, the TRIM function helps you eliminate extra spaces.

Tip. If the substring to be extracted is very big, replace 99 and 50 with bigger numbers, say 1000 and 500.

### How to force an Excel Mid formula to return a number

Like other Text functions, Excel MID always returns a text string, even if it contains only digits and looks much like a number. To turn the output into a number, simply "warp" your Mid formula into the VALUE function that converts a text value representing a number to a number.

For example, to extract a 3-char substring beginning with the 7^{th} character and convert it to a number, use this formula:

`=VALUE(MID(A2,7,3))`

The screenshot below demonstrates the result. Please notice the right-aligned numbers pulled into column B, as opposed to the original left-aligning text strings in column A:

The same approach works for more complex formulas as well. In the above example, assuming the error codes are of a variable length, you can extract them using the Mid formula that gets a substring between 2 delimiters, nested within the VALUE function:

`=VALUE(MID(A2,SEARCH(":",A2)+1,SEARCH(":",A2,SEARCH(":",A2)+1)-SEARCH(":",A2)-1))`

This is how you use the MID function in Excel. To better understand the formulas discussed in this tutorial, you are welcome to download a sample workbook below. I thank you for reading and hope to see you on our blog next week!

## Download practice workbook

Excel MID function - formula examples (.xlsx file)

## 301 comments

Hello

My problem is the following:

I cell A1 I have a string like "ADZ3RT(500R|700R)".

This has to be parsed into into:

ADZ3RT

ADZ3RT500R

ADZ3RT700R

Is there any solution?

Thanks for your help

Hello Manfred!

You need to use three different formulas to get these three numbers. For the first value, I recommend using these instructions: Get text before a specific character.

=LEFT(A1, SEARCH("(",A1)-1)

For the second value, extract the text before the “|” character and remove the unwanted characters as described here: How to delete special / unwanted characters in Excel.

=SUBSTITUTE(LEFT(A1, SEARCH("|",A1)-1),"(","")

For the third value, extract text strings before “|” and between “|” and “)”. I recommend this manual: How to extract text between two characters in Excel. Combine these text strings using concatenation operator (&).

=LEFT(A1, SEARCH("(",A1)-1) & MID(A1, SEARCH("|", A1)+1, SEARCH(")", A1) - SEARCH("|", A1) -1)

how can I get only letter "PRV" from this text "10001_PRV01" in excel? please help someone.

what would be the formula if the number of Letters in the middle are not consistent but there are always 2 digits on the right. Thanks in advance.

Hi! Find the position of the “_” character using the SEARCH function and extract the next 3 characters. Look for the example formulas here: Extract text after a certain character.

Please try the following formula:

=MID(A1,SEARCH("_",A1)+1,3)

Hi! To extract characters from text in Excel using regular expressions, you can use the VBA (Visual Basic for Applications) function. Here is a sample code that will help you extract the characters after the “_” and before two numbers:

Open Excel and press Alt + F11 to open the VBA editor.

Insert a new module by selecting Insert > Module.

Paste the following code into the module:

Function ExtractText(ByVal text As String) As String

Dim regex As Object

Set regex = CreateObject("VBScript.RegExp")

regex.Pattern = "_([A-Za-z]+)\d{2}"

regex.Global = False

If regex.Test(text) Then

ExtractText = regex.Execute(text)(0).SubMatches(0)

Else

ExtractText = ""

End If

End Function

Close the VBA editor and return to Excel.

Use the ExtractText function in the cell where you want to extract the text. For example, if your text is in cell A1, type in another cell:

=ExtractText(A1).

This code will extract the characters after the “_” and before the two numbers. For example, for the text “10001_PRV01”, the result would be “PRV”.

You can find the examples and detailed instructions here: How to extract substrings in Excel using regular expressions (Regex).

I recommend paying attention to the Regex tool. You can find, extract, compare, delete, or replace strings that match the regular expression pattern you enter. You don't need to install any VBA code.

It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.

I'm not sure if mid is the best way to go about things, but I have to do a ton of formatting with phone numbers and am wondering how to accomplish the following

A, B, C

1|(321) 456-7894, (321) 555-6666,_

2| , (321) 123-4567,_

3|(321) 777-8888, ,_

What I want to accomplish is that if A1 has data, make c1 in the format (using line one as an example) 3214567894

If A is blank, the C would be B formatted as 3211234567. Right now I can use if(A1>0,A1,B2) to bring over the formatted phone number (321) 456-7894 for line 1 into col C, but I don't know how to then make Col C into 3214567894 format. It would be cool if this could all be done in a single step/col but using more than one is fine as well. Anyone have any thoughts on this? (my only thought is to use 3 mid cols to break the data into |321|456|7894| and then concatenate those three fields into 1 but I'm HOPING there is an easier way)

Hello Chris!

I’m sorry but your description doesn’t give me a complete understanding of your task. If A1 contains a number formatted as a phone number, you can get the first 3 digits of that number and concatenate them with the text string “1234567”. Using a mathematical operation, you can convert this text to a number.For example:

=IF(A1>0,A1,--LEFT(B1,3)&"1234567")

If text is written in the cells, use these instructions to extract digits from the text: How to extract number from string in Excel. The formula might look like this:

=IF(A1<>"",A1,--(LEFT(CONCAT(IF(ISNUMBER(--MID(B1,ROW($1:$40),1)), MID(B1,ROW($1:$40),1),"")),3))&"1234567")

Hi Team,

Need your help in excel

I have business days as calender as BD+01,BD+02,BD+03 .........BD+22 For a month in b4,c4,d4........

In column a I have list of days when deliverables are there..

For eg., I have one report which gets produced on different days BD+01,BD+02 BD+07

I want a formula to lookup b4, c4,d4,till BD+22 cell reference to in the deliverable date and return yes or no..

So for BD+01,BD+02 BD+07 I SHOULD GET answer in respective days as yes or no.

I have around 30 reports with different days as deliverable please help

Hi! Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.

if i need to collect the same word eg and from multiple sentences but it isn't in the same place in each sentence, what formula can I use

Hi! I’m sorry but your task is not entirely clear to me.

Please describe your problem in more detail. Include an example of the source data and the result you want to get.

Maybe this article will be helpful: Excel IF statement for partial text match (wildcard).

Hi, I would like to extract a text that starts with SRM, the issue that I am having is when the SRM has some letters without space on it.

For example:

conditions to govern jsndjsdSRM_011288 sjjdjsjd

I need to extract from SRM everything else after that

I was using =TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),MAX(1,FIND("SRM",SUBSTITUTE(A1," ",REPT(" ",99)))-50),99))

but the result is jsndjsdSRM_011288

What formula can I use to get only SRM and the info after that?

Thanks!

Hello Mariana!

Determine the position of "SRM" in the text using SEARCH function. Extract all characters from this position using MID function.

=MID(A1,SEARCH("SRM",A1),100)

You can also use this instruction: Extract text after a certain character. The formula might look like this:

=RIGHT(A1,LEN(A1)-SEARCH("SRM",A1)+1)

Hi,

Kindly provide me formula to extract "257681100152" from data 8102979835#2080344###681.00#1 UNIT#03/2024#257681100152#ASSY. ORVM RH BS1/2/3/4/6

Hi! If your data has a common pattern, you can use Text to Columns tool to extract part of text between the "#" delimiters. The following tutorial should help: How to split cells in Excel: Text to Columns, Flash Fill and formulas.

You can also use TEXTSPLIT function to split the text. Then extract the penultimate part of text using CHOOSECOLS function. For example:

=CHOOSECOLS(TEXTSPLIT(A1,"#"),-2)

I have the following formula and I would like to colon at the end of the results. How would I go about doing it?

=MID(A2,FIND(",",A2)+1,LEN(A2))

Hi! Here is the article that may be helpful to you: Excel CONCATENATE function to combine strings, cells, columns. You can combine two text strings:

=MID(A2,FIND(",",A2)+1,LEN(A2))&":"

How can I combine these 2 formulas:

To substitute the first comma, and any double spaces for a semi colon on data in cells of Column D I have entered this formula on Column E: =SUBSTITUTE(SUBSTITUTE(D3,",",";",1)," ",";"), and then separately on Column F, I entered the following MID formula to extract a number between a "(" and" ;" from Column E: =IFERROR(VALUE(MID(E3,SEARCH("(",E3)+1,SEARCH(";",E3)-SEARCH("(",E3)-1)),"None")

thank you in advance.

Hi! Without an example of your data, I cannot check your formulas. But if you want to combine the formulas, replace the reference to cell E3 in the second formula with the first formula written in this cell.

=IFERROR(VALUE(MID(SUBSTITUTE(SUBSTITUTE(D3,",",";",1)," ",";"), SEARCH("(",SUBSTITUTE(SUBSTITUTE(D3,",",";",1)," ",";"))+1, SEARCH(";",SUBSTITUTE(SUBSTITUTE(D3,",",";",1)," ",";")) - SEARCH("(",SUBSTITUTE(SUBSTITUTE(D3,",",";",1)," ",";"))-1)), "None")

Hi!

Just asking whether we can use above to extract data from a formula.

Example: =(122014-121304)*1000

Need to extract the "122014" from this.

Please help.

Hi! You can use the recommendations and formulas from the article above, and you can also take a look at these instructions: How to extract text between two characters or words. If your formula is written as text, you can extract the first number like this:

=MID(A1, SEARCH("(", A1)+1, SEARCH("-", A1) - SEARCH("(", A1) -1)

If it is a regular Excel formula, you can convert it to text string using the FORMULATEXT function.

Hi Sir,

It is possible excel can read the YEAR in different format?

Example (Same report number but different year format)

Report 1 - LONDON/123/2024

Report 2 - LONDON/123/24

Hi! I can assume that you want to extract the year from the text. In your examples, the year is the last word, which is separated by the "/" separator. If I understand your task correctly, the following tutorial should help: How to extract word from string in Excel: first, last, Nth, and more. Based on the information given, the formula could be as follows:

=TRIM(RIGHT(SUBSTITUTE(A1, "/", REPT(" ", LEN(A1))), LEN(A1)))

You can also split text using the TEXTSPLIT function. The CHOOSECOLS function can get the third word:

=CHOOSECOLS(TEXTSPLIT(A1,"/"),3)

You can solve the problem without using formulas. Pay attention to the Split Text tool. If text is separated by commas, spaces, dashes, or other characters, you can use this tool to create multiple columns or rows from a single cell. The tool is included in the Ultimate Suite for Excel and can be used in a free trial to see how it works.

Tq Sir for your reply. I want to excel detect the duplication of the report number between 2 reports but different year format. (2024/24)

Hi! I recommend paying attention to the tool Fuzzy Duplicate Finder. The add-in looks for partial duplicates and typos in Excel files that differ by 1 to 10 characters, and detects characters that are missing, have extra characters, or are misspelled. This is done without using formulas.

The tool is included in the Ultimate Suite for Excel and can be used in a free trial to see how it works.

Hi Alexander,

In Cell A1, A2, A3 i have following data

ABC Travel Company

XYZ Tours Comapny

A & B Travels Company

I use following Formula

=TRIM(LEFT(a1,FIND("^",SUBSTITUTE(a1," ","^",2)&"^"))) to extract first two words from the string of words. but i want the formula in such a way that if the second word is "and" or "&" than it should extract first three words otherwise only first two words

Hi! Extract the second word from the text string and use it in the IF OR statement. How to extract the second word from a text string is described in detail in this section: How to extract Nth word from a text string. Depending on the result of the condition, extract two or three words.

Based on the information provided, the formula could look like this:

=IF(OR(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))), (2-1)*LEN(A2)+1, LEN(A2)))="&", TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))), (2-1)*LEN(A2)+1, LEN(A2)))="and"), TRIM(LEFT(A2,FIND("^",SUBSTITUTE(A2," ","^",3)&"^"))),TRIM(LEFT(A2,FIND("^",SUBSTITUTE(A2," ","^",2)&"^"))))

Hi Sir,

How to extract word only - remove ID

LEAD R2194705 VICTOR RICHARD

Hello! Determine the position of the first and second spaces with the SEARCH function. Use the LEFT function to extract the first word. Use the MID function to extract the text after the second space. Combine text strings with the & operator. The formula might look like this:

=LEFT(A1, SEARCH(" ",A1)-1) & MID(A1,SEARCH(" ",A1, SEARCH(" ",A1)+1),50)

You can also find useful information in these articles: How to remove characters/text from string in Excel and How to extract word from string in Excel: first, last, Nth, and more.

Thank you, Sir, for your guidance.

Hi Sir,

I want to extract ID number only from multiple row. Use this fx =MID(Sheet1!F5,6,8) but failed for the 2nd row because the wording not same. Please help

Insp G0123211 JOHN BIN LEE

Sarjan R0123211 JOHN BIN LEE

Hello! Find the position of the first space using the SEARCH function. Then extract the next 8 characters from the text. I believe the following formula will help you solve your task:

=MID(A1,SEARCH(" ",A1)+1,8)

Great. Thank You Sir

Hi, I'm really struggling to pull a MID with variable number length. My goal was to extract the second set of numbers in a cell and then return the value as a number to allow for that column to be calculated. It works with those cells where the second numbers are exactly the same char length as specified, but I can't make it work with smaller numbers.

This is the content of the cell. I want just the message response number:

"Message Response : 200

Recipients count : 52

BT Response : 0 001 OK"

I thought of using the next line as the closing delimiter, but it just returns a #value! and the calculation isn't working.

My syntax is: =VALUE(MID(I84,SEARCH("Recipients count :",I84)+1,SEARCH("BT",I84)))

Never mind. I was trying to be too fancy. =NUMBERVALUE((MID(I2,43,3))) did what I needed.

Hi I am trying to convert a deck of cards into a value so that Ace=14, King=13, Queen=12 and so forth. But I only want do type two letters for each card into a cell, to get the outcome into another table. So if I put into the input table "AKQ" for Ace, king and a queen I want to get into the output table the value "39". I need up to 8 cards to be able to put into the formula.

Hi! To get a value depending on multiple conditions, try any of the methods described in this article: Excel Nested IF statement: examples, best practices and alternatives.

How would I split the following text into two columns, the entire line is in one cell?

1.d4 Nf6 2.c4 g6 3.Nf3 Bg7 4.e3 O-O 5.Be2 d6 6.Nc3 e5 7.dxe5 dxe5 8.Nxe5 Qxd1+ 9.Bxd1 Re8 10.Nd3 Ne4 11.Nxe4 Rxe4 12.Bf3 Rxc4 13.Bd5 Rh4 14.f3 Nc6 15.O-O Nd4 16.exd4 Rxd4 17.Re1 Bf5 18.Bxb7 Rb8 19.Nf2

Column A, Column B

1.d4 Nf6

2.c4 g6

...

19.Nf2

The final number could be more or less than 19.

Hi! If I understand your task correctly, the following formula should work for you:

=WRAPROWS(TEXTSPLIT(A1," "),2)

You can find the examples and detailed instructions here: Convert column / row to range in Excel: WRAPCOLS & WRAPROWS functions and TEXTSPLIT function in Excel: split text strings by delimiter.

Harmony Nirvana Country Flat No 1701 Unitech Harmony Sector VI

From the above address I want to extract the value (1701). Which formula will work for it?

Hi! We have a special tutorial that can help to solve your problem: How to extract number from string in Excel.