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.
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,
- 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.
Step 2:
- 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.
Related Content:How to Remove the First Character from a String in Excel with VBA
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,
- 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: Remove Last Character from String Excel [5 Easy Methods]
Similar Readings:
- VBA to Remove Characters from String in Excel [7 Methods]
- How to Remove Non-Printable Characters in Excel [4 Easy Ways]
- Remove Blank Characters in Excel [5 Methods]
- How to Remove Single Quotes in Excel [6 Ways]
- How to Remove Semicolon in Excel [4 Methods]
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,
- 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!
Read More:How to Remove Character from String in Excel [14 Ways]
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.
- A new window is opened. Now click Insert and select Module to open a new module.
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 FunctionStep 3:
- 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.
Related Content: How to Remove the Last 3 Characters in Excel [4 Formulas]
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!
Related Articles
- Excel Remove Characters From Right [5 ways]
- How to Remove Last Character in Excel[Easiest 6 Ways]
- Remove Dashes from Phone Number in Excel[4 Ways]
- How to Remove Non-numeric Characters from Cells in Excel
- How to Remove Special Characters in Excel [4 Methods]