How to use MID function in Excel for pull characters

MID function in Excel is intended to select a substring from a string of text passed as the first argument, and returns the required number of characters starting from the specified position.

Examples of using the MID function in Excel

One character in single-byte-encoded languages corresponds to 1 byte. When working with such languages, the results of the functions MID and MIDB (returns a substring from a string based on the number of specified bytes) do not differ. If a two-byte language is used on the computer, each character will be counted as two characters when using MIDB. The two-byte languages are Korean, Japanese, and Chinese.

How to divide the text into several cells in columns in Excel?

Example 1. The table column contains dates recorded as text strings. Record separately in adjacent columns the number of the day, month and year, isolated from the presented dates.

View source data table:

To fill in the day number, use the following formula:

Argument Description:

A2:A10 - a range of cells with a textual representation of the dates from which the numbers of the days will be highlighted;
1 - the number of the initial position of the character of the extracted substring (the first character in the original string);
2 - the number of the last position of the character of the extracted substring.

In a similar way, we select the month numbers and years to fill in the corresponding columns, taking into account that the month number starts with the 4th character in each row and the year from the 7th. We use the following formulas:

=MID(A2:A10,4,2)

=MID(A2:A10,7,4)

View of the completed data table:

Thus, we were able to cut the text into cells of column A. It was possible to divide each date separately into several cells by columns: day, month, and year.

How to cut part of cell text in Excel?

Example 2. A text column with the name and brand of goods is stored in a table column. Split the existing lines into substrings with the name and brand, respectively, and write the values in the corresponding columns of the table.

View of data table:

To fill the column "Name" we use the following formula:

=MID(A2,1,FIND("",A2))

FIND function returns the position number of the space character "" in the line being scanned, which is taken as an argument to the number_ characters of the MID function. As a result of the calculations we get:

To fill the “Mark” column, use the following array formula:

=MID(A2:A8,FIND("",A2:A8)+1,100)

FIND function returns the position of the space character. A unit is added to the received number to find the position of the first character of the brand name of the product. The final value is used as an argument to the Start_num of the MID function. For simplicity, instead of searching for the last position number (for example, using the LEN function), the number 100 is indicated, which in this example is guaranteed to exceed the number of characters in the original line.

As a result of the calculations we get:

How to calculate age by date of birth in Excel?

Example 3. The table contains information about employees in the columns name and date of birth. Create a column in which the surname of the employee and his age in the format “Jon - 27” will be displayed.

View source table:

To return a string with the last name and current age, use the following formula:

MID function returns part of a string to a space character, whose position is determined by the FIND function. To find the age of the employee, use the DATEDIF function, the resulting value of which is truncated to the nearest smaller integer to get the number of full years. The TEXT function converts the resulting value to a text string.

For connection (concatenation) of the obtained strings the symbols “&” are used. As a result of the calculations we get:

Features of using the MID function in Excel

The function has the following syntax entry:

=MID(Text,Start_num,Num_chars)

Argument Description:

Text – a required argument that accepts a cell reference with a text or a text string enclosed in quotes from which a substring of a certain length will be extracted starting from the specified position of the first character;
Start_num – is a required argument that accepts integers from the range from 1 to N, where N is the length of the string from which you want to extract a substring of a given size. The initial position of the character in the string corresponds to the number 1. If this argument takes a fractional number from the range of valid values, the fractional part will be truncated;
Num_chars – is a required argument that takes a value from a range of non-negative numbers, which characterizes the length in characters of the returned substring. If the argument is the number 0 (zero), the MID function returns an empty string. If the argument is a number greater than the number of characters in the string, the entire part of the string starting with the specified second position argument will be returned. In fractional numbers used as the argument, the fractional part is truncated.

MIDB function has a similar syntax:

=MIDB(Text,Start_num,Num_bytes)

It has a single argument:

Num_bytes is a required argument that accepts integers from the range from 1 to N, where N is the bytes in the source string, characterizing the number of bytes in the returned substring.

Download examples MID function for splitting text in Excel

Notes:

MID function will return an empty string if, as an argument, the Start_num was passed value exceeding the number of characters in the source string.
If the value 1 was passed as an argument for Start_num, and Num_chars argument is determined by a number that is equal to or greater than the total number of characters in the source line, the MID function returns the entire line.
If the argument Start_num was specified by a number from the range of negative numbers or 0 (by zero), the MID function will return the error code #VALUE!.
If the Num_chars argument is a negative number, the result of the MID function is the error code #VALUE!.