While working with Microsoft Excel, we can extract any number of texts from the beginning, middle, or end using functions and formulas. On the other hand, you can remove any number of characters from a cell. In this tutorial, we are going to show you how to remove characters from the left in Excel.
How to Remove Characters from Left in Excel: 6 Ways
To demonstrate this tutorial we are going to use the following dataset:
Here,
Text is the column of the values where you will perform the methods.
Num_Characters is the number of characters you want to remove from the left.
The result is the final text after removing characters from the left.
1. Using the REPLACE Function to Remove Characters from Left
To remove characters from the left, we are going to use the REPLACE function. In this case, we are going to replace our characters from the left with an empty string.
The Basic Syntax of the REPLACE Function:
=REPLACE(string, 1, num_chars, “”)
Steps:
1. Type the following formula in Cell D5.
=REPLACE(B5,1,C5,"")
2. Then, press Enter. It will remove the character you want to remove from the left.
3. After that, drag the Fill Handle over the range of cells D6:D9.
As you can see, the number of characters we want to remove from the left is gone.
Read More: Excel Remove Characters From Right
2. The RIGHT and LEN Functions to Erase Characters from Left
As a rule, the RIGHT and LEN functions remove characters from the left. In this case, the RIGHT function gives us the characters from the right. So, it will delete characters from the left.
The Syntax of the Formula:
=RIGHT(text,LEN(text)-num_chars)
Steps:
1. Firstly, type the following formula in Cell D5.
=RIGHT(B5,LEN(B5)-C5)
2. Next, press Enter.
3. Next, drag the Fill Handle over the range of cells D6:D9.
Ultimately, we have removed the specific number of characters from the left.
3. The MID and LEN Functions to Delete Characters from Left
In General, the MID function gives us the characters starting from the middle of a text. In the case of removing characters from the left, the MID function will return the text starting from a particular index. And it will automatically remove the characters from the left.
The Syntax of the Formula:
=MID(text,1+num_chars,LEN(text))
Note:
1+num_chars is used as the starting number of the text. That means we are starting without the left character that we want to remove.
Steps:
1. First, type the following formula in Cell D5:
=MID(B5,1+C5,LEN(B5))
2. Next, press Enter.
3. Finally, drag the Fill Handle over the range of cells D6:D9.
Finally, our formula returns the same result similar to the previous methods.
4. Using the SUBSTITUTE Function to Remove Characters
Unlike the other methods, we are using the SUBSTITUTE function to replace the characters from the left with an empty string.
The Syntax of the Formula:
=SUBSTITUTE(Text,LEFT(Text,num_chars),””)
In this case, the LEFT function will return the characters from the left that we want to delete.
Steps:
1. First type the following formula in Cell D5:
=SUBSTITUTE(B5,LEFT(B5,C5),"")
2. After that, press Enter.
3. Then, drag the Fill Handle over the range of cells D6:D9.
Finally, we are successful in removing characters from the left.
Read More: How to Remove Characters from Left and Right in Excel
5. Using Text to Columns Wizard to Delete Characters from Left
Typically, using the Text to Columns option of the Data tab in Excel splits our dataset into two columns. In other words, you can remove the specific number of characters from the left.
Now, we are going to use the following data set which has only one column for the ease of demonstration:
Simply put, we are going to split and remove the character from the left.
Steps:
1. Select the range of Cells B3:B7.
2. Now, go to the Data tab > Data Tools > Text to Columns.
3. After that, you will see a dialog box. Select the Fixed width option. Click on Next.
4. Now, in the second dialog box, select between the first character and second character as shown below:
5. Then, click on Next.
6. After that, it will show which characters our column will be divided.
7. Now, click on Finish.
As you can see, our data is divided according to the left character on a new column.
Read More: How to Remove Specific Characters in Excel
6. Excel VBA Codes to Delete Characters from Left
Now, if you have knowledge about the VBA Codes then you should also try this method.
We are using this dataset to demonstrate :
Steps:
1. Press Alt+F11 on the keyboard. It will open the dialog box of VBA. Click Insert > Module.
3. After that, it will open the editor of VBA. Now, type the following code:
Public Function RemoveLeft(txt As String, num As Long)
RemoveLeft = Right(txt, Len(txt) - num)
End Function
3. Now, go to the dataset. Type the following formula in Cell D5.
=RemoveLeft(B5,C5)
4. Then, press Enter.
5. Next, drag the Fill Handle over the range of cells D6:D9.
As you can see, we are successful in removing characters from the left using VBA.
Read More: How to Remove Characters from String Using VBA in Excel
Download Practice Workbook
Conclusion
To conclude, I hope these methods will definitely help you remove the characters from the left in Excel. Download the practice workbook and try this on your own. Surely, it will enrich your knowledge.
