Excel offers a lot of cool operations with text. One of these is to remove characters from the left side of a string. You can use the REPLACE or LEFT function for this purpose. In this tutorial, you will learn how to remove characters from the left.
How to Remove Characters from the Left in Excel using the REPLACE Function
The syntax used by the REPLACE function is
Here, the REPLACE function takes a string which is the previous_text argument. The removal operation is performed with this string. The start_number is the position of the character you want to start the removal from. The number_chars is the number of characters you want to remove. The new_text here will be an empty string (“”) which will remove the existing characters. REPLACE automatically removes the number of characters specified starting from the start_number provided.
In the following example, you will use an employee data set. Column A has the employee names, column B has the IDs. Column C has the number of characters you will need to remove. To remove the leading characters from the IDs:
- Go to cell D2. Select it with your mouse.
- Assign the formula,
- Click Enter.
- Drag the formula from cell D2 to D7 with your mouse.
This will remove the characters from the left of the IDs.
How to Remove Characters from the Left in Excel using the RIGHT Function
You can nest a LEN function inside the RIGHT function to remove characters from the left. The generic syntax for this is =RIGHT(text,LEN(text)-number_chars).
The RIGHT function extracts the number of the characters provided in the second argument. Here you will be using a LEN function as the second argument providing the length of the text minus the number of characters to remove. This will remove these characters from the left of the string.
To remove the characters from the previous example using RIGHT:
- Go to cell D2 and click on it with your mouse to select it.
- Write the formula,
=RIGHT(B2, LEN(B2)-C2)in the formula bar of D2.
- Click Enter to apply the formula to D2.
- Drag the fill handle to copy the formula from cell D2 to D7.
The result will be the same as the previous example. Excel will remove the number of characters in column B from the left.