Shamima Sultana
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.
Download to Practice
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.
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.
- You can use the Fill Handle to AutoFill the formula in the rest of the cells.
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.
- You can use the Fill Handle to AutoFill the formula in the rest of the cells.
Read More: How to Extract Data from Cell in Excel (5 Methods)
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.
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.
Read More: Excel VBA: Pull Data Automatically from a Website (2 Methods)
Similar Readings
- How to Extract Month from Date in Excel (5 Quick Ways)
- Transfer Data from One Excel Worksheet to Another Automatically
- VBA Code to Convert Text File to Excel (7 Methods)
- How to Import Text File with Multiple Delimiters into Excel (3 Methods)
- How to Import Data from Secure Website to Excel (With Quick Steps)
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.
Read More: How to Extract Data from Excel Based on Criteria (5 Ways)
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.
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.
Read More: Convert Excel to Text File with Delimiter (2 Easy Approaches)
Read More: How to Extract Data From Table Based on Multiple Criteria in Excel
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
- Return Multiple Values in Excel Based on Single Criteria (3 Options)
- Import Data from Excel into Word Automatically Using VBA (2 Ways)
- Excel Macro: Extract Data from Multiple Excel Files (4 Methods)
- How to Extract Data from Excel to Word (4 Ways)
- Extract Specific Data from a Cell in Excel (3 Examples)
- How to Extract Year from Date in Excel (3 Ways)
