Sometimes you have data containing characters 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 characters in Excel using the Right and LEN functions. 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 delete 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, stands for the number of characters you will replace with nothing/text. New_text is the text that will be used instead of the existing text. But we will replace it with nothing for our case so that the first 5 numbers can be divided.
For example, if we want to replace the first 5 characters, then we are going to follow these simple steps;
1. Copy and paste the following function in a blank excel cell =REPLACE(A2,1,5, "rep")
2. Click enter
Using VB code
You can also delete the "n" number of characters using the 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
Public
Function
RemoveFirstC(rng As
String, cnt As
Long)
RemoveFirstC = Right(rng, Len(rng) - cnt)
End
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 advise 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 characters and then click Apply and Okay
Using A Combination of MID And LEN Functions to Delete Characters from the Left
Generally, the MID function extracts characters starting from the middle of the text. That means it will return the text starting from a particular index, especially if using it to delete characters from left or right. The general formula for the combination will be:
=MID(text,num_chars,LEN(text)-num), where;
num_chars is used to refer to the starting number of the text. In this case, you are starting without the first characters you want to remove.
text is the text that returns after removing the first characters.
Steps:
1. Type the formula in the cell you want to return the text. For example, if you want to return the text in cell D5, you can write the formula:
=MID(B4,6,LEN(B5)-5)
2. Press the Enter
3. Use the Fill Handle icon to drag the formula in the range of cells you want to delete the first five characters.
5. The formula will return a text without the first characters in each corresponding cell.
Using the SUBSTITUTE Function to Delete First Characters
The SUBSTITUTE function is often characterized under the TEXT functions in Excel and replaces the existing text in a given string with a specified text. It also works similarly to the REPLACE function, though it has a different formula, as shown below:
SUBSTITUTE(text, old_text, new_text, [instance_num]), where;
text is the text to change
old_text is the text to substitute
new_text is the text to substitute with
instance_num (optional) refers to which occurrence from the old text you want to replace or substitute with the new text.
The formula is prone to change based on how you want to use it. For instance, when using it to delete the first five characters, the formula changes to:
=SUBSTITUTE(Text,LEFT(Text,num_chars),””), where;
The LEFT function returns the characters you want to delete from the left.
Steps:
1. Write the formula in the cell you want the return text to appear. For example, if you want the return text to appear in cell D4, you can write the formula as follows:
=SUBSTITUTE(B5,LEFT(B5,C5),"")
2. Press the Enter button.
3. Use the Fill Handle icon to drag the formula over the range of cells you want to delete the first five characters.
4. The formula will return the text without the first five characters.
Using Text to Columns Wizard to Delete First Characters
The Text to Column Wizard in the Data Tab splits the dataset into two columns. That means you can delete the specific number of characters from the left of the text. To use this option, you can proceed with the following steps:
1. Select the range of cells you want to split or change. For example, you can select cells C3:C7.
2. Go to the Data Tab and select Data Tools. Next, select the Text to Columns
3. A dialog box will appear. Select the Fixed Width option and click on the Next
4. In the second dialog box, select the number of characters you want to split or delete and click Next.
5. You will see the text divided into two. Click on Finish, and you will get a new column with the new dataset.
