MID function for splitting text into parts in Excel – an example of use

PSTR – a fairly popular function that is used by both professional Excel users and beginners. But also the second category of people may not understand in what cases it is appropriate to apply it. And the purpose of this function is very simple – it helps to pull out a small substring from one big one. But at the same time, the opportunities that it provides are much greater. In particular, it is used to separate strings. Let’s take a closer look at this magical Excel feature.

MID function – detailed description

So we realized that PSTR is a function. which is used in order to get some specific one from one piece of text. But it is somewhat different from the Find and Replace function, which can be implemented both through the Excel menu and through formulas. It returns a string starting with a specific character.

The syntax is extremely simple:

=MID(text; start_position; number_characters)

Let’s understand in more detail what arguments are used in this function:

1. Text. The contents of a cell can be values ​​in different formats, both links and text strings. However, a cell of any other format can also be used. For example, you can get the month from a date in this way. To do this, in the “Text” field, specify a link to a cell with a date. It is important to make sure that it has been previously converted to text format.
2. Initial position. This is the numeric value of the character from which the extraction of the substring begins.
3. Number of characters. This is the number of characters to be extracted from the string. If you need to extract the date, then it always has a size of 10 characters. 

Each argument must be specified. Suppose we have entered the required data. And we got the following result. 

=MID(A1)

STRB function (new function)

This new function performs almost the same operations, only the result is returned in bytes instead of characters. 

What is the difference between PSTR and PSTRB?

The question arises: what is the fundamental difference? The fact is that some characters are considered double-byte, and a number of them are single-byte. In the case of the function PSTRB those characters that occupy two bytes in memory are always counted as 2. First of all, Japanese, Chinese, and Korean characters are double-byte characters.

PSTR function in English

If a person uses the English version of Excel, he needs to know how this function is written in English. The function is responsible for extracting a certain number of characters from a string. MID. Accordingly, if it is necessary to focus on the number of bytes, then the formula with MIDB is used.

MID function and VBA in Excel

Using the VBA programming language, you can write subroutines that perform a series of actions automatically. This environment also has an alternative to the function PSTR. Let’s take an example. 

Range(«A2»).Value = Mid(Range(«A1»), 17, 10)

Common Mistakes in Using the MID Function

When using the MID function, errors are possible if the syntax is incorrectly followed or some fundamental rules are violated:

1. You cannot write a negative value as an argument to this function. In this case, the #VALUE error will be issued.
2. It is important to ensure that the starting position is not a larger number than the length of the searched string. Otherwise, an empty value will be returned.

How to cut text from a cell in excel

And now let’s start a review of practical examples of how it is possible to use the function PSTR.

Let’s say we have a spreadsheet that contains information about what products are sold, specifically: the brand and name of a particular product. And we are faced with the task of separating the name and brand and placing this information in appropriate columns.

The table itself looks like this.

To fill in the column with the names of the goods, you need to insert the following formula in the top line under the heading.

=MID(A2,FIND(” “,A1))

In our example, the function TO FIND is used to determine the ordinal position where there is only one space character. As an argument to the find function, respectively, we write a space enclosed in quotes. 

After that we get the following result.

If we are faced with the task of filling in a column with information about the brand of goods sold, we must use the array formula of such a plan.

=ПСТР(A2:A8;НАЙТИ(» «;A2:A8)+1;100)

Here again we see that we used the function TO FIND, with which in this example we are looking for the initial position containing a space. We also added one to the content of the argument to move the program’s gaze to the first character of the product brand. To simplify the task of finding the last character, we simply decided to write the number 100, which is guaranteed to be greater than the length of the string. 

So can you.

After the program performs all the necessary calculations, we get the following table.

How to calculate age by date of birth in excel

Now let’s give another example that employees who often use Excel encounter quite often. We have a database containing three columns: Last name, first name, patronymic, and date of birth. And we are faced with the task of determining how old a person is at the moment, using PSTR.

This is how the table we will work with looks like.

In order to return a string with the last name and age in this case, it is necessary to write the following text into the formula bar (in the appropriate places, the function arguments must be replaced with your own).

u2d PSTR (A1; 2; FIND (” “; A2)) &” – ​​”& RAZDAT (BXNUMX; TODAY (); “Y”)

This function first determines the part of the string that is contained before the space character. This is done using the function TO FIND similar to the examples above. Next, using the function RAZNDAT we determine the number of full years. In our case, we automatically cut off the section of days that remains until the next birthday in this year. That is why this function can always indicate the correct age. 

After that, the entire calculation result is converted into text format. Next, you need to connect the lines together. This can be done with the & character. After all the necessary actions are completed, such a resulting table will appear.

Several features of using PSTR in Excel

And now let’s go through the theory a bit after the function has been clearly demonstrated. So we know that the function PSTR has the following syntax:

=MID(text,start_position,number_of_characters)

And once again, let’s look at each argument in more depth and describe some of the features through the prism of this function.

1. Text. This is a required argument that must be passed to the function. Represents either a reference to a cell, or a string itself, from which you want to extract the required information. It is important to note that in the latter case it must be wrapped in quotation marks. Regardless of the format of the string, certain information will be retrieved from it. Which one is determined by the following arguments. 
2. Starting position. This argument is also required. Its task is to set the starting point of reference. Is a necessarily integer that refers to positive numbers. That is, the minimum value is 1. If it turns out that a fractional part was passed to the argument, it is cut off.
3. Number of characters. And this argument is mandatory. Thus, all arguments used in this function must be used. There is such a nuance here. If it turns out that a number was passed into it that is greater than the length of the string, then the entire string is returned. 

It is also important to understand that the syntax used for the MTRP function is very similar, with the only exception that instead of the number of characters after the start position, the number of bytes starting from that point is given.

As a reminder, the syntax is as follows:

=PSTRB(text,start_position,number_bytes)

As the saying goes, repetition is the mother of learning. So let’s sum up a little intermediate results:

1. If at the place of the starting position you specify a value that is larger than the original string, then after all the operations performed by the function PSTR will return an empty value.
2. If you use the unit as the starting position, and specify the number of characters that will be greater than or equal to the string, then the entire contents of this string will be displayed as a total. Thus, you can use this function as an alternative, albeit not so convenient, cell reference. In Excel, situations are different, so sometimes you have to get out of any situation, including in such bizarre ways. 
3. A #VALUE! error will be returned if a negative value is used as the starting position. The same applies to the situation if the argument with the number of characters is given by a negative value. It is important to remember forever, you cannot use a zero or negative value in this argument. 

Examples of using the MID function

And now let’s give one example of how you can use the MID function in practice. First you need to understand that the number of bytes that one character occupies depends on the languages. If the encoding in the language is one-byte, then one character occupies exactly one byte. In this case, it does not matter which formula to use: PSTR or PSTRB.

But maybe a two-byte language from the list above. In this case, there is a difference which function to use. 

One possible way to use the function PSTR, is the division of text into several cells in columns. Suppose we have such a table that contains dates divided into columns date, day, month, year. We are faced with the task of automatically, using Excel, to enter the elements in the appropriate columns. 

The table itself looks like this:

In this case, you need to apply the array formula, namely this one.

What arguments were used in this case?

1. A2:A10. This lists a set of cells that represent dates in text form. Of these, and will get the day. 
2. 1 is a number indicating the initial position from which the extraction will be performed. 
3. 2 is the last position.

As you can see, the difference between the first and second numbers is not very big. This is because it is enough for us to extract the characters that are in the neighborhood. Therefore, we start with the first and end with the second.

In the same way, the numbers of the month and years are selected in order to write them in the corresponding columns. At the same time, it should be borne in mind that the month in dates presented in this format must be written starting with the fourth character, and the year – from the 7th. 

Don’t forget that you need to convert the date string to text format before extracting.

Accordingly, to find the month and year and add them to the appropriate columns, you need to write the following formulas in their first cells:

=ПСТР(A2:A10;4;2)

=ПСТР(A2:A10;7;4)

As a result, we get the following table. 

As you can see, we managed to break the text string into several parts and write the corresponding values ​​in the cells. But this is only possible when setting the text format. Similar operations can be done with time and any other format, provided that it is first converted to a string format. 

Thus, the possibilities of the function PSTR great amount. It can be used to process huge amounts of information. And since every year the amount of data that needs to be effectively analyzed is constantly increasing, it is necessary to look for high-quality methods of automation. And although some believe that Excel is somewhat outdated, there are no alternatives to this program in its niche. If only because knowing a few formulas, you can achieve almost any functionality of spreadsheets. Good luck to you and ease in mastering this craft.