How to Remove Characters from Left in Excel (6 Ways)

Get FREE Advanced Excel Exercises with Solutions!

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:

dataset for excel remove characters from left

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,"")

replace formula to remove character from left

2. Then, press Enter. It will remove the character you want to remove from the left.

result after typing replace function

3. After that, drag the Fill Handle over the range of cells D6:D9.

drag formula to remove characters from left

As you can see, the number of characters we want to remove from the left is gone.


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)

RIGHT function to remove character from left

2. Next, press Enter.

result after removing character

3. Next, drag the Fill Handle over the range of cells D6:D9.

excel remove characters from left

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))

MID function to remove characters from left

2. Next, press Enter. 

Result after typing MID function

3. Finally, drag the Fill Handle over the range of cells D6:D9.

excel remove characters from left

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),"")

SUBSTITUTE function to remove characters

2. After that, press Enter.

result after entering the formula

3. Then, drag the Fill Handle over the range of cells D6:D9.

characters removed from the left

Finally, we are successful in removing characters from the left.


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:

using text to columns to remove characters

Simply put, we are going to split and remove the character from the left.

Steps:

1.  Select the range of Cells B3:B7.

select the range of cells

2. Now, go to the Data tab >  Data Tools > Text to Columns.

Data tab for text to columns

3. After that, you will see a dialog box. Select the Fixed width option. Click on Next.

select fixed width

4. Now, in the second dialog box, select between the first character and second character as shown below:

select the left character to remove

5. Then, click on Next.

6. After that, it will show which characters our column will be divided.

selected characters to be removed

7. Now, click on Finish. 

new column without the left character

As you can see, our data is divided according to the left character on a new column.


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 :

dataset for using VBA codes

Steps:

1. Press Alt+F11 on the keyboard. It will open the dialog box of VBA. Click Insert > Module. 

excel VBA 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

VBA code to remove characters from left

3. Now, go to the dataset. Type the following formula in Cell D5.

=RemoveLeft(B5,C5)

VBA formula to remove characters from left

4. Then, press Enter.

result after entering the formula

5. Next, drag the Fill Handle over the range of cells D6:D9.

drag fill handle to copy the formula

As you can see, we are successful in removing characters from the left using VBA.


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.


Excel Remove Characters from Left: Knowledge Hub

<< Go Back To Excel Remove Characters | Data Cleaning in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
A.N.M. Mohaimen Shanto
A.N.M. Mohaimen Shanto

A.N.M. Mohaimen Shanto, a B.Sc. in Computer Science and Engineering from Daffodil International University, boasts two years of experience as a Project Manager at Exceldemy. He authored 90+ articles and led teams as a Team Leader, meticulously reviewing over a thousand articles. Currently, he focuses on enhancing article quality. His passion lies in Excel VBA, Data Science, and SEO, where he enjoys simplifying complex ideas to facilitate learning and growth. His journey mirrors Exceldemy's dedication to excellence and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo