In this tutorial, we will discuss some easy-to-use methods to remove everything after a character in Excel. Often, we work with spreadsheets with a long list of codes containing different types of characters, delimiters, etc. In such cases, sometimes, we need to delete text, numbers, etc. after a certain character to make the spreadsheet look clean and easily readable. So, let us explore the methods.

## Remove Everything After a Character in Excel: 7 Handy Methods

**1. Apply Find and Replace Option to Delete Everything After a Character in Excel**

One of the very easy ways to remove everything after a character is to use the** Find and Replace** tool in Excel. For example, we have a dataset containing employee codes and we want to remove everything after the employee name. Steps associated with this method are:

**Steps:**

- Firstly, select the dataset (
**B5:B9**).

- Then, enter
**Ctrl+H**from the keyboard, and the Find and Replace window will show up. Next, select the**Replace**option and type ‘**,***’ in the ‘**Find what’**Leave the ‘**Replace with**’ field blank. Now, click on**Replace All**.

- Finally, every character after the name will be deleted.

**2. Remove Everything After a Character Using Flash Fill**

Excel has an amazing option named **Flash Fill** which can sense the pattern of your content in cells and fill other cells accordingly. For instance, in our employee code dataset, we want only the names of the employees. So, let’s see the steps involved in applying the Flash Fill method.

**Steps:**

- Write only the name in
**Cell C5**. Then start to write the name in**Cell C6**and you will see that Excel already recognized that you are interested in keeping the employee names from the codes.

- Lastly, hit
**Enter**and get only the names.

**Read More:** How to Remove Text After Character in Excel

**3. Combination of LEFT and SEARCH Functions to Delete Everything After a Character in Excel**

Several combinations of functions can be used to erase everything after a character. Such as, we will combine **LEFT** and **SEARCH** functions to remove characters from a string. In our current dataset, we have a list of people, including their professions. Now, if we only want to keep the names of the people we have to follow the below steps in this method.

**Steps:**

- Write the following formula in
**Cell C5**.

`=LEFT(B5,SEARCH(",",B5)-1)`

- In the end, here is the final result. Use the Excel
**Autofill**(**+)**option to copy the formula to the rest of the cells.

**Breakdown of the Formula:**

➤ **SEARCH(“,”,B5)**

Here, the** SEARCH **function returns the position of the character (here comma).

➤ **SEARCH(“,”,B5)-1)**

Now, one character is subtracted from the number returned by **SEARCH** to exclude the comma(,) from the results.

➤ **LEFT(B5,SEARCH(“,”,B5)-1)**

Finally, the** LEFT **function returns the specified number of characters from the start of the string.

**Read More:** How to Remove Text between Two Characters in Excel

**4. Remove Everything After a Character Using the Combination of LEFT and FIND Functions in Excel**

Likewise, as described in **Method 3**, we can try a combination of the** LEFT** and **FIND** functions to remove everything after a character. Here are the steps:

**Steps:**

- Initially, write the below formula to
**Cell C5**.

`=LEFT(B5,FIND(",",B5)-1)`

Here, we have used **the FIND function** to find the position of the ‘**,**’ character in the text. The rest of the formula works similarly to the formula described in** Method 3**.

- Finally, you will get the names of the people only.

### 5. Delete Everything After Nth Occurrence of a Character in Excel

Sometimes, we might need to delete everything after a particular character. For example, we have a data string containing several commas (**Adelle, Engineer, CEO, 2232**) and we want to discard everything after the 2nd comma. So, to do this task we will follow below steps:

**Steps:**

- First, type the following formula in
**Cell C5**.

`=LEFT(B5,FIND("#",SUBSTITUTE(B5,",","#",2))-1)`

- At last, everything after the 2nd comma will be deleted.

**Breakdown of the Formula:**

➤ **SUBSTITUTE(B5,”,”,”#”,2)**

Here, **the SUBSTITUTE function** replaces the 2nd comma with ‘#’’.

➤ **FIND(“#”,SUBSTITUTE(B5,”,”,”#”,2))**

Then, the **FIND** function tells us the position of the 2nd comma. Here, the 2nd comma position is in the 17th.

➤ **FIND(“#”,SUBSTITUTE(B5,”,”,”#”,2))-1)**

Now, one character is subtracted from the number returned from the previous part of the formula.

➤ **LEFT(B5,FIND(“#”,SUBSTITUTE(B5,”,”,”#”,2))-1)**

Finally, the **LEFT **function returns the specified number of characters from the start of the string.

**6. Remove Everything After Last Occurrence of a Character in Excel**

Often, values in a cell are separated by numbers of delimiters. For example, we have an employee code: **Adelle, Engineer, CEO, 2232** and we want to delete everything after the last comma. So, let’s go through the steps.

**Steps:**

- Firstly, type the following formula in
**Cell C5**.

`=LEFT(B5,FIND("#",SUBSTITUTE(B5,",","#",LEN(B5)-LEN(SUBSTITUTE(B5,",",""))))-1)`

- Finally, everything is deleted after the last comma. Use the
**Autofill (+)**to apply the formula to the rest of the cells.

**Breakdown of the Formula:**

➤ **LEN(SUBSTITUTE(B5,”,”,””))**

The first thing we need to do is to determine the position of the last delimiter (in our example last comma). e have to know how many commas are present in our string. For this purpose, we will replace each comma with blank (**“”**) and pass it through **the LEN function** to get the length of the string, which is **24** for **B5.**

➤ **LEN(B5)-LEN(SUBSTITUTE(B5,”,”,””))**

Here, we subtracted the result of the previous part from the original total length of **B5**. The result is **3**, which is the number of commas present in **B5**.

Then we will apply a combination of **LEFT**, **FIND** and **SUBSTITUTE** functions to delete everything after the last comma (shown in **Method 5**).

**Read More: **How to Remove Letters from Cell in Excel

**7. Erase Everything After a Certain Character Using VBA in Excel**

You can delete everything after a character simply using a **VBA** code. For instance, from our dataset (**B5:B9**), we want to delete everything but the names. Then, we have to follow below steps::

**Steps:**

- Select the dataset (
**B5:B9**) at first.

- Next, right-click the corresponding sheet and choose ‘
**View Code**’.

- Then a
**VBA Module**window will pop up. Now, write the following code and**Run**it.

```
Option Explicit
Sub remove_everything_after_char()
Dim rng As Range
Dim cell As Range
Set rng = Application.Selection
For Each cell In rng
cell.Offset(0, 1).Value = Left(cell, InStr(cell, ",") - 1)
Next cell
End Sub
```

- Finally, here we get only the names as a result, Everything after the first comma is removed successfully.

**Download the Practice Workbook**

You can download the practice workbook that we have used to prepare this article.

**Conclusion**

In the above article, I have tried to discuss all the methods elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.

## Related Articles

- How to Remove Specific Text from Cell in Excel
- How to Remove Text from an Excel Cell but Leave Numbers
- How to Remove Text before a Space with Excel Formula

**<< Go Back To Excel Remove Text | Data Cleaning in Excel | Learn Excel**