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.

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)

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.

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:

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.

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 :

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.

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.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , and premium Excel consultancy services for Excel and business users. Feel free to contact us with your Excel projects.
A.N.M. Mohaimen Shanto
A.N.M. Mohaimen Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo