How to Remove Everything After a Character in Excel (7 Methods)

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.

Download the Practice Workbook

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

7 Methods to Remove Everything After a Character in Excel

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

Read More: How to Find and Delete Rows in Excel (5 Ways)

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 Value in Excel (9 Methods)

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.

• 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 Formulas in Excel: 7 Easy Ways

Similar Readings

• How to Remove Dotted Lines in Excel (5 Quick Ways)
• Remove Percentage in Excel (5 Quick Ways)
• How to Remove Grid from Excel (6 Easy Methods)
• Remove Borders in Excel (4 Quick Ways)
• How to Remove Data Validation Restrictions in Excel (3 Ways)

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

Likewise, 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.

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.

Related Content: How to Delete Blank Cells and Shift Data Left in Excel (3 Methods)

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.

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

Read More: How to Remove Conditional Formatting in Excel (3 Examples)

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.

• 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 Last Digit in Excel (6 Quick Methods)

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 result, Everything after the first comma is removed successfully.

Read More: How to Remove Formatting in Excel Without Removing Contents

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 Decimals in Excel (13 Easy Ways)
• Remove Subtotals in Excel (2 Easy Tricks)
• How to Remove Checkboxes from Excel (5 Easy Ways)
• Remove the Page Break Lines in Excel (3 Ways)
• How to Remove Strikethrough in Excel (3 Ways)