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.
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.
- Now, select cell D5 and go to the Data tab.
- Then, click on the Flash Fill command from the Data Tools group.
- 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.
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.
- Then, enter the formula into the cell:
=MID(B5,7+1,LEN(B5)-(7+4))
- After that, press Enter and you will see the name of the customer.
- Next, for the remaining cells, rewrite the formula depending on the length of the characters.
- Finally, you will see only the names of the customers from cells D5:D11.
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.
- Then, click on Insert > 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.
- 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.
- Then type the formula of the function you created to remove characters from the left and right into cell D5:
=RemoveCharacters(B5,7,4)
- Now, press Enter and you will see the name of the customer in cell D5.
- Next, for the remaining cells, rewrite the formula depending on the length of the characters.
- 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.
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.
- Secondly, enter the formula into the cell:
=RIGHT(B5,LEN(B5)-2)
- Now, press Enter and you will see the name of the product in cell C5.
- 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.
- After that, you will see only the names of the products from cells C5:C11.
- 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.
- Then, enter the formula into the cell:
=LEFT(D5,LEN(D5)-4)
- After that, press Enter and you will see the name of the customer in cell E5.
- 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.
- Finally, you will see the names of the customers in cells E5:E11.
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.
- Now, enter the formula into the cell:
=VALUE(LEFT(B5,(LEN(B5)-4)))
- Then, press Enter and you will see only the quantity of the product in cell C5.
- Subsequently, you will need to rewrite the formula for different cells depending on how many string characters are there with the numerical values.
- Next, you will see the quantity of the products by rewriting the formula for different cells in C5:C11.
- Similarly, to get a new value by removing characters from the left, we are selecting cell E5.
- Now, enter the formula into the cell:
=VALUE(RIGHT(D5,(LEN(D5)-4)))
- Next, press Enter and you will see the ID of the customer in cell E5.
- After that, rewrite the formula for different cells depending on how many string characters you want to remove.
- Finally, you will see the IDs of the customers in cells E5:E11.
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.
