Remove Characters from Left in Excel with 7 Tricks

Excel, the beloved spreadsheet software, is a powerhouse of data manipulation and transformation. One common task is removing characters from the left of a text string. Whether it’s cleaning up messy data or extracting specific information, Excel offers multiple methods to tackle this challenge efficiently. In this article, I’ll discuss 7 methods to remove characters from the left in Excel. Also, I will cover two additional techniques related to data cleaning in Excel.

The dataset I’m going to use 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.

Remove Characters from the Left in Excel Using the REPLACE Function

You can remove characters from the left in Excel using the REPLACE function by replacing the left characters with an empty string.

Syntax

=REPLACE(old_text, start_num, num_chars, new_text)

Formula

=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 (“”).

Now follow these steps to remove characters from the left using the SUBSTITUTE function:

1. Select cell C2.
2. Type this formula on it: =REPLACE(A2,1,B2,””)
3. Press ENTER to insert the formula in cell C2.
4. Bring the cursor to the bottom-right corner of cell C2. Fill Handle icon will appear.
5. Double-click on the icon to copy the formula up to cell C10.

The REPLACE function is used here to replace the characters starting from the 1st position (the left) and continuing for the specified number of characters with an empty string, effectively removing them from the original text.

Delete Characters from Left in Excel with MID & LEN Functions

This method uses the MID function to extract a substring of text starting from the character after the specified number of characters, effectively deleting characters from the left. The LEN function is used to determine the length of the original text, ensuring the correct number of characters is removed.

Syntax

=MID(text,1+num_chars,LEN(text))

Formula

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

To delete characters from the left in Excel using the MID and LEN functions, follow these steps:

1. Click on the cell where you want the modified text to appear.
2. In the formula bar, type the following formula: =MID(A2,1+B2,LEN(A2))
3. Press ENTER to insert the formula.
4. Double-click on the Fill Handle icon to copy down the formula to the rest of the cells.

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

Delete Characters from Left Using LEFT & SUBSTITUTE Functions in Excel

In this method, the LEFT function extracts the specified number of characters from the left side of the text. Then the SUBSTITUTE function replaces this extracted substring with an empty string, effectively deleting characters from the left.

Syntax

=SUBSTITUTE(Text,LEFT(Text,num_chars),"")

Formula

=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 (“”).

To delete characters from the left in Excel using the LEFT and SUBSTITUTE functions, follow these steps:

1. Select cell C2.
2. Type the formula in the formula bar: =SUBSTITUTE(A2,LEFT(A2,B2),””)
3. Press ENTER to insert the formula into cell C2.
4. Double-click on the “+” icon at the bottom-right corner to copy down the above formula.
   

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.

Clear Characters from Left in Excel with RIGHT & LEN Functions

This method utilizes the RIGHT function to extract the rightmost characters from the text. The LEN function calculates the total length of the original text. By subtracting the desired number of characters, you effectively clear characters from the left.

Syntax

=RIGHT(text,LEN(text)-num_chars)

Formula

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

To clear characters from the left in Excel using the RIGHT and LEN functions, follow these steps:

1. Select a cell.
2. Use this formula: =RIGHT(A2,LEN(A2)-B2)
3. Hit ENTER to insert the formula.
4. Left-click on the “+” icon and hold it. Then pull it down until cell C10.

The “Cleanup Up Data“ column will now display the result with characters cleared from the left. See screenshot:

Use Flash Fill to Remove Characters from Left in Excel

You can use Flash Fill to remove characters from the left in Excel. Flash Fill is a feature that automatically recognizes patterns in your data and makes transformations accordingly.

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

Steps to remove characters from the left in Excel using the Flash Fill:

1. Insert your text without the characters that you want to remove.
2. Select the entire column including the texts that you’ve just entered.
3. Click on the Fill drop-down in the Home tab.
4. Choose Flash Fill. Or, press CTRL + E (Windows) or Command + E (Mac) to activate the Flash Fill.

The Flash Fill will automatically apply the pattern to the entire column, transforming the data as desired. It’s an efficient way to remove characters from the left in Excel and can save time when working with large datasets.

Remove Characters from Left in Excel with Text to Columns Wizard

The Text to Columns Wizard in Excel is a powerful tool that can help you separate text within a cell into different columns based on a specified delimiter or fixed-width format. However, it can also be used to remove characters from the left of your data.

Here’s how you can remove characters from left in Excel:

1. Select your data range first.
2. Go to the “Data” tab in Excel. In the “Data Tools” group, you’ll find the “Text to Columns” button. Click on it to launch the Text to Columns Wizard. Now, you’ll be presented with two options:
   Delimited: Select this option if your data is separated by a specific character (like a space, comma, semicolon, etc.). This is commonly used when you want to split data into separate columns based on a delimiter.
   Fixed Width: Choose this option if your data has a consistent width, and you want to remove a specific number of characters from the left. This is particularly useful when you want to trim a certain number of characters from the beginning of each cell.
3. 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.
4. Just hit Next.
   In this step, you can specify the format for each column or skip columns you don’t need. This step is essential to ensure that your data is transformed as desired.
5. Set the Column data format to General and hit Finish.

The Text to Columns Wizard will apply your chosen settings, effectively removing characters from the left according to your specifications.

• 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]

Delete Characters from the Left in Excel by Creating a User-Defined Function with VBA Script

Removing characters from the left in Excel can also be accomplished by creating a User-Defined Function (UDF) using VBA (Visual Basic for Applications) script. This method provides a custom solution that you can reuse for various Excel tasks.

Here’s how to create a UDF for this purpose:

Step 1: Open Excel and Enable Developer Tab

If you don’t see the Developer tab in your Excel ribbon, you’ll need to enable it. To do this, go to File > Options > Customize Ribbon, and check the “Developer” option.

Step 2: Open the Visual Basic for Applications (VBA) Editor

Click on the Developer tab and then click on “Visual Basic” to open the VBA Editor. Or, simply right-click on your worksheet name and choose View Code. This will open the Visual Basic Editor.

Step 3: Insert a New Module

In the VBA Editor, go to Insert > Module to add a new module.

Step 4: Create a User-Defined Function

In the module, write the VBA code for the UDF. Below is an example of a UDF to remove characters from the left of a cell. 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

This code defines a function called “ClearCharLeft” that takes two arguments: “cell_ref” (the text you want to modify) and “char_num” (the number of characters to remove from the left). It returns the modified text.

Now, press CTRL + V to paste the code in the blank module that we’ve just created.

Step 5: Save and Close the VBA Editor

1. Press CTRL + S to save the file. A dialog box will pop up.
2. Click No.
3. Save your Excel file as “Excel Macro-Enabled Workbook”.
   So, we are done with creating a user-defined function.

Step 6: Use the UDF

Now you can use the UDF just like any other Excel function. Let’s remove characters from the left using the function.

Syntax

=ClearCharLeft(cell_ref,char_num)

Formula

=ClearCharLeft(A2,B2)

Formula Explanation

• A2 contains text with unwanted characters to remove.
• B2 states the number of characters to remove.

Follow these steps to remove characters from the left in Excel using a UDF:

1. Select a blank cell.
2. Write this formula in it: =ClearCharLeft(A2,B2)
3. Press ENTER to insert the formula.
4. Double-click on the Fill Handle icon to copy down the formula.

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.

This custom UDF provides a flexible way to remove characters from the left in Excel and can be used in various Excel workbooks. It’s particularly useful when you have a repetitive data transformation task, and it allows you to perform the task with a single function call.

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)

Formula

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

Follow these steps to remove characters from the left until a particular text or symbol is encountered:

1. Select cell B2.
2. Type this formula: =RIGHT(A2,LEN(A2)-FIND(“,”,A2))
3. Press ENTER to insert the formula above.
4. Double-click on the Fill Handle icon to copy down to the formula.

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 Simultaneously in Excel

This method combines the MID function with the LEN function to precisely remove characters from both sides of the text.

Syntax

=MID(string, left_chars + 1, LEN(string) - (left_chars + right_chars)

Formula

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

To remove characters from both the left and right simultaneously in Excel, follow these steps:

1. Select cell B2.
2. Use this formula: =MID(A2, 9+1, LEN(A2) – (9+8))
3. Press ENTER now.
4. Double-click on the Fill Handle icon to copy down the formula.
   

Unwanted characters are removed both from the left and the right side. See the result in the column, “Cleaned Up Data”.

Conclusion

In this article, I attempted to provide an overview of more than seven different techniques to remove unwanted characters from the left in Excel. Some of the methods were based on using different formulas, while some of them used different command buttons. I hope that you’ve found the appropriate solutions from this article. Thanks.

Frequently Asked Questions

How do I get rid of 2 digits from the left in Excel?

To remove 2 digits from the left in Excel, use the MID function. Here’s how:

1. Click on the cell where you want the modified text to appear.
2. In the formula bar, type the following formula: =MID(A1, 3, LEN(A1)-2)
3. Hit ENTER to apply the formula.

The cell will display the result with 2 digits removed from the left.

How do I remove the first 3 characters in Excel?

To remove the first 3 characters from a text string in Excel,

1. Click on the cell where you want the modified text to appear.
2. In the formula bar, type the following formula: =MID(A1, 4, LEN(A1)-3)
3. Hit ENTER to apply the formula.

The cell will display the result with the first 3 characters removed.

How do I remove 8 characters from the left in Excel?

To remove 8 characters from the left in Excel, you can use the MID function. Here’s how:

1. Click on the cell where you want the modified text to appear.
2. In the formula bar, type the following formula: =MID(A1, 9, LEN(A1)-8)
3. Hit ENTER to apply the formula.

The cell will display the result with the first 8 characters removed.

Related Articles

• 3 Ways to Remove Specific Characters in Excel
• 5 Ways to Remove Blank Characters in Excel
• 5 Ways to Remove Numeric Characters from Cells in Excel
• 5 Approaches to Remove Characters from the Right in Excel
• 3+ Ways to Remove Dashes in Excel