6 Ways to Remove the First 4 Characters in Excel
If you’re dealing with some data that have prefixes or codes that you need to remove, Excel offers several ways to make the process quick and easy. In this article, I’ll go over six different ways to remove the first 4 characters from the cells in Excel. From using formulas to using features, you can streamline your data and make your work more efficient.
Introduction to the Dataset
In this article, Sample Text and Result are the two columns in the dataset that follows. Notice that in the column Sample Text, I have a list of full names. Each name is preceded by 4 characters. The first 4 characters are sometimes numerical, alphabetical, or symbolic, and some even have combinations of these.
Now I will show you a few methods that will help you remove the first 4 characters in Excel.
Easiest Way to Remove the First 4 Characters in Excel
When you need to remove the first 4 characters on a huge dataset, using the VBA can be convenient. You’ll get the results in a couple of seconds with the help of the VBA code.
This piece of VBA code can smoothly remove the numeric characters from the cells in Excel. Just insert the code inside a new Module in your Visual Basic Editor and then Run it.
Sub RemoveFirstFourChars()
Dim inputRange As Range
Dim outputRange As Range
Dim inputCell As Range
Dim outputCell As Range
'Specify input range
Set inputRange = Range("A2:A10") 'Replace with your input range
'Specify output range
Set outputRange = Range("B2:B10") 'Replace with your output range
'Loop through input range and remove first four characters of each cell
For Each inputCell In inputRange
Set outputCell = outputRange.Cells(inputCell.Row - inputRange.Row + 1, 1)
If Len(inputCell.Value) >= 4 Then
outputCell.Value = Right(inputCell.Value, Len(inputCell.Value) - 4)
Else
outputCell.Value = ""
End If
Next inputCell
End SubEditable Line 1
Set inputRange = Range("A2:A10")
Here, the data from which I want to remove the first 4 characters are in the range A2:A10. So, you may need to modify the range according to your dataset.
Editable line 2
Set outputRange = Range("B2:B10")
I have specified the cell range where I want to put the outputs, cell B2:B10. In your case, specify the range where you want to place the output.
Editable line 3 (Optional)
outputCell.Value = Right(inputCell.Value, Len(inputCell.Value) - 4)
Here, the Right function eliminates the first 4 characters from the left side. To remove more characters replace 4 with your desired value.
Usage Guide
Step_1: Press ALT + F11 to open the Visual Basic Editor.
Step_2: Then select Insert > Module.
Step_3: Then paste the code into the new module.
Step_4: Click on the Run button or press the F5 key to run the code.
Final Result >
As I ran the VBA code, it instantly filled up the cell range B2:B10 with the output.
Alternative Way #1: Use the REPLACE Function to Remove the First 4 Characters in Excel
The REPLACE function in Excel is dedicated to working in a way that replaces an old text with a new text. So here, in this technique, I am going to apply the REPLACE function to replace the first 4 characters with black space.
Syntax
=REPLACE(old_text, start_num, num_char, new_text)
Formula
=REPLACE(A2,1,4,””)
Usage Guide
Step_1: At first, copy the formula from above.
Step_2: Secondly, paste it into cell B2.
Formula Explanation
- A2 is the old_text here. It indicates the cell address where you want to perform the formula.
- 1 indicates the start_num of the formula. From the left side, the position of the first character is 1.
- 4 is the char_num. The number of characters that I want to remove is 4, that’s why the char_num is 4.
- As I want to replace the 4 characters with empty spaces, for this reason, the new_text in this formula is “ “.
Step_3: Press the ENTER key.
Step_4: Now copy down the formula by dragging the Fill Handle from cell B2 to cell B10.
Final Result >
Excel has properly eliminated the first 4 characters using the REPLACE function.
- 7+ Methods to Remove Characters from Left in Excel
- 5 Approaches to Remove Characters from the Right in Excel
- 3+ Ways to Remove Dashes in Excel
Alternative Way #2: Use the RIGHT & LEN Function to Remove the First 4 Characters in Excel
The RIGHT function in Excel allows you to extract a specific number of characters from a text string’s right side. I’ll also apply the LEN function to determine how many characters are in a given text string. So in this alternative method, I’ll utilize a combination formula of the RIGHT function and LEN function to eliminate the first two characters from a cell.
Syntax
=RIGHT(text,LEN(text)-4)
Formula
=RIGHT(A2,LEN(A2)-4)
Usage Guide
Step_1: First, type the formula in cell B2.
Step_2: Second, press ENTER.
Formula Explanation
- A2 is the text argument in the formula. It is the cell address where I am going to apply the formula.
- LEN(A2)-4 means the 4 characters are subtracted from the total length of the cell.
Step_3: Now, drag down the Fill Handle icon to copy the formula from cell B2 to cell B10.
Final Result >
By doing so, you can modify the formula so that it trims characters from the right using the RIGHT function or the left using the LEFT function.
Alternative Way #3: Use the MID & LEN Function to Remove the First 4 Characters in Excel
With the MID function, it is possible to retrieve a portion of the string from a provided text string, by indicating the starting position and length of the string. And again, to count the number of characters in the string, I am going to use the LEN function.
Syntax
=MID(text, start_num, LEN(text)-4)
Formula
=MID(A2,5,LEN(A2)-4)
Usage Guide
Step_1: At first, write the formula in cell B2.
Formula Explanation
- A2 is the formula’s text argument. A2 denotes the cell address where I am going to apply the formula.
- 3 is the start_num argument in the formula. It indicates the starting number from where the characters will be returned in the result.
- LEN(A2)-4 is the char_num. It mentions that all the characters will return in the result except the first 4 characters from the left side.
Step_2: Next, hit ENTER.
Step_3: Then, drag the Fill Handle icon to copy the formula in the cell range B2:B10.
Final Result >
The MID & LEN function has successfully removed the first 4 characters from the target cell range.
- 4 Ways to Remove First Word in Excel
- 6 Ways to Remove the First 2 Characters in Excel
- Remove Non-Printable Characters in Excel [5+ Methods]
Alternative Way #4: Use the Text to Columns Feature to Remove the First 4 Characters in Excel
The Text to Columns feature is a great tool that can separate the text string within the columns. As the position (first position) and number (four characters) of the characters are fixed here, using the Text to Columns feature would be another option to remove the first 4 characters from the cells in Excel.
Usage Guide
Step_1: First, select the cell range A2:A10.
Step_2: Next, go to the Data tab.
Step_3: Now, click on the Text to Columns icon from the Data Tools group.
This will bring the Convert Text to Columns Wizard onto the Excel sheet.
Step_4: Then, select the option Fixed Width.
Step_5: Then, hit Next.
Step_6: Then, inside the Data Preview box, position the cursor between the fourth and fifth characters and click there.
It will create a barrier between the first 4 characters and the rest of the characters.
Step_7: Now, click on the Finish button.
You will see the cells of the Sample Text are separated into two columns now, column A and column B. As a result, the first 4 characters are in column A, and the rest are in column B.
To clean up the column of the first 4 characters, follow these steps.
Step_8: Select the cell range A2:A10.
Step_9: Now, go to the Home tab from the ribbon.
Step_10: Then click on the Delete drop-down menu from the Cells group.
Step_11: Select the command Delete cells.
The Delete dialog box will pop up on the screen.
Step_12: Select the option Shift cells left.
Step_13: Finally click on the OK button.
Final Result >
The results are now placed inside the column Sample Text. If you want, you can keep them in a separate column.
Alternative Way #5: Use the Flash Fill Feature to Remove the First 4 Characters in Excel
The Flash Fill tool is really smart. Just manually type the input in the first cell, and Flash Fill will copy the pattern from the first cell and fill the whole column for you. I’ll use it to show you the last method.
Usage Guide
Step_1: Write the content of cell A2 manually, but exclude the first four characters.
Step_2: Secondly, go to the Data tab.
Step_3: Next click on the Flash Fill icon from the Data Tools group.
Final Result >
Flash Fill immediately recognized that I intended to get cells without the first 4 characters. It did the job perfectly.
Conclusion
In conclusion, there are various methods to remove the first four characters in Excel. Ultimately, it is essential to choose the best method based on the specific requirements of the task at hand. By using the appropriate approach, removing the first four characters in Excel can be done quickly and efficiently. I hope you find the best method for your situation. To find more articles about removing characters, click here. Happy reading!
Related Articles
