How to Use the LEFT and RIGHT Functions to Separate a Text String of Numbers in Excel

Sometimes, you may have a text string of numbers that you want to separate into different parts. For example, you may have a phone number like “1234567890” and you want to extract the area code, the prefix, and the line number. Or you may have a product code like “ABCD1234” and you want to separate the letters and the digits.

One way to do this in Excel is to use the LEFT and RIGHT functions. These functions allow you to extract a certain number of characters from the left or right side of a text string. In this article, we will explain how to use these functions and give an example with real data.

The LEFT and RIGHT functions have the following syntax:

• LEFT(text, [num_chars])
• RIGHT(text, [num_chars])

The text argument is the text string that you want to extract characters from. The num_chars argument is optional and specifies how many characters you want to extract. If you omit this argument, it defaults to 1.

For example, the formula =LEFT("Hello", 2) returns “He”, and the formula =RIGHT("Hello", 3) returns “llo”.

Procedures

To use the LEFT and RIGHT functions to separate a text string of numbers, you need to follow these steps:

1. Identify the text string that you want to separate and how many characters you want to extract from each side.
2. Enter the text string in a cell, or refer to a cell that contains the text string.
3. In another cell, enter the LEFT function with the text string and the number of characters you want to extract from the left side.
4. In another cell, enter the RIGHT function with the text string and the number of characters you want to extract from the right side.
5. If you want to extract more than two parts from the text string, you can use the MID function to get the characters in the middle. The MID function has the following syntax:

• MID(text, start_num, num_chars)

The text argument is the same as before. The start_num argument specifies the position of the first character you want to extract, starting from 1. The num_chars argument specifies how many characters you want to extract.

For example, the formula =MID("Hello", 2, 2) returns “el”.

Example

Let’s say you have a list of product codes in column A, and you want to separate them into three parts: the first two letters, the next two digits, and the last four digits. You can use the following formulas in columns B, C, and D:

• =LEFT(A2, 2)
• =MID(A2, 3, 2)
• =RIGHT(A2, 4)

Here is a sample table with the results:

Other Approaches

There are other ways to separate a text string of numbers in Excel, such as using the Text to Columns feature or the FIND and LEN functions. However, the LEFT and RIGHT functions are simple and easy to use, and they can handle any text string regardless of its length or format.