While you are working with unstructured raw data you may often need to extract relevant information from it. Sometimes you need to remove the 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. Today in this article, we will discuss 4 methods to remove the first 3 characters in Excel. Before diving into the methods, take a look at the overview of this article.
Remove First 3 Characters in Excel: 4 Suitable Methods
Consider a situation where you have a database containing raw data. The first 3 characters of your 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.
1. Use RIGHT Function to Remove First 3 Characters in Excel
- In cell C4, apply the RIGHT function nested with the LEN. The formula is,
- 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.
- Now that our formula is ready, press ENTER to get the result.
- 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.
Read More: How to Remove First Character in Excel
2. Apply 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.
- Apply the REPLACE function in cell C4. The formula is,
- 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.
- ENTER to get the result. From the result, we can see that our formula is working perfectly.
- Now we will apply the same formula to all cells required.
Read More: How to Remove the Last 3 Characters in Excel
3. Insert 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.
- The formula that we will apply in cell C4 is,
- Here text is B4
- Start_num is 4 as we will remove the first 3 numbers.
- Num_chars is defined as LEN(B4)-3)
- Hit ENTER and apply the formula to all cells. Our job here is done!
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.
- First, go to the Microsoft Visual Basic for Applications Window by pressing Alt+F11.
- A new window is opened. Now click Insert and select Module to open a new module.
- 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
- Our code is written. Now go back to the worksheet and type the function =RemoveFirst3. We can see that the function is created.
- Now, apply the function in cell C4. The function is,
- Press ENTER to get the result.
- Our custom-made function is working properly. We will now apply this function to the rest of the cells to get the final result.
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.
Download Practice Workbook
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.