How to Remove First 3 Characters in Excel (4 Methods)

While you are working with unstructured raw data you may often need to extract relevant information from it. Sometimes you need to remove first, second or third characters from your text string to retrieve the value. Excel has some functions by which you can do that kind of task. You can also make your customized function to remove characters from text strings too. Today in this article, we will discuss how to remove the first 3 characters in Excel.


Download practice Workbook

Download this practice book to exercise the task while you are reading this article.


4 Suitable Ways to Remove First 3 Characters in Excel

Consider a situation where you have a database containing raw data. The first 3 characters of your every data are unnecessary and now you have to remove those characters. In this section, we will show how to remove those first 3 characters from your data in Excel.

Use the RIGHT Function to Remove First 3 Characters in Excel


1. Use the RIGHT Function to Remove First 3 Characters in Excel

The combination of the RIGHT function and the LEN function can help you to remove the first 3 characters from your data cells. This method is described in the steps below.

Step 1:

  • In cell C4, apply the RIGHT function nested with the LEN The formula is,
=RIGHT(B4,LEN(B4)-3)
  • Here, string_cell is B4 from where we will remove 3 characters.
  • LEN(B4)-3 is used as the num_chars. The LEN function will make sure to remove the first 3 characters from the cell.

Use the RIGHT Function to Remove First 3 Characters in Excel

Step 2:

  • Now that our formula is ready, press ENTER to get the result.

Use the RIGHT Function to Remove First 3 Characters in Excel

  • Our result is here. To get the full result, move your mouse cursor to the bottom right corner of your cell. When the cursor shows the cross sign, double click on the sign to apply the same function to the rest of the cells.

Use the RIGHT Function to Remove First 3 Characters in Excel


2. Apply the REPLACE Function to Remove First 3 Characters in Excel

The REPLACE function usually replaces part of a text string with a different text string. But in this section, we will utilize this function to remove characters from cells. Let’s see how it’s done.

Step 1:

  • Apply the REPLACE function in cell C4. The formula is,
=REPLACE(B4,1,3,"")
  • Where B4 is the Old text.
  • Start_num is 1. We will start from the beginning.
  • Num_chars is 3 as we want to replace the first three characters.
  • New_text is the modified text that will replace the old text.

Apply the REPLACE Function to Remove First 3 Characters in Excel

  • ENTER to get the result. From the result, we can see that our formula is working perfectly.

Apply the REPLACE Function to Remove First 3 Characters in Excel

  • Now we will apply the same formula to all cells required.

Apply the REPLACE Function to Remove First 3 Characters in Excel


3. Insert the MID Function to Remove First 3 Characters in Excel

The combination of the MID function and the LEN function does the same operation as method one. We will now apply this formula to our dataset.

Step 1:

  • The formula that we will apply in cell C4 is,
=MID(B4,4,LEN(B4)-3)
  • Here text is B4
  • Start_num is 4 as we will remove the first 3 numbers.
  • Num_chars is defined as LEN(B4)-3)

Insert the MID Function to Remove First 3 Characters in Excel

  • Hit ENTER and apply the formula to all cells. Our job here is done!

Insert the MID Function to Remove First 3 Characters in Excel


4. Introduce a User Defined Function to Remove First 3 Characters in Excel

You can also make a function of your own to complete this task. That’s right, you can define a custom function of your own to do your job. You need to write a VBA code to do that. We will discuss this process using the steps below. To know more about custom-made functions, Click Here!

Step 1:

  • First, go to the Microsoft Visual Basic for Applications Window by pressing Alt+F11.

Introduce a User Defined Function

  • A new window is opened. Now click Insert and select Module to open a new module.

Introduce a User Defined Function

Step 2:

  • In the newly opened module, Insert the VBA code to make a UFD to remove the first 3 characters from your cells. We have provided the code for you. You can just copy this code and use it in your worksheet. The name of our user-defined function is RemoveFirst3. And the code to create this function is,
Public Function RemoveFirst3(rng As String, cnt As Long)
RemoveFirst3 = Right(rng, Len(rng) - cnt)
End Function

Introduce a User Defined Function

Step 3:

  • Our code is written. Now go back to the worksheet and type the function =RemoveFirst3. We can see that the function is created.

Introduce a User Defined Function

  • Now apply the function in cell C4. The function is,
=RemoveFirst3(B4,3)

Introduce a User Defined Function

  • Press ENTER to get the result.

Introduce a User Defined Function

  • Our custom-made function is working properly. We will now apply this function to the rest of the cells to get the final result.

Introduce a User Defined Function


Things to Remember

👉 Creating a custom formula has more limitations than regular VBA macros. It cannot alter the structure or format of a worksheet or cell.

👉 Applying the UFD, you can remove the N number of characters from your cells.


Conclusion

In this guide, we have gone through four different approaches to remove the first 3  characters from cells in excel. You are most welcome to comment if you have any questions or queries. Leave your feedback in the comment section. Thanks for visiting ExcelDemy. Keep Learning!


Similar Articles to Explore

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo