How to Remove Characters from Left and Right in Excel

Get FREE Advanced Excel Exercises with Solutions!

Today we will discuss how to remove characters from left and right in a string of characters in Excel. Sometimes, there may be extra characters in a cell and we may need to remove them. It is possible to extract data or characters from given characters in a cell by using different functions, formulas, and commands.

Overview of How to Remove Characters from Left and Right in Excel


How to Remove Characters from Left and Right in Excel: 3 Methods

In this article, we will demonstrate how we can remove characters from left and right in Excel. There are several methods to do it. We can use the Flash Fill command to remove characters from left and right. We can use functions such as the MID function to extract characters in a cell. We can also use VBA Codes to remove characters from left and right in Excel.


Method 1: Use Flash Fill Command to Remove Characters from Left and Right in Excel

We can use the Flash Fill command to remove characters from left and right in Excel. This is the quickest and easiest way to extract characters from a string in a cell. Here in the worksheet, customers have ordered different products from a shop. The order detail of products is compressed with the name and a unique ID of the consumer in Customer Name, ID, and Order field. Order field represents the quantity along with the name of the product. We want to get the name of the customer in the Customer Name field. Simply, follow the steps below.

Steps:

  • To begin, we will create a pattern or example to use the Flash Fill command. We want to get the Customer Name from the Customer Name, ID and Order field. So we need to remove some characters from both the left and right sides. We have written ‘Adam’ in cell D5 as an example of what we want.

Selecting a Particular Cell to Remove Characters from Left and Right in Excel

  • Now, select cell D5 and go to the Data tab.

Selecting Data Tab

  • Then, click on the Flash Fill command from the Data Tools group.

Selecting the Flash Fill Command

  • Finally, you will see the names of the customers in cells D5:D11. So, we have removed characters from the left side and right side of the Customer Name, ID, and Order field to get only the names of the customers.

Using Flash Fill Command to Remove Characters from Left and Right in Excel


Method 2: Use MID function to Remove Characters from Left and Right in Excel

We can use the MID function to remove data from either side at once. We can retrieve relevant data by using the MID function if the available dataset has multiple pieces of information compressed together. The worksheet is the same as Method 1. To remove characters from both sides, follow the simple steps.

Steps:

  • To begin, select the cell where you want to keep the value after removing the characters from left and right. We are selecting cell D5.

Selecting a Particular Cell to Remove Characters from Left and Right in Excel

  • Then, enter the formula into the cell:
=MID(B5,7+1,LEN(B5)-(7+4))
This formula will remove all the left and right characters except the name of the customer in cell B5

Entering a Formula into the Cell to Remove Characters from Left and Right in Excel

  • After that, press Enter and you will see the name of the customer.

Applying the Formula into the Cell

  • Next, for the remaining cells, rewrite the formula depending on the length of the characters.

Rewriting the Formula for Multiple Cells

  • Finally, you will see only the names of the customers from cells D5:D11.

Using MID Function to Remove Characters from Left and Right in Excel

Read More: Excel Remove Characters From Right


Method 3: Employ VBA Codes to Remove Characters from Left and Right in Excel

You can also use VBA codes to remove characters from left and right in Excel. This method is very effective and easy to use. The dataset is the same as Method 1. Simply follow the below steps.

Steps:

  • To begin, go to the Developer tab and click on Visual Basic and it will open Microsoft Visual Basic for Applications.

or press Alt+F11.

Selecting Visual Basic from Developer Tab to Remove Characters from Left and Right in Excel

  • Then, click on Insert > Module.

Inserting into the Module

  • Subsequently, enter the following VBA code:
Function RemoveCharacters(txt, num_chars, numm_chars)
i = Right(txt, Len(txt) - num_chars)
j = Left(i, Len(i) - numm_chars)
RemoveCharacters = j
End Function

  • Then, Save the code and go back to the worksheet.

Saving the VBA Code

  • Now, we want to see the Customer Name by removing characters from the left and right of the Customer Name, ID, and Order field. We are selecting cell D5 to begin.

Selecting a Particular Cell to Remove Characters from Left and Right in Excel

  • Then type the formula of the function you created to remove characters from the left and right into cell D5:
=RemoveCharacters(B5,7,4)
This formula will remove 7 characters from left and 4 characters from right side of cell B5

Entering a Formula into the Cell

  • Now, press Enter and you will see the name of the customer in cell D5.

Applying the Formula into the Cell

  • Next, for the remaining cells, rewrite the formula depending on the length of the characters.

Rewriting the Formula for the Remaining Cells

  • Finally, you will see the names of the customers in cells D5:D11 by removing characters from left and right of the Customer Name, ID, and Order field.

Employing VBA Codes to Remove Characters from Left and Right in Excel

Read More: How to Remove Characters from String Using VBA in Excel


Apply LEFT and RIGHT Functions to Remove Characters Separately from Left and Right in Excel.

We can use different functions to remove characters from left and right separately in Excel. We can use the LEFT function, and the RIGHT function to remove characters from right and left respectively. It is very easy to use. In this worksheet, there are multiple entries for different delivery orders and customers from a shop. Product Quantity with Name represents the number of quantity and name of a product that a customer has ordered. Customer Name with ID is the name of the consumer along with a unique ID given by the shop. We will extract the Product Name and Customer Name from the given data. Simply, follow the steps below.

Steps:

  • First, select the cell where you want to place your new value. We are selecting cell C5. We want to see the Product Name by removing characters from the left of the Product Quantity with Name field.

Selecting a Particular Cell to Remove Characters from Left and Right in Excel

  • Secondly, enter the formula into the cell:
=RIGHT(B5,LEN(B5)-2)
This formula will remove the first two characters from the left of cell B5

Entering a Formula into the Cell

  • Now, press Enter and you will see the name of the product in cell C5.

Applying Formula into a Cell

  • Then, you can use the Fill Handle to Autofill the data for the cells if the number of characters you want to remove from the cells remains the same.

Using Fill Handle to Apply Formula in Multiple Cells

  • After that, you will see only the names of the products from cells C5:C11.

Applying LEFT and RIGHT functions to Remove Characters from Left and Right in Excel

  • Subsequently, we want to see the Customer Name by removing characters from the right of the Customer Name with ID field. We are selecting cell E5 to begin.

Selecting a Particular Cell to Remove Characters from Left and Right in Excel

  • Then, enter the formula into the cell:
=LEFT(D5,LEN(D5)-4)
This formula will remove the last four characters of cell D5

Entering a Formula to Remove Characters from Left and Right in Excel

  • After that, press Enter and you will see the name of the customer in cell E5.

Applying Formula into the Cell

  • Next, you can use the Fill Handle and Autofill data for the remaining cells if the number of characters you want to remove remains the same.

Using Fill Handle to apply Formula in Multiple Cells

  • Finally, you will see the names of the customers in cells E5:E11.

Applying LEFT and RIGHT Functions to Remove Characters from Left and Right in Excel

Read More: How to Remove Characters from Left in Excel


Combine LEFT, RIGHT, and VALUE Functions to Remove Characters from Left and Right Separately in Excel

While dealing with numerical values, you can use the VALUE function along with the LEFT and RIGHT functions to remove characters from a string in a cell. Here, the worksheet is the same as the previous one, but instead of Product Name and Customer Name, we want to get the Product Quantity and Customer ID respectively. Just follow the steps below.

Steps:

  • First, select the cell where you want to put your new value after removing the characters from the right. We are selecting cell C5.

Selecting a Particular Cell to Remove Characters from Left and Right in Excel

  • Now, enter the formula into the cell:
=VALUE(LEFT(B5,(LEN(B5)-4)))
This formula will remove the last four characters of cell B5

Entering a Formula into the Cell

  • Then, press Enter and you will see only the quantity of the product in cell C5.

Applying the Formula into the Cell

  • Subsequently, you will need to rewrite the formula for different cells depending on how many string characters are there with the numerical values.

Rewriting the Formula for Multiple Cells

  • Next, you will see the quantity of the products by rewriting the formula for different cells in C5:C11.

Combining LEFT, RIGHT and VALUE Functions to Remove Characters from Left and Right in Excel

  • Similarly, to get a new value by removing characters from the left, we are selecting cell E5.

Selecting a Particular Cell to Remove Characters from Left and Right in Excel

  • Now, enter the formula into the cell:
=VALUE(RIGHT(D5,(LEN(D5)-4)))
This formula will remove the first four characters from cell D5

Entering a Formula into the Cell  to Remove Characters from Left and Right in Excel

  • Next, press Enter and you will see the ID of the customer in cell E5.

Applying the Formula to the Cell

  • After that, rewrite the formula for different cells depending on how many string characters you want to remove.

Rewriting the Formula for Multiple Cells

  • Finally, you will see the IDs of the customers in cells E5:E11.

Combining LEFT, RIGHT and VALUE Functions to Remove Characters from Left and Right in Excel


Download Practice Book


Conclusion

In this article, we have talked in detail about how to remove characters from left and right in Excel. This article will allow users to use Excel more efficiently and effectively. If you have any questions regarding this essay, feel free to let us know in the comments.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abu Sina Ibne Albaruni
Md. Abu Sina Ibne Albaruni

This is Abu Sina, a fresh Engineering Graduate and currently an Excel and VBA content developer at SOFTEKO. I did my bachelor’s in Mechanical Engineering from BUET. My passion is to build new skills, gather experience and apply them to optimize and balance various trade-offs for a better future. Besides, I am fond of travelling, hanging out with friends, and watching movies.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo