Function to get the middle character in Excel What is that? How to get the middle 2 characters in Excel how? Let's find out with Quantrimang.com!
MID is one of several text functions that Microsoft Excel provides for processing text strings. At the most basic level, it is used to extract a substring in the middle of a text string or help you get the middle 2 characters in Excel. If you find it useful, please join Quantrimang.com to learn how to use the MID function in Microsoft Excel!
In Excel, the 3 functions LEFT, RIGHT function and MID function are important and basic Excel functions to process characters in the character string on the data table. With the LEFT function, when entering the function, Excel will take the characters on the left. The RIGHT function will take the characters to the right. The remaining function is the MID function that will take the middle character string equivalent to the user's request. The implementation of these functions is relatively similar, you need to enter the command, then select the number of characters you want to get in the data table and you're done. The article below will guide you how to use the MID function in Excel to get the middle character.
We will proceed to retrieve the character string with the statistics table below. The requirement of that table is to filter out the major code from the student's class name. Corresponding are 2 characters starting from the 5th character in the class name.
The MID function will cut out n characters in the character string from the original position m.
Step 1:
The implementation syntax of the MID function will be =MID(text,m,n).
In there:
- text: character string to cut.
- m: Starting position of the character string to be cut.
- n: Number of characters to cut from the character string.
In cell D3, we will enter the formula =MID(C3,5,2) then press Enter. That means we will take 2 characters starting from the 5th character in cell C3 and enter the resulting characters in cell D3.
Step 2:
The result will be as shown below. You have filtered the major code with 2 characters in the student's class name.
To do this with the remaining cells, we just need to drag down the remaining cells to get the same result.
In addition, the LEFT and RIGHT functions are also two functions that take characters corresponding to the left and right sides in the character string. You do the same as with the MID function.
Syntax of LEFT function is =LEFT(text,n).
Where text is the character string to get and n is the number of characters to cut. If there is no n parameter, Excel will automatically take from the first value in the series.
For example, I need the class number in the class name with 4 characters from the first character. You will enter the function =LEFT(C3,4).
The final result will be as shown below.
Syntax RIGHT function is =RIGHT(text,n).
For example, I need to get 5 characters from the industry code in the class name. The input formula will be =RIGHT(C3,5). The results also appear as shown below.
Things to remember when using the MID function in Excel
1. Error #VALUE!
occurs if argument is provided [num_chars]
less than 0 or argument start_num
less than 1.
2. Dates are saved in Excel as numbers, and only cell formatting causes them to show up as dates in the spreadsheet. Therefore, if you use the MID function on a date, it will return the middle characters of the number representing the date.
For example, January 1, 1980 is represented by the number 29221, so applying this function to a cell containing January 1, 1980 will give the result as the value of 922.
If you want to use that data for dates, you need to combine the MID function with the DAY, DATE, MONTH or YEAR function in Excel. If not, you can convert date cells to text using Excel's Text to Column tool.
3. The MID function always returns a text string, even if the extracted substring only contains numbers. This can be important if you want to use the results of the MID formula in another calculation. To convert the output to a number, use MID in combination with the VALUE function.
4. If start_num greater than the total length of the original text, the MID formula in Excel returns an empty string (“”).
5. If num_chars less than 0 (negative number), the Mid formula returns the value #VALUE. If num_chars is 0, it results in an empty string (empty cell).
Above is a tutorial on how to use the MID function, the function to retrieve the middle character string according to user requirements in Excel. Accordingly, the MID function will retrieve the middle characters in the character string of the data table. In addition, how to use the LEFT function to get the character string on the left and the RIGHT function to get the character string on the right in Excel also follow the instructions above.
Wishing you success!