5 Ways To Remove Characters From Left In Excel

When working with text data in Excel, you may encounter situations where you need to remove characters from the left side of a string. This can be due to various reasons such as correcting formatting issues, removing unwanted prefixes, or standardizing data. Fortunately, Excel provides several methods to accomplish this task. In this article, we will explore five ways to remove characters from the left in Excel.

Method 1: Using the RIGHT Function

The RIGHT function is a popular choice for removing characters from the left side of a string. This function returns the specified number of characters from the right side of a text string. To use the RIGHT function, follow these steps:

• Select the cell where you want to display the result.
• Type =RIGHT(A1,LEN(A1)-X) where A1 is the cell containing the text string and X is the number of characters you want to remove from the left.
• Press Enter to get the result.

For example, if you want to remove the first three characters from the text string "ABC123", the formula would be =RIGHT(A1,LEN(A1)-3). This will return "123".

Method 2: Using the MID Function

The MID function is another useful function for removing characters from the left side of a string. This function returns a specified number of characters from a text string starting from a specified position. To use the MID function, follow these steps:

• Select the cell where you want to display the result.
• Type =MID(A1,X+1,LEN(A1)) where A1 is the cell containing the text string and X is the number of characters you want to remove from the left.
• Press Enter to get the result.

For example, if you want to remove the first three characters from the text string "ABC123", the formula would be =MID(A1,4,LEN(A1)). This will return "123".

Method 3: Using the SUBSTITUTE Function

The SUBSTITUTE function is a versatile function that can be used to remove characters from the left side of a string. This function replaces a specified text string with another text string. To use the SUBSTITUTE function, follow these steps:

• Select the cell where you want to display the result.
• Type =SUBSTITUTE(A1,LEFT(A1,X),"") where A1 is the cell containing the text string and X is the number of characters you want to remove from the left.
• Press Enter to get the result.

For example, if you want to remove the first three characters from the text string "ABC123", the formula would be =SUBSTITUTE(A1,LEFT(A1,3),""). This will return "123".

Method 4: Using the VBA Macro

If you are comfortable with VBA programming, you can create a custom macro to remove characters from the left side of a string. To create a VBA macro, follow these steps:

• Press Alt + F11 to open the Visual Basic Editor.
• In the Editor, click Insert > Module to insert a new module.
• Paste the following code in the module:

Sub RemoveCharactersFromLeft()
    Dim cell As Range
    For Each cell In Selection
        cell.Value = Right(cell.Value, Len(cell.Value) - 3)
    Next cell
End Sub

• Replace 3 with the number of characters you want to remove from the left.
• Click Run > Run Sub/UserForm to run the macro.

Method 5: Using Power Query

Power Query is a powerful data manipulation tool in Excel that can be used to remove characters from the left side of a string. To use Power Query, follow these steps:

• Select the cell range that contains the text strings.
• Go to the Data tab > From Other Sources > From Microsoft Query.
• In the Query Editor, click Add Column > Custom Column.
• In the Custom Column formula, type =Right([Text], Len([Text]) - 3) where Text is the column name that contains the text strings.
• Replace 3 with the number of characters you want to remove from the left.
• Click OK to create the new column.

Excel Functions Image Gallery

In conclusion, removing characters from the left side of a string in Excel can be accomplished using various methods, including the RIGHT function, MID function, SUBSTITUTE function, VBA macro, and Power Query. Each method has its own advantages and disadvantages, and the choice of method depends on the specific requirements of the task. We hope this article has provided you with the necessary knowledge to remove characters from the left side of a string in Excel.

If you have any questions or need further assistance, please leave a comment below.