Remove Characters From Left In Excel

Removing characters from the left side of a text string in Excel can be a useful skill when cleaning up data or preparing it for analysis. In this article, we will explore the different methods to remove characters from the left side of a text string in Excel.

Why Remove Characters from the Left Side of a Text String?

There are several reasons why you might need to remove characters from the left side of a text string in Excel. For example, you may have a list of phone numbers with area codes that you want to remove, or you may have a list of dates with unnecessary characters at the beginning. Whatever the reason, Excel provides several methods to remove characters from the left side of a text string.

Method 1: Using the RIGHT Function

One way to remove characters from the left side of a text string in Excel is to use the RIGHT function. The RIGHT function returns a specified number of characters from the right side of a text string. To use the RIGHT function to remove characters from the left side of a text string, you need to combine it with the LEN function, which returns the length of a text string.

Here's an example formula:

=RIGHT(A1,LEN(A1)-3)

Assuming the text string is in cell A1, this formula returns all characters except the first 3 characters from the left side.

How the Formula Works

The LEN function returns the length of the text string in cell A1.
The RIGHT function returns the specified number of characters from the right side of the text string.
By subtracting 3 from the length of the text string, the formula effectively removes the first 3 characters from the left side.

Method 2: Using the MID Function

Another way to remove characters from the left side of a text string in Excel is to use the MID function. The MID function returns a specified number of characters from a text string, starting from a specified position.

Here's an example formula:

=MID(A1,4,LEN(A1))

Assuming the text string is in cell A1, this formula returns all characters except the first 3 characters from the left side.

How the Formula Works

The MID function returns the specified number of characters from the text string in cell A1, starting from the 4th character.
The LEN function returns the length of the text string in cell A1.
By specifying the start position as 4, the formula effectively removes the first 3 characters from the left side.

Method 3: Using VBA Macro

If you need to remove characters from the left side of a text string in Excel and you are comfortable with VBA macros, you can create a custom function to do this.

Here's an example VBA macro:

Function RemoveLeftCharacters(text As String, num As Integer) As String
    RemoveLeftCharacters = Right(text, Len(text) - num)
End Function

This macro creates a custom function called RemoveLeftCharacters that takes two arguments: the text string and the number of characters to remove from the left side.

How to Use the VBA Macro

Open the Visual Basic Editor by pressing Alt+F11 or by navigating to Developer > Visual Basic in the ribbon.
In the Visual Basic Editor, click Insert > Module to create a new module.
Paste the VBA macro into the new module.
Save the workbook as a macro-enabled file (.xlsm).

Method 4: Using Power Query

remove characters from left excel power query

If you are using Excel 2010 or later, you can use Power Query to remove characters from the left side of a text string.

Here's an example:

Select the range of cells that contains the text strings.
Go to the Data tab in the ribbon.
Click From Table/Range to create a new query.
In the Power Query Editor, click Add Column > Custom Column.
In the Custom Column formula, enter the following formula:

= Text.End([Text], Text.Length([Text]) - 3)

Assuming the text string is in the Text column, this formula returns all characters except the first 3 characters from the left side.

How to Load the Query

Click Load to load the query into the worksheet.
The new column will contain the text strings with the first 3 characters removed from the left side.