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.

Introduction to the Dataset

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,"")

Easiest Way to Remove Characters from Left in Excel with REPLACE function


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))

Delete Characters from Left in Excel with MID & LEN Functions


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.

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.

Now the unwanted characters are deleted from the beginning of the texts in the Raw Data column

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),"")

Remove Characters from Left Using LEFT & SUBSTITUTE Functions in Excel


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.

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.

Clear Characters from Left with RIGHT & LEN Functions

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.

Output of Clear Characters from Left with RIGHT & LEN Functions

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.

Use Flash Fill to Omit Characters from Left in Excel

Final Result

The Flash Fill feature detected the pattern and automatically deleted all the special characters from the left.

Result: Usage of the Flash Fill to Omit Characters from Left in Excel

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.

Remove Characters from Left in Excel with Text to Columns Wizard

Step_4: Choose Fixed width in the Convert Text to Columns Wizard dialog box and hit Next.

Choose Fixed width in the Convert Text to Columns Wizard dialog box

You will see the texts and numbers are split under the Data Preview section.

Step_5: Just hit Next.

You will see the texts and numbers are split under the Data Preview section

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.

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 Function

Step_4: Press CTRL + V to paste the code in the blank module that we’ve just created.

Create a User-Defined Function with VBA Script to Delete Characters from Left in Excel

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.

Save your Excel file as Excel Macro-Enabled Workbook


 

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)

 

Use a User-Defined Function to Delete Characters from Left in Excel


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))

Remove Characters from Left Until a Specific Text in Excel


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.

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))

 

Remove Characters from Left and Right in Excel


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”.

Unwanted characters are removed both from the left and the right side.

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.

(Visited 679 times, 2 visits today)

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *