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.
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. 4 characters precede each name. 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.
Remove the First 4 Characters in Excel Using VBA
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.
Follow these steps to remove the first four characters in Excel using the VBA:
- Press ALT + F11 to open the Visual Basic Editor.
- Then select Insert > Module.
- Then paste the code into the new module.
- Click on the Run button or press the F5 key to run the code.
As I ran the VBA code, it instantly filled up the cell range B2:B10 with the output.
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,””)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 “ “.
To remove the first four characters in Excel using the REPLACE function, follow this guide:
- Select cell B2.
- Type this formula: =REPLACE(A2,1,4,””)
- Press the ENTER key.
- Now, copy down the formula by dragging the Fill Handle from cell B2 to cell B10.
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
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)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.
To remove the first four characters in Excel using the RIGHT function, follow these steps:
- Select a cell.
- Type the formula: =RIGHT(A2,LEN(A2)-4)
- Press ENTER.
- Drag down the Fill Handle icon to copy the formula from cell B2 to cell B10.
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.
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. 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)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.
To remove the first four characters in Excel using the MID and LEN functions, follow these steps:
- Select cell B2.
- Write the formula: =MID(A2,5,LEN(A2)-4)
- Next, hit ENTER.
- Drag the Fill Handle icon to copy the formula in the cell range B2:B10.
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]
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.
To remove the first four characters in Excel using the Text to Columns feature:
- Select the cell range A2:A10.
- Go to the Data tab.
- 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. - Select the option Fixed Width.
- Then, hit Next.
- Then, inside the Data Preview box, position the cursor between the fourth and fifth characters and click there.
- 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.
- Select the cell range A2:A10.
- Now, go to the Home tab from the ribbon.
- Then click on the Delete drop-down menu from the Cells group.
- Select the command Delete cells.
The Delete dialog box will pop up on the screen. - Select the option Shift cells left and click OK.
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.
The Delete dialog box will pop up on the screen.
The results are now placed inside the column Sample Text. If you want, you can keep them in a separate column.
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.
To remove the first four characters in Excel using the Flash Fill feature, follow these steps:
- Write the content of cell A2 manually without the first 4 characters.
- Go to the Data tab.
- Next click on the Flash Fill icon from the Data Tools group.
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!
Frequently Asked Questions
How do I extract 4 characters from left in Excel?
To extract 4 characters from the left in Excel, use the LEFT function. Here’s a concise formula: =LEFT(cell_reference, 4)
Replace “cell_reference” with the reference to the cell containing the text. This formula extracts the leftmost 4 characters from the specified cell, providing a quick and efficient way to obtain a specific portion of the text in Excel. Adjust the cell reference as needed for your specific case.
How do I trim the last 4 characters in Excel?
To trim the last 4 characters in Excel, you can use the LEFT function along with LEN. Here’s a concise formula: =LEFT(cell_reference, LEN(cell_reference) – 4)
Replace “cell_reference” with the reference to the cell containing the text. This formula extracts the text from the left, excluding the last 4 characters. It’s a quick and effective way to trim the end of a text string in Excel. Adjust the cell reference accordingly for your specific case.
How do I separate the first 4 letters in Excel?
To separate the first 4 letters in Excel, use the LEFT function. Here’s a concise formula: =LEFT(cell_reference, 4)
Replace “cell_reference” with the reference to the cell containing the text. This formula efficiently extracts the initial 4 letters from the specified cell, providing a straightforward way to separate the first characters of a text string in Excel. Adjust the cell reference as needed for your specific scenario.
Related Articles
