How to Use MID Function for Variable Length in Excel

This guide will explain how to use the `MID` function for variable length in Excel.

The rules for using the `MID` function in Excel are the following:

• The `MID` function will extract text from numeric values, but the result will be a text.
• The `MID` function will always return a text string.
• If start_num is greater than the overall length of the selected text, the `MID` function will return an empty string.
• If start_num is less than 1, the `MID` function will return a #VALUE! error.
• If num_chars is less than 0 or is a negative number, the `MID` function will return a #VALUE! error.
• If num_chars is equal to 0, the MID function will return an empty string.
• The `MID` function will return a substring starting from start_num and up to the last character when the sum of the start_num and num_chars is greater than the total length of the original string.

Excel has several text functions for manipulating text strings. And one of these is the `MID` function. So the `MID` function extracts a substring from the middle of a selected text string.

Furthermore, the `MID` function will return a specific number of characters from the text string based on the given starting position on the left side of the text string.

However, sometimes, we want to extract middle characters given a specific starting and ending character. Since the `MID` function only returns characters from the middle based on a starting position and length, we need to combine another function with being able to perform it.

For instance, we can combine the `MID` function with the `FIND` function to extract middle characters from a text string based on a starting position and ending character.

Let’s take a sample scenario to understand better.

Suppose we have a list of website URLs. And we were tasked to extract the website name based on the given data. In this case, the website name is in the middle of the text string. But, each website URL has different lengths. So individually creating a formula for each one would be inefficient.

And this is where we can combine the `MID` function and the `FIND` function to extract the website name despite the different lengths of each data. Without the `FIND` function, the `MID` function would not be able to extract the middle characters due to the difference in text length.

Great! Before we move on to a real example of how to use `MID` function for variable length in Excel, let’s first learn the syntax of the `MID` function.

The Anatomy of the MID Function

The syntax or the way we write the `MID` function is as follows:

`=MID(text, start_num, num_chars)`

Let’s take apart this formula and understand what each term means:

• = the equal sign is how we begin any function in Excel.
• MID() is our `MID` function. And this function returns the middle characters of a selected text based on a given starting position and length.
• text is a required argument. And this refers to the text string we want to extract the middle characters from.
• start_num is another required argument. So it refers to the position of the first character we want to extract.
• num_chars is also a required argument. And this will specify how many characters the function will return from the selected text.

Now let’s move on to the syntax of the `FIND` function.

The Anatomy of the FIND Function

The syntax or the way we write the `FIND` function is as follows:

`=FIND(find_text,within_text, [start_num])`

Let’s take apart this formula and understand what each term means:

• = the equal sign is how to start any function in Excel.
• FIND() refers to our `FIND` function. And this function will return the starting position of one text string within another text string. Additionally, this function is case-sensitive.
• find_text is a required argument. So it refers to the text we want to find. And we need to use double quotes to match the first character in within_text. Furthermore, wildcard characters are not supported.
• within_text is another required argument. And this refers to the text we want to find.
• start_num is an optional argument. And it refers to the specific character at which we want to start the search. So the first character in within_text is character number 1. If this is omitted, start_num is equal to 1 by default.

Awesome! Now let’s dive into a real example of how to use `MID` function for variable length in Excel.

A Real Example of Using the MID Function for Variable Length in Excel

Let’s say we have a data set containing a list of different website URLs. And we were asked to extract the website name from each URL. So our initial data set would look like this:

However, we cannot extract the website names by using the `MID` function only. Since the URLs have different lengths, the `MID` function cannot perform the action. So we need to combine the `MID` function with the `FIND` function to extract the website names.

In this case, the website name is between the “//” and “.com” characters. With the help of the `FIND` function, we can extract the middle characters from the URL despite the length of each website being different.

So our final output would look like this:

Finally, let’s discuss the process on how to use `MID` function for variable length in Excel.

How to Use the MID Function for Variable Length in Excel

In this section, we will discuss the step-by-step process of how to use the `MID` function for variable length in Excel.

1. Firstly, we must select a cell to input the result. In this case, we will be extracting the website names from the URLs. So simply type in the formula “=MID(B2,FIND(“//”,B2)+2,FIND(“.com”,B2,10)-FIND(“//”,B2)-2)”. Lastly, press the Enter key to return the result.

2. Secondly, we will drag down the formula to autofill the other cells and extract the website name.

3. And tada! We have successfully extracted the middle characters from the website URLs despite the different text string lengths.

And that’s pretty much it! We have discussed how to use `MID` function for variable length in Excel. Now you can apply this to your work whenever you need to extract the middle characters from a list of text strings containing different lengths.

Are you interested in learning more about what Excel can do? You can now use the `MID` function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

How to Add a Running Total to a Pivot Table in Excel

This guide will explain how to add a running total to a Pivot Table in Excel. Running totals…

How to Use SUMIFS on Multiple Columns with Several Criteria in Excel

This guide will explain how to find the sum of multiple columns given one or more criteria. We’ll…

How to Create a Covariance Matrix in Excel

This guide will explain how to create a covariance matrix in Excel. The covariance matrix is a useful…

How to Merge Two Files in Excel

This guide will explain several methods you can use to merge two files in Excel. When working with…