# 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. ## 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)` 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. #### 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))` 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. ➤ 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)` 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. #### 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)` 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. ### 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)` 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. #### 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))` 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. Read More: How to Extract Data from a List Using Excel Formula (5 Methods)

### 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 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`````` 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. ➤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. 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. ### 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 ➤ 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 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. 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 ➤ 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 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. 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 A dialog box will pop up showing the selection then select My table has headers. Finally, click OK. ➤ A new window will pop Here, select the cell range to get the first 3 characters from a cell.
Now, open Add column tab >> from Extract >> select Range 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. 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. ## 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.

## Related Articles #### Shamima

Hello! Welcome to my Profile. I am Shamima Sultana Rita. 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. 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 