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

Get FREE Advanced Excel Exercises with Solutions!

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

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

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

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

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

Remove Everything After a Character Using Flash Fill

  • 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.
=LEFT(B5,SEARCH(",",B5)-1)

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

  • 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


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

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

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

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

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.
=LEFT(B5,FIND("#",SUBSTITUTE(B5,",","#",2))-1)

Delete Everything After Nth Occurrence of a Character in Excel

  • 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.
=LEFT(B5,FIND("#",SUBSTITUTE(B5,",","#",LEN(B5)-LEN(SUBSTITUTE(B5,",",""))))-1)

Remove Everything After Last Occurrence of a Character in Excel

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

Erase Everything After a Certain Character Using VBA in Excel

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

Erase Everything After a Certain Character Using VBA in Excel

  • 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

Erase Everything After a Certain Character Using VBA in Excel

  • 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

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo