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.

Dataset for showing how to remove the first 4 characters in Excel

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 Sub

Editable 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.

Open Module in the Visual Basic Editor

Step_3: Then paste the code into the new module.

Input the VBA code in the Module to remove the first 4 characters in Excel

Step_4: Click on the Run button or press the F5 key to run the code.

Final Result >

Apply VBA code to remove the first 4 characters in Excel

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.

Use REPLACE Function to Remove the First 4 Characters in Excel


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.

copy down the REPLACE formula by dragging the Fill Handle from cell B2 to cell B10

Final Result >

Application of the REPLACE function to remove the first 4 characters in Excel

Excel has properly eliminated the first 4 characters using the REPLACE function.



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.

Use the RIGHT & LEN Function to Remove the First 4 Characters in Excel


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.

Dragging down the Fill Handle icon to copy the RIGHT & LEN function from cell B2 to cell B10

Final Result >

Application of the RIGHT & LEN Function to Remove the First 4 Characters in Excel

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.

Use MID & LEN Function to Remove the First 4 Characters in Excel


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.

Dragging the Fill Handle icon to copy the MID & LEN function formula in the cell range B2:B10

Final Result >

Application of the MID & LEN function to remove the first 4 characters in Excel

The MID & LEN function has successfully removed the first 4 characters from the target cell range.



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.

Open the Text to Columns feature from the Data Tools group in the Data tab

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.

Use the Text to Columns Feature to Remove the First 4 Characters in Excel

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.

Use the Flash Fill Feature to Remove the First 4 Characters in Excel

Step_2: Secondly, go to the Data tab.

Step_3: Next click on the Flash Fill icon from the Data Tools group.

Open the Flash Fill feature from the Data Tools group under the Data tab in Excel

Final Result >

Application of the Flash Fill feature to remove the first 4 characters in Excel

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

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *