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

Get FREE Advanced Excel Exercises with Solutions!

Sometimes when data is copied from another source then there is a possibility of having a couple of information compacted 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 the 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 for a particular product. There are 3 columns and these are Order ID, Brand, and Price. In Order ID, the information of order number and product name is concatenated. So, we extracted the Order Number from cell B4 using the LEFT function. And the extracted part is presented here in cell E4. ## 3 Easy Methods to Get First 3 Characters from a Cell Using Excel Formula

Here we can get the first 3 characters from a cell, using various types of methods. For avoiding any compatibility issues, try to use the Excel 365 edition. ### 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.

STEPS:

• First, 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)`
• 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.
• Therefore, you will see the extracted 3 characters in the Order Number column. • Finally, you can use the Fill Handle to AutoFill the formula in the rest of the cells. Use LEFT with the VALUE function to get the outputs in numeric format instead of text. Hence, follow the below steps.

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.

STEPS:

• 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.
`=VALUE(LEFT(B4,3))`
• 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 in number format. • You can use the Fill Handle to AutoFill the formula in the rest of the cells. ### 2. Apply LEFT with SEARCH Function to Extract First 3 Characters

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.

STEPS:

• 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,SEARCH("_",B4)-1)`
• 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. NOTE: 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. Use LEFT with the FIND function for case-sensitive issues.

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 a special character.

STEPS:

• 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,FIND("_",B4)-1)`
• 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. NOTE: 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. ### 3. Apply MID Function for First 3 Characters From a Cell

By using the MID function you can get the first 3 characters from a cell. Therefore, follow the below process to get the first 3 characters from a cell using the formula in Excel.

STEPS:

• Firstly, select the cell where you want to place your resultant value.
• Here, I selected cell E4.
• Secondly, type the following formula in the selected cell or into the Formula Bar.
`=MID(B4,1,3)`
• 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. To get the results in text format, use MID with the 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.

STEPS:

• 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.
`=VALUE(MID(B4,1,3))`
• After that, 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.
• Finally, 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. Read More: How to Extract Data from a List Using Excel Formula (5 Methods)

## Some More Ways to Get First 3 Characters from a Cell

Apart from using the formula, you can also get the first 3 characters from a cell using some other features and VBA in Excel. Therefore, go through the methods.

1. Get First 3 Characters from a Cell Through Excel VBA

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

STEPS:

• Open the Developer tab >> then select Visual Basic. • It will open a new window of Microsoft Visual Basic for Applications.
• Hence go to Insert >> select Module. • Then 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`````` • 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. • After that, a dialog box will pop up. From there select the Macro to Run. • 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 is extracted in the given column. 2. Utilize Flash Fill to Return First 3 Characters from a Cell

You can use the Flash Fill feature to extract characters.

STEPS:

• 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. • Now, open the Data tab >> from Data Tools >> select Flash Fill. • Finally, all the remaining cells of the Order Number will be filled with 3 characters from the Order ID column. 3. Extract First 3 Characters from a Cell with Text to Columns Feature

3.1 With Delimited

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

STEPS:

• 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.
• 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.
• After that, I selected other and given the delimiter my value has (_).
• Again, click Next. • Here you can choose the Destination otherwise keep it as it is.
• Then, I selected the destination E4:F12.
• Finally, click Finish. • Afterward, a warning message will pop up then click OK. • Thus, you will see the first 3 characters in the Order Number column. 3.2 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.

STEPS:

• 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. • 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.
• Then, I created the break line after the first 3 characters.
• Again, click Next. • Hence you can choose the Destination otherwise keep it as it is.
• Moreover, I selected the destination E4:F12. • Finally, click Finish. • Therefore, a warning message will pop up then click OK. • Finally, you will see the first 3 characters in the Order Number column. ## Practice Section

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

In this article, I’ve explained 6 ways of the 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.

## Related Articles Shamima Sultana

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I'm working as a Project Manager at ExcelDemy. I am doing research on Microsoft Excel and here we will be posting articles related to this. My last educational degree was BSc and my program was in Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  