How to delete first 5 characters in Excel

How to delete first 5 characters in Excel

Sometimes you have data that contains characters that you don’t need in Excel. They can either exist before or after your data. Deleting these characters is easy by following these techniques. You can delete the first 5 characters by using the right function or using the VB code editor. Let us look see how you can you these two techniques to eliminate the first 5 characters.

These methods can also be used to remove the last five characters but they have to be twisted appropriately first. For instance, the left positions should be used instead of the right positions.

Right() function

You can delete the first 5 or characters in Excel using Right and LEN function. This is how the formula works. Formula: =RIGHT(string_cell,LEN(string_cell)-n_character). RIGHT specifies the position at which you are going to remove characters, string_cell is the cell that you deleting characters, while n_character stands for the number of characters being deleted.

For example, follow the following steps if you want to remove the first 5 characters in the picture below

Steps

1. Copy and paste the following formula =RIGHT(A1, LEN(A1)-5) in an empty cell

2. Click enter


3. The word-level contains 5 characters and they have all been eliminated

Replace the first 5 characters with nothing

Replacing the first 5 characters with nothing is another great way that you can delete the first 5 characters. Use the REPLACE function which follows the following syntax or formula.
=REPLACE(Old_text,Strat_n,Num_Chars,New_text). Now old_text refers to the cells that you want to replace your characters. While start_n refers to the position in which you want to replace the characters. Num_chars on the other hand stand for the number of characters that you will replace with nothing/text. New_text is the text that will be used instead of the existing text. But we are going to replace it with nothing for our case so that the first 5 numbers can characters can be divided.

For example, if we want to replace the first 5 characters in A6 then we are going to follow these simple steps

1. Copy and paste the following function in a blank excel cell =REPLACE(A6,1,2,3,4,5,””)

2. Click enter

Using VB code

You can also delete the “n” number of characters using VB code in the Excel sheet.

Steps

1. Press Alt+F11 to trigger the Microsoft VB code editor

2. Click insert then select module

3. Copy and paste the code below into the module script

1
2
3

4

5

Public
Function
RemoveFirstC(rng As
String, cnt As
Long)
RemoveFirstC = Right(rng, Len(rng) – cnt)
End
Function

4. Save your code and go back to the sheet that you want to remove the first 5 characters and paste the following formula =RemoveFirstC(A11,5)

5. Press the enter key and your first 5 characters will be removed automatically

Using KUTOOLs for excel

If you are dealing with a huge amount of data then I advice using KUTOOLs for excel

1. Download and install the software

2. Select your cells (Highlight)

3. Click Kutools, the text then remove by position

4. Select the position that you want to start with. I.e left for our case

5. Enter your number of character and then click Apply and Okay