7+ Methods to Remove Characters from Left in Excel
While processing data in Excel, data cleaning is a vital step for presenting data in a meaningful way. To clean up data, you may need to remove characters from your texts. In this regard, this blog will guide you with more than 7 methods to remove characters from the left in Excel.
Introduction to the Dataset
The dataset I have used throughout the entire article contains 3 columns.
- Raw Data: This column contains texts with unwanted characters at the beginning.
- Number of Characters to Remove: It has a list of numbers that refers to the number of characters to remove from the left.
- Cleaned Up Data: I used this column to keep the data after removing unwanted characters from the left.
Easiest Way to Remove Characters from Left in Excel
The easiest way to clear characters from the left is to insert a formula using the REPLACE function.
Syntax
=REPLACE(old_text, start_num, num_chars, new_text)
Usage Guide
Step_1: Type the formula in the top cell of column “Cleaned Up Data”.
=REPLACE(A2,1,B2,"")
Formula Explanation
- A2 cell contains the text.
- 1 is the character position number from where the replacement begins.
- B2 has the number to the number of characters to remove from the left.
- “” represents a null value. The REPLACE function replaces the number of characters stated in cell B2 with a blank (“”).
Step_2: Press ENTER to insert the formula in cell C2.
Step_3: Bring the cursor to the bottom-right corner of cell C2. Fill Handle icon will appear.
Step_4: Double-click on the icon to copy the formula up to cell C10.
Final Result
Unwanted characters are removed from the left of the texts in the Raw Data column. You will find the result in the Cleanup Up Data column.
Alternative Method #1: Delete Characters from Left in Excel with MID & LEN Functions
The first alternative formula to remove characters from the left has two functions, the MID function & the LEN function.
Syntax
=MID(text,1+num_chars,LEN(text))
Usage Guide
Step_1: Write this formula in the top cell of the column, “Cleaned Up Data”.
=MID(A2,1+B2,LEN(A2))
Formula Explanation
- A2 contains the text (#$%France) with unwanted characters at the left.
- B2 has the number to the number of characters to remove from the left which is 3.
- LEN(A2) determines the total number of characters in cell A2 which is 9.
- The MID function returns LEN(A2) e. 9 characters starting from the (1 + B2 =) 4th character of #$%France. Here, the 4th character is F. Thus the output is France.
Step_2: Press ENTER to insert the formula.
Step_3: Double-click on the Fill Handle icon to copy down the formula to the rest of the cells.
Final Result
Now the unwanted characters are deleted from the beginning of the texts in the Raw Data column. You will see this result in the Cleanup Up Data column.
- 4 Ways to Remove First Word in Excel
- 6 Ways to Remove the First 2 Characters in Excel
- 6 Ways to Remove the First 4 Characters in Excel
Alternative Method #2: Remove Characters from Left Using LEFT & SUBSTITUTE Functions in Excel
You can write another formula with the LEFT and SUBSTITUTE functions. This function will also omit unwanted characters from the start.
Syntax
=SUBSTITUTE(Text,LEFT(Text,num_chars),"")
Usage Guide
Step_1: Type the formula in cell C2.
=SUBSTITUTE(A2,LEFT(A2,B2),"")
Formula Breakdown
- LEFT(A2,B2) selects 3 characters (Mentioned in cell B2) from the left side of A2 (#$%France).
- SUBSTITUTE(A2,LEFT(A2,B2),””) substitutes the first 3 characters of cell A2 with a blank value (“”).
Step_2: Press ENTER to insert the formula into cell C2.
Step_3: Double-click on the “+” icon at the bottom-right corner to copy down the above formula.
Final Result
Finally, the unwanted characters are cleared from the start of the texts in the Raw Data column. You will find the output in the Cleanup Up Data column.
Alternative Method #3: Clear Characters from Left with RIGHT & LEN Functions
The third alternative formula comprises the RIGHT and the LEN functions.
Syntax
=RIGHT(text,LEN(text)-num_chars)
Usage Guide
Step_1: Use this formula in cell C2.
=RIGHT(A2,LEN(A2)-B2)
Formula Explanation
- LEN(A2) calculates the length of the texts in cell A2 (#$%France) which is 9.
- B2 tells the number of characters to remove from the start of the text which is 3.
- The RIGHT function extracts the (9 – 3 =) 6 characters from the right part of #$%France. So, the output becomes France.
Step_2: Hit ENTER to insert the formula.
Step_3: Left-click on the “+” icon and hold it. Then pull it down until cell C10.
Final Result
At this point, the unwanted characters are removed from the beginning of the texts in the Raw Data column. You will get the final result in the Cleanup Up Data column.
Alternative Method #4: Use Flash Fill to Omit Characters from Left in Excel
If you want to remove the special characters from the beginning of a text, then you can use the Flash Fill feature.
The feature operates based on pattern recognition. If you show Excel that you want to remove a specific part, Excel will do the rest by itself. But you must ensure to Excel that there is a pattern in that task.
Another important aspect of this feature is that the Flash Fill feature only works within the adjacent columns.
Look at the following screenshot. Column Raw Data has texts with unwanted special characters at the left. The adjacent column “Cleaned Up Data” will be used for storing the output.
Usage Guide
Step_1: Insert two texts without the characters that you want to remove.
Step_2: Select the entire column including the texts that you’ve just entered.
Step_3: Click on the Fill drop-down in the Home tab.
Step_4: Choose Flash Fill.
Final Result
The Flash Fill feature detected the pattern and automatically deleted all the special characters from the left.
Alternative Method #5: Remove Characters from Left in Excel with Text to Columns Wizard
If you have texts and numbers separated by a space, then you can also use the Text to Columns Wizard to split them out. In this way, you can remove unwanted characters from the left.
Usage Guide
Step_1: Select your data range first.
Step_2: Click on Data in the main ribbon.
Step_3: Click on the Text to Columns command of the Data Tools group.
Step_4: Choose Fixed width in the Convert Text to Columns Wizard dialog box and hit Next.
You will see the texts and numbers are split under the Data Preview section.
Step_5: Just hit Next.
Step_6: Make sure that the Column data format is set to General and hit Finish.
Final Result
The texts and numbers are divided into two different columns. You can use this trick to remove characters from the left.
- 6 Ways to Remove Single Quotes in Excel
- 12 VBA Codes to Remove Characters From String in Excel
- Remove Non-Printable Characters in Excel [5+ Methods]
Alternative Method #6: Create a User-Defined Function with VBA Script to Delete Characters from Left in Excel
Let’s create a user-defined function to make our task easier.
The user-defined function will instantly remove characters from the left if I provide texts and the number of characters to remove.
So, let’s get started.
Usage Guide
Step_1: Right-click on your worksheet name and choose View Code.
This will open the Visual Basic Editor.
Step_2: Click on Module from the Insert menu.
Step_3: Just copy this piece of code.
Public Function ClearCharLeft(cell_ref As String, char_num As Long)
ClearCharLeft = Right(cell_ref, Len(cell_ref) - char_num)
End FunctionStep_4: Press CTRL + V to paste the code in the blank module that we’ve just created.
This VBA script creates a function named ClearCharLeft which requires two arguments.
Step_5: Press CTRL + S to save the file.
A dialog box will pop up.
Step_6: Click No.
Step_7: Save your Excel file as Excel Macro-Enabled Workbook.
So, we are done with creating a user-defined function.
Now let’s remove characters from the left using the function.
Syntax
=ClearCharLeft(cell_ref,char_num)
Usage Guide
Step_1: Write this formula in cell C2.
=ClearCharLeft(A2,B2)
Formula Explanation
- A2 contains text with unwanted characters to remove.
- B2 states the number of characters to remove.
Step_2: Press ENTER to insert the formula.
Step_3: Double-click on the Fill Handle icon to copy down the formula.
Final Result
You will notice the unwanted characters are removed from the start of the texts in the Raw Data column. You will get the final result in the Cleanup Up Data column.
Remove Characters from Left Until a Specific Text in Excel
We can combine the RIGHT, LEN, & FIND functions to create a formula that removes characters from the left until a particular text or symbol is encountered.
In this particular case, we will use a formula to remove characters from the left until the first comma (,) is encountered.
Syntax
=RIGHT(text,LEN(text)-FIND(find_text, within_text)
Usage Guide
Step_1: Type this formula in cell B2.
=RIGHT(A2,LEN(A2)-FIND(",",A2))
Formula Explanation
- FIND(“,”,A2) looks for the first comma within the cell.
- LEN(A2) returns the total number of characters in cell A2.
- LEN(A2)-FIND(“,”,A2) subtracts the number of characters before the first comma encountered in cell A2 from the total number of characters in cell A2.
- RIGHT(A2,LEN(A2)-FIND(“,”,A2)) extracts the characters after the first comma from cell A2.
Step_2: Press ENTER to insert the formula above.
Step_3: Double-click on the Fill Handle icon to copy down to the formula.
Final Result
All the characters before the first comma are removed. These results are stored in the column, “Cleaned Up Data”.
Remove Characters from Left and Right in Excel
Here, I will make a formula using the MID and LEN functions to delete characters both from the left and right in Excel.
Syntax
=MID(string, left_chars + 1, LEN(string) - (left_chars + right_chars)
Usage Guide
Step_1: Use this formula in cell B2.
=MID(A2, 9+1, LEN(A2) - (9+8))
Formula Explanation
- 9 is used to remove “Country: ” (9 characters including the space) from cell A2.
- 9+8 Here, 9 is to remove “Country: ” (9 characters). Then 8 is to remove “, Europe” (8 characters).
- LEN(A2) returns the total number of characters in cell A2 which is 23.
- LEN(A2) – (9+8) is equal to (23 – 17 =) 6.
- MID(A2, 9+1, LEN(A2) – (9+8)) ultimately becomes MID(A2, 10, 6). Thus, the MID function extracts the middle 6 characters starting from character number 10. So, we get France out of Country: France, Europe.
Step_2: Press ENTER now.
Step_3: Double-click on the Fill Handle icon to copy down the formula.
Final Result
Unwanted characters are removed both from the left and the right side. See the result in the column, “Cleaned Up Data”.
Conclusion
I tried to discuss more than 7 different methods to remove unwanted characters from the left in Excel. Some of the methods were based on using different formulas, while some of them of using different command buttons. I really hope that you’ve found the appropriate solutions from this article. Thanks.
Related Articles
