How to Use MID Function to End of String in Excel

This guide will discuss how to use the MID function to end of string in Excel.

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

  • When the inputted num_chars argument is less than 0, the function will return a #VALUE! error. 
  • When the inputted start_num argument is less than 1, the function will return a #VALUE! error. 
  • If the num_chars is left blank, the argument defaults to 1.
  • The MID function can extract text from numeric values. But, the returned value will be text.
  • The function does not support number formatting. 

Excel is an excellent tool for manipulating data. Since it contains several functions and tools, we can use different functions for different purposes and situations. 

So the MID function in Excel is used to extract a specific number of characters from a text string beginning from the starting position on the left side of the selected string. 

Furthermore, we can use the MID function to extract the characters in the middle of a text string based on a specific starting character to the end of the string. 

But, we would need to use the MID function and the LEN function to perform this specific task. With a formula combining the MID function and LEN function, we can return every character, starting from a specific character to the last character of a selected string. 

Let’s take a sample scenario wherein we need to use the MID function to end of a string in Excel. 

Suppose you have a list of data containing the employee ID and name of the employee. And you want to extract the employee names from the list. 

Since the name of the employee starts in the middle of the text string and all the way to the end of the string, we will use a combination of the MID function and the LEN function. 

Great! Before we dive into a real example, let’s first discuss how to write the MID function in Excel. 

 

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 can activate any function in Excel. 
  • MID() is our MID function. And this function returns the characters from the middle of the selected text string based on the starting position and length. 
  • text is a required argument. And this refers to the text string which we want to extract the characters from. So this is usually a cell reference containing the text string. 
  • start_num is another required argument. And it refers to the position of the first character we want to extract. So the first character in a text is 1.
  • num_chars is also a required argument. So this refers to the specific characters to return from the selected text. 

Now let’s learn how to use the LEN function in Excel.

 

The Anatomy of the LEN Function

The syntax or the way we write the LEN function is as follows

=LEN(text)

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

  • = the equal sign is how we begin any function in Excel.
  • LEN() refers to our LEN function. And this function will return the number of characters in a text string. 
  • text is the only required argument. So this refers to the text string whose length we want to find. Additionally, spaces in the text count as characters. 

Amazing! Now let’s move on and explain a real example of using the MID function to end of string in Excel.

 

A Real Example of Using the TOROW Function in Excel

Let’s say we have a data set containing a list of employee IDs and names. And we want to extract only the names of the employee from the list. So our initial data set would look like this:

Initial data set

 

In this case, each data contains the employee ID at the beginning then, immediately followed by the name of the employee. So the format of the list is IDName. 

Since we want to extract the employee’s name, which is found in the middle of the text string to the end of the string, we can utilize the MID function and the LEN function. 

So the MID function extracts a specific number of characters from the middle of the text string. Then, we will combine it with the LEN function which will extract every character starting from a specified character to the last character of the text string. 

Essentially, the LEN function is used to find the length of the string. So the formula will extract the characters starting from the specified character to the end based on the character representing the entire length of the string. 

For example, we have one data as 101Kate. So we want to extract Kate from that specific text string. So we will tell the MID function to extract from the 4th character. 

Then, the LEN function will find the length of the string and use that to extract the characters to the end, starting from the 4th character. So our final data set would look like this:

Final data set

 

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

Finally, we can start to explain the process of how to use the MID function to end of string in Excel. 

 

How to Use MID Function to End of String in Excel

In this section, we will explain the step-by-step process of how to use the MID function to end of a string in Excel. Furthermore, each step contains pictures and detailed instructions to guide you along the way. 

1. Firstly, we need to create a new column to input the extracted characters. Then, we can begin the formula with an equal sign and the MID function. So simply type in the formula “=MID(B2, 4, LEN(B2))”. Lastly, press the Enter key to return the result.

MID Function to End of String in Excel

 

2. Secondly, we will drag down the Fill Handle tool to copy and apply the formula to the rest of the cells. And this way, we can also extract the employee name from the rest of the text string.

MID Function to End of String in Excel

 

3. And tada! We have successfully used the MID function to end of the string in Excel.

MID Function to End of String in Excel

 

And that’s pretty much it! We have successfully explained how to use the MID function to end of string in Excel. Now you can apply this learning whenever you need to extract characters from the middle to the end of a specific text string. 

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