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:

Initial data set

 

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:

Final output

 

You can make your own copy of the spreadsheet above using the link attached below. 

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.

Use MID Function for Variable Length in Excel

 

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

Copying the formula

 

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

Use MID Function for Variable Length in Excel

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.

Get emails from us about Google Sheets.

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.

 

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like