# 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:

- Select cell
**C2.** - Type this formula on it:
**=REPLACE(A2,1,B2,””)** - Press
**ENTER**to insert the formula in cell**C2**. - Bring the cursor to the bottom-right corner of cell
**C2**.**Fill Handle**icon will appear. - 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:

- Click on the cell where you want the modified text to appear.
- In the formula bar, type the following formula:
**=MID(A2,1+B2,LEN(A2))** - Press
**ENTER**to insert the formula. - 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:

- Select cell
**C2.** - Type the formula in the formula bar:
**=SUBSTITUTE(A2,LEFT(A2,B2),””)** - Press
**ENTER**to insert the formula into cell**C2**. - 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:

- Select a cell.
- Use this formula:
**=RIGHT(A2,LEN(A2)-B2)** - Hit
**ENTER**to insert the formula. - 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**:

- Insert your text without the characters that you want to remove.
- Select the entire column including the texts that you’ve just entered.
- Click on the
**Fill**drop-down in the**Home**tab. - 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:

- Select your data range first.
- 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. - 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. - 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. - 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

- Press
**CTRL + S**to**save**the file. A dialog box will pop up. - Click
**No**. - 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:

- Select a blank cell.
- Write this formula in it:
**=ClearCharLeft(A2,B2)** - Press
**ENTER**to insert the formula. - 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:

- Select cell
**B2**. - Type this formula:
**=RIGHT(A2,LEN(A2)-FIND(“,”,A2))** - Press
**ENTER**to insert the formula above. - 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:

- Select cell
**B2**. - Use this formula:
**=MID(A2, 9+1, LEN(A2) – (9+8))** - Press
**ENTER**now. - 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:

- Click on the cell where you want the modified text to appear.
- In the formula bar, type the following formula:
**=MID(A1, 3, LEN(A1)-2)** - 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,

- Click on the cell where you want the modified text to appear.
- In the formula bar, type the following formula:
**=MID(A1, 4, LEN(A1)-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:

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

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

**Related Articles**