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

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.

Overview of extracting only the first 3 character from cell B4


How to Get First 3 Characters from a Cell Using Excel Formula: 3 Easy Ways

Here we can get the first 3 characters from a cell, using various types of methods. To avoid any compatibility issues, try to use the Excel 365 edition.

Sample Dataset


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.

Using LEFT Function to Get First 3 Characters from a Cell

  • Finally, you can use the Fill Handle to AutoFill the formula in the rest of the cells.

Dragging the Fill Handle to cell E12 to get the order number value in the range of cell E4:E12

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.

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.

Utilizing the Autofill feature to fill the range of cell E4:E12 with ther range of cells with order number

Read More: How to Extract Data from Excel Sheet


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.

Using LEFT with SEARCH Function to Get First 3 Characters

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.

Pulling the fill handle to cell E12 to extract the first 3 letter of the order ID.

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

Using LEFT with FIND Function to Get First 3 Characters

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.

Fill Handle pulled along to cell C12 to get the desired no of characters.

Read More: How to Extract Data from Cell in Excel


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.

Using MID Function to Get First 3 Characters

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

Use of the autofill feature to automatically to deduce only the first part of the order_id

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.

Using MID with VALUE Function to extract first 3 characters from a cell

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

Utilizing the Autofill feature to fill the range of cell E4:E12 with order number

Read More: How to Extract Data from a List Using Excel Formula


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.

Using VBA to Get First 3 Characters from a Cell

  • It will open a new window of Microsoft Visual Basic for Applications.
  • Hence go to Insert >> select Module.

Inserting module in the visual code editor

  • 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

Inserting Code inside the code editor

  • 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.

Run view maros command from the view tab to see available macro in the sheet

  • After that, a dialog box will pop up. From there select the Macro to Run.

Run appropriate macro from the Macro dialog box

  • 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.

Utilizing the autofill feature to fill the range of cell E4:E12 with order number


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.

Using Flash Fill to Get First 3 Characters

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

go to Flash fill from the data tab to flash fill values in column

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

utilizing the autofill feature to extract the order number from the order_id

Read More: How to Extract Specific Data from a Cell in Excel


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.
  • Using Text to Columns with delimited to Get First 3 Characters from a Cell A dialog box will pop up. From there select the file type Delimited and click Next.

choose delimieted from the original data type

  • 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.

choose others as the delimiters

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

select destination of the exported text

  • Afterward, a warning message will pop up then click OK.

warnign about whether we want to replace the parent data or not

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

Output values after running the VBA macro


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.

Converting order id text to column using the Text to Column feature

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

set the width of the data being exported

  • 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.

Dialog box showing the data preview

  • Hence you can choose the Destination otherwise keep it as it is.
  • Moreover, I selected the destination E4:F12.

selection of the destination cell for exportation of the extracted values

  • Finally, click Finish.

clicking on finish after finishing all the set ups

  • Therefore, a warning message will pop up then click OK.

warning about whether we want to replace data or not

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

output after running the text to column feature


Practice Section

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

Practice Sheet where you can implement you learning


Download to Practice


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

<< Go Back To Extract Data Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Shamima Sultana
Shamima Sultana

Shamima Sultana, BSc, Computer Science and Engineering, East West University, Bangladesh, has been working with the ExcelDemy project for 2 years. She has written over 75+ articles and reviewed 1000+ articles for ExcelDemy. She has also led several teams with Excel VBA and Content Development works. Currently, she is working as the Project Manager and oversees the day-to-day work, leads the services team, allocates resources to the right area, etc. Her work and learning interests vary from Microsoft... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo