Formulas for Text Segmentation in Excel MID, LEFT or RIGHT

One of the most crucial tasks when editing text strings in Excel is separating a part of the string to extract a text fragment.



Examples of Basic Text Functions in Excel with Descriptions

Using text functions like LEFT, RIGHT, and MID during office work can quickly and easily solve tasks such as:

• Converting territorial codes to postal codes.
• Removing country or mobile operator codes from phone numbers.
• Extracting a fragment of an employee code or position from an employee database.
• And similar tasks...

The ability to quickly solve these basic tasks in Excel is valuable for every office worker.

The example images demonstrate how solutions to the above-described tasks are easily implemented using the text functions LEFT, RIGHT, and MID:

The LEFT function allows an Excel user to separate a fragment of text or a number with a specific number of characters from the beginning of the original data string. This function requires specifying two arguments:

1. Text - the original data. It supports other value types besides text, such as numbers and logical values. It does not support error values, and it interprets dates as numerical values.
2. Number_of_characters - the number of characters to be left in the text fragment when separating it from the string.

In the example, the function =LEFT(A2, 5) extracts the first 5 numbers from the text in cell A2.

The RIGHT function provides the ability to separate a specific number of characters from the end of the original text string. It works inversely proportional to LEFT, requiring the same arguments. The main difference is the direction of the function's action: from right to left, starting the character count from the end.

In this example, using the RIGHT function separates the right part of the company employees' phone numbers. This is the last 9 digits at the end: =RIGHT(A6, 9).

The next text function, MID, is more advanced. It allows obtaining a fragment of text from the middle of the original string. The MID function requires filling in three arguments:

1. Text - the original data (text or numerical value).
2. Start_position - the ordinal number of the character from the beginning of the string to start separating the text fragment.
3. Number_of_characters - the number of characters taken from the middle of the text in the original data.

In the example described, the function =MID(A10, 4, 1) separates only one numerical value starting from the 4th character from the beginning of the original string.

Practical Example of Using Text Formulas MID and LEFT

A retail company follows an internal corporate regulation for encoding categories of sold products. The structure of the code for each product looks like this:

1. The first two characters represent the main category (e.g., MM - Mass Market or EL - Elite).
2. Next are three digits of the digital code for subcategories (e.g., 703 - household chemicals, 205 - kitchen accessories, 124 - perfumery).
3. The remaining characters represent the supplier code.

The task is to create a report outlining the sales structure by product categories and display the metrics on an interactive chart for data visualization.

First, we will create a table to decode the product codes based on the general nomenclature, using the LEFT and MID functions to separate the original text into fragments:

Next, based on the obtained structured data, we create a pivot table and pivot charts:

Download

Now it's very easy and convenient to present a report on the sales structure of goods in different categories. The pivot chart contains interactive controls and filters that enhance the presentation of the report.