Excel Formula to Get First 3 Characters from a Cell(6 Ways)

Sometimes when data is copied from another source then there is a possibility of having a couple of information compact together. For this type of problem extracting character is needed. In this article, I’m going to explain how you can use the EXCEL formula to get first 3 characters from a cell.

I’m going to use a sample dataset to make the explanation easier. Here, I used a dataset of order information of a particular product. There are 3 columns these are Order ID, Brand, and Price. In Order ID the information of order number and product name is concatenated. I will extract the Order Number from there.

Sample Dataset

Download to Practice

6 Ways to Get First 3 Characters from a Cell

1. Using LEFT Function to Get First 3 Characters from a Cell

You can use the LEFT function to get the first 3 characters from a cell.

Let’s see the procedure to use this function.
➤ First, select the cell where you want to place your resultant value. Here, I selected cell E4.
Then, type the following formula in the selected cell or into the Formula Bar.

=LEFT(B4,3)

Using LEFT Function to Get First 3 Characters from a Cell

Here, in the LEFT function, I’ve selected the B4 cell as text and the num_chars 3. As I was given num_chars 3 so the first 3 characters from the left will be extracted.

Finally, press the ENTER key.
Now, you will see the extracted 3 characters in the Order Number column.

➤ You can use the Fill Handle to AutoFill the formula in the rest of the cells.

Using LEFT Function to Get First 3 Characters from a Cell

I. Using LEFT with VALUE Function

There is a disadvantage in the LEFT function it treats all types of value as a string that’s why it converts the numeric value into a text value.
To overcome this problem, you can use the LEFT function with the VALUE function to get the numeric values as numbers.

First, select the cell where you want to place your resultant value.
➤ Here, I selected the cell E4
Then, type the following formula in the selected cell or into the Formula Bar.

=VALUE(LEFT(B4,3))

Using LEFT with VALUE Function to Get First 3 Characters

Here, in the LEFT function, I’ve selected the B4 cell as text and the num_chars 3. Now, it will extract the first 3 characters from the left. Then, the VALUE function will convert the text string to a numeric value.

In the end, press the ENTER key.
Hence, you will see the extracted 3 characters in the Order Number column are represented as number format.

Using LEFT with VALUE Function to Get First 3 Characters

➤ You can use the Fill Handle to AutoFill the formula in the rest of the cells.

II. Using LEFT with SEARCH Function

You also can use the LEFT function with the SEARCH function to get the first 3 characters from a cell if you want to extract characters from any text or special character.

First, select the cell where you want to place your resultant value.
➤ Here, I selected the cell E4
Then, type the following formula in the selected cell or into the Formula Bar.

=LEFT(B4,SEARCH("_",B4)-1)

Using LEFT with SEARCH Function to Get First 3 Characters

Here, in the SEARCH function, given (_) in find_text, then within_text selected the B4 cell. Now, the SEARCH function will give the position number of the given text, then 1 will be subtracted from the position then it will work as num_chars for LEFT. At last, the LEFT function will extract the characters from the left.

Press the ENTER key.
Now, you will see the extracted 3 characters in the Order Number column.

Because the position of the given character (_) was 4 from there -1 was subtracted so the num_chars became 3 that’s why 3 characters are extracted from the left of the find_text.

If you want, you can use the VALUE function here to convert the text string to numeric value by following the method explained in Using LEFT with VALUE section.

➤ You can use the Fill Handle to AutoFill the formula in the rest of the cells.

Using LEFT with SEARCH Function to Get First 3 Characters

III. Using LEFT with FIND Function

Here, you can use the LEFT function with the FIND function to get the first 3 characters from the left if you want to extract value from particular text and special character.

First, select the cell where you want to place your resultant value.
➤ Here, I selected the cell E4
Then, type the following formula in the selected cell or into the Formula Bar.

=LEFT(B4,FIND("_",B4)-1)

Using LEFT with FIND Function to Get First 3 Characters

Here, in the FIND function, given (_) in find_text, then within_text selected the B4 cell. Now, the FIND function will give the position number of the given text then 1 will be subtracted from the position then it will work as num_chars for LEFT. At last, the LEFT function will extract the characters from the left.

Press the ENTER key.
Now, you will see the extracted 3 characters in the Order Number column.

Because the position of the given character (_) was 4 from there -1 was subtracted so the num_chars became 3 that’s why 3 characters are extracted from the left of the find_text.

If you want, you can use the VALUE function here to convert the text string to numeric value by following the method explained in Using LEFT with VALUE section.

➤ You can use the Fill Handle to AutoFill the formula in the rest of the cells.

Using LEFT with FIND Function to Get First 3 Characters

2. Using MID Function Get First 3 Characters From a Cell

By using the MID function you can get the first 3 characters from a cell.

Firstly, select the cell where you want to place your resultant value.
➤ Here, I selected the cell E4
Secondly, type the following formula in the selected cell or into the Formula Bar.

=MID(B4,1,3)

Using MID Function to Get First 3 Characters

Here, in the MID function, I’ve selected the B4 cell as text, start_num 1, and num_chars 3. So, the MID function will extract the characters starting from 1st character to the 3rd character.

In the end, press the ENTER key.
Now, you will see the extracted 3 characters in the Order Number column.

➤ You can use the Fill Handle to AutoFill the formula in the rest of the cells.

Using MID Function to Get First 3 Characters

I. Using MID with VALUE Function

The MID function has the drawback of treating all types of value as a string that’s why it converts the numeric value into a text value.
To rectify this problem, you can use the MID function with the VALUE function to get the numeric values as numbers.

First, select the cell where you want to place your resultant value.
➤ Here, I selected the cell E4
Then, type the following formula in the selected cell or into the Formula Bar.

=VALUE(MID(B4,1,3))

Using MID with VALUE Function

Here, in the MID function, I’ve selected the B4 cell as text, start_num 1, and num_chars 3. So, the MID function will extract the characters starting from 1st character to the 3rd character. Finally, the VALUE function will convert the text string to numeric values.

Press the ENTER key. Therefore, you will see the extracted 3 characters as a numeric value in the Order Number column.

➤ You can use the Fill Handle to AutoFill the formula in the rest of the cells.

Using MID with VALUE Function

3. Using VBA to Get First 3 Characters from a Cell

You can use the VBA to get the first 3 characters from a cell.

➤ Open the Developer tab >> then select Visual Basic

Using VBA to Get First 3 Characters

It will open a new window of Microsoft Visual Basic for Applications.
➤From Insert >> select Module.

Now, write the code in the Module.

Sub Get_First_3Character()
Dim R As Integer
For R = 4 To 12
   ActiveSheet.Cells(R, 5).Value = Left(Cells(R, 2), 3)
Next R
End Sub

Using VBA to Get First 3 Characters

Here R represents the row numbers.
In the LEFT function given the num_chars = 3
Used a For loop to continue the process for the given row range. I have used Cells(R, 5) and Cells(R, 2) that denotes the 5th and 2nd column for a particular row respectively.

Then, Save the code and go back to the worksheet.

Now, select the cell that you want to split into rows.
➤ I selected cell C6
Open the View tab >> from Macros >> select View Macro

➤ A dialog box will pop up. From there select the Macro to Run.

Using VBA to Get First 3 Characters

➤From the dialog box select the Macro name Get_First_3Character.

➤ Finally, you will see that the character from the used row range in VBA are extracted in the given column.

4. Using Flash Fill to Get First 3 Characters from a Cell

You can use the Flash Fill feature to extract characters.

To use Flash Fill first you will need to create a pattern to follow.
Here, I give the pattern of the first 3 characters from the B4 cell.

Using Flash Fill to Get First 3 Characters

Now, open the Data tab >> from Data Tools >> select Flash Fill

Finally, all the remaining cells of Order Number will be filled with 3 characters from the Order ID column.

Using Flash Fill to Get First 3 Characters

5. Using Text to Columns to Get First 3 Characters from a Cell

I. With Delimited

You can also use the Text to Columns from the Ribbon to extract characters from a cell.

To use this option, first, select the cell or range of cells that you want to split.
➤ Here, I selected the cell range B4:B12.
Now, open the Data tab >> from Data Tools >> select Text to Columns

Using Text to Columns to Get First 3 Characters

➤ A dialog box will pop up. From there select the file type Delimited and click Next.

In the dialog box select the Delimiters your value has.
➤ I selected other and given the delimiter my value has (_)
➤ Again, click Next

Using Text to Columns to Get First 3 Characters

Here you can choose the Destination otherwise keep it as it is.
➤ I selected the destination E4:F12
Finally, click Finish.

A warning message will pop up then click OK.

Using Text to Columns to Get First 3 Characters

Thus, you will see the first 3 characters in the Order Number column.

II. With Fixed Width

Again, you can use the Text to Columns from the Ribbon to extract characters from a cell with the Fixed Width option.

To use this option, first, select the cell or range of cells that you want to split.
➤ Here, I selected the B4:B12 cell range.
Now, open the Data tab >> from Data Tools >> select Text to Columns

Using Text to Columns to Get First 3 Characters

➤ A dialog box will pop up. From there select the file type Fixed Width and click Next.

In the dialog box create a break in the line. To do that, click on the desired position.
➤ I created the break line after the first 3 character
➤ Again, click Next

Using Text to Columns to Get First 3 Characters

Here you can choose the Destination otherwise keep it as it is.
➤ I selected the destination E4:F12

Using Text to Columns to Get First 3 Characters

Finally, click Finish.

A warning message will pop up then click OK.

Using Text to Columns to Get First 3 Characters

Finally, you will see the first 3 characters in the Order Number column.

6. Using Power Query to Get First 3 Characters from a Cell

You also can use the Power Query to get the first character from a cell.

First, select the cell range.
Now, open the Data tab >> then select From Table/Range

Using Power Query to Get First 3 Characters

A dialog box will pop up showing the selection then select My table has headers. Finally, click OK.

➤ A new window will pop

Using Power Query to Get First 3 Characters

Here, select the cell range to get the first 3 characters from a cell.
Now, open Add column tab >> from Extract >> select Range

Using Power Query to Get First 3 Characters

A dialog box will pop up. From there select the Starting Index then select Number of Characters
➤ I gave 0 in Starting Index and 3 in Number of Characters
Finally, click OK.

Now, a new column will appear with the first 3 characters and be renamed the column as Order Number.

Using Power Query to Get First 3 Characters

Now you can keep the table on the worksheet.

Practice Section

I’ve provided an extra practice sheet in the worksheet so that you can practice these explained methods.

Practice Sheet

Conclusion

In this article, I’ve explained 6 ways of Excel formula to get the first 3 characters from a cell. These methods will be useful for you whenever you want to get the first 3 characters from a cell. In case you have any confusion or question regarding these methods you may comment down below.

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo