Microsoft Excel is one of the most commonly used tools in the business world. It can be used for everything from simple calculations to complex data analysis. One of the most useful functions in Excel is the mid function. In this post, we will show you how to use the mid function in Microsoft Excel.
The mid function in Microsoft Excel returns the specific number of characters from the middle of a string based on the starting position and the number of characters we specify. Let’s see how the mid function works in Microsoft Excel.
Here we have some sample data in column A, and we would expect the result in column B. So we begin with typing mid function cell P A. We type equal MIT and then hit cat go to compute the function. Here we can see that it takes three arguments which are text starting number and number of characters.
The first argument is a string from which you want to expect the substring. The second is the starting number. It is the position of the first character from where you want the extraction to begin or the location of the first character which you want the function to return. Then the number of characters you want to extract from the string.
Let’s select A A as a cell reference which contains a text input coma. Now in the second argument, we would tell Excel to start extracting the number from the first character onwards. So we type 1 here, and then we tell Excel only to expect 5 characters. If we type 5 here and hit enter, we see that the function returns “Excel” from the string “excel.” Helpful.
Now we type mid function here in cell B9, and we select A9 in the first argument. We enter 7 in the second argument and four in the third argument as the number of characters. Before we hit enter, let’s see what the mid function should return. We have said that it should start extracting the number from the 7th position onwards. So in this case, if we look at the string which starts with E, which is the first character, and then X, say E L space than H, so H is at the seventh position here, and the second argument stops here. Now the third argument will look for four numbers of characters from H onwards. So in this case, it is going to be till P, and the function returns “help.”
Let’s type wait here, and we select A10-10 coma. Now we type 11, and we want to expect only one character, so type one. If you need to expect only one character, you have to type one only in the number of characters. Now in B11, we enter the function make and select 11 as a cell reference. Then we enter 25 as a starting position, and let’s say we give 2 in the number of characters.
If we have noticed, we type 25, which seems to be greater than the total length of the original string, and the function returns MPD self because it starts from the position which is the end of the string, and then it has to return the given number of characters, which is not from there, and hence it returns plan.
What if we give the starting position as a negative number? So let’s say and here we give minus 1 in the starting position and let’s expect 1 character only. The function would return value error because mid function does not allow any negative number or zero as a starting number, and the same applies to the number of characters as it does not allow a negative number. When we hit enter, we get the same value error.
But let’s see what happens if we give 0 as the number of characters. We enter mid function in B13, and we select A13. Then we say Excel to start expecting from the 11th position, and we type 0 in the third argument. The function returns input itself because we are telling Excel to start looking from the 11th position but not to return any character, and hence the function returns.
What if the number of characters specified in the last argument is larger than the length of the string? In that situation, the function would return the entire text. We type your make function be 14, and we tell Excel to start looking from the first character onwards and expect 15 numbers of characters, which seems to be greater than the length of the string. So we see the function returns the actual text which is in A14.
This is how we can use mid function in different ways. We hope you found this lesson informative. We shall meet again in our next lesson. Till then, happy learning and have a nice day.