Remove Characters after a Specific Character in Excel (4 Tricks)

We may want to trim some characters after a particular character to make our dataset neat and clean in Excel. There are many reasons to remove characters such as deleting unnecessary texts, keeping only the important facts, making the data table more organized, and so on. Whatever the reason is, you will learn 4 awesome tricks that you can use to remove characters after a specific character in Excel with ease.


Download the Practice Workbook

You are recommended to download the Excel file and practice along with it.


4 Tricks to Remove Characters after a Specific Character in Excel

In this article, we will be using a list of influential businessmen and their country of origin as a dataset to demonstrate all the methods. So, let’s have a sneak peek of the dataset:

Sample Dataset to Remove Characters after a Specific Character in Excel

So, without having any further discussion let’s dive straight into all the methods one by one.


1. Use Find and Replace to Remove Characters in Excel

As in the picture below, we have two columns in our data table. In the first column, we have mostly influential businessmen with the country of origin. All we want to do is, trim each of their country names off. In the second column, we want to store only the name of the businessmen without their country.

As we can see, we can easily replace all the characters after the comma at the end of their names to trim off the country names. To do so,

❶ Copy the texts from Name & Country column to Name only column.

Cell Selection to use the Find and Replace command in Excel

❷ Press CTRL + H to open up the Find and Replace dialog box.

❸ Enter

,*
 in the Find what box.

❹ Leave the Replace with box blank.

❺ Hit the Replace All command.

Find and Replace Dialog box in Excel

When you are done with all the steps above, we will see you’ve successfully removed all the characters after the names as the picture attached below:

Read More: How to Remove Specific Characters in Excel ( 5 Ways)


2. Formula to Delete Characters after a Specific Character in Excel

We can use the LEFT and the FIND functions to write a formula that can delete the characters after a specific character in Excel.

For example, we want to delete all the characters after a comma. To do so, we can use the FIND function. This function finds the comma within the text. After that, the LEFT function allows only a specific character specified by the return value of the FIND function. Now let’s jump into the steps to implement the formula:

❶ First of all, select cell C5 ▶ to store the formula result.

❷ Now type the formula:

=LEFT(B5,FIND(",",B5)-1)
within the cell.

❸ Finally press the ENTER button.

The LEFT and FIND Function

❹ Now drag the Fill Handle icon at the end of the Name Only column.

Fill Handle Icon in Excel

When you are done with all the steps above, you will see your texts trimmed off like in the picture below:

Result after deletion of characters

  Formula Breakdown

  • FIND(“,”,B5) ▶ finds the position of the comma which is 16.
  • FIND(“,”,B5)-1 ▶ subtracts the return value of the FIND function by 1.
  • =LEFT(B5,FIND(“,”,B5)-1) ▶ keeps only the first 15 characters from the left side.

Read More: How to Remove Last Character in Excel(Easiest 6 Ways)


Similar Readings:


3. Remove Trailing Characters in Excel Using Flash Fill 

There’s an amazing feature in Excel that’s called Flash Fill. This feature can show some intelligence in getting its task done. It can get the user’s intention according to their work procedure.

For example, we have names with countries separated by a comma in our dataset. And we want to delete the characters after the comma.

To do so, if we type two consecutive names with the trailing text after the comma, the Flash Fill feature will automatically get our intention and it will suggest the next names without the characters after the comma. Interesting, huh? Yes, it is.

So, let’s dive right into it to see how it actually works:

❶ Select cell C5 and type Andrew Carnegie.

This is the text leaving the characters followed by a comma.

❷ After that, staring typing the next name that is Steve Jobs.

As you can see in the picture below, when we type S for Steve Jobs, it suggests all the following names without the characters after the commas.

So all we need to do to accept this suggestion is to

❸ Press the ENTER button.

Removal of Trailing Characters in Excel Using Flash Fill

When we are done with all the steps above, we will see our desired result like the picture below:

Related Content: How to Remove Numeric Characters from Cells in Excel (5 Methods)


4. Remove Characters after a Specific Character Using VBA Code

We can create a user-defined function using the VBA code to remove characters after a specific character in Excel. Now follow the steps below:

❶ Press ALT + F11 to open up the VBA editor.

❷ Go to Insert Module.

Creation of new module in VBA editor

❸ Copy the following VBA code:

Function CharLoc(x As Range, y As String)
Dim z As Integer
z = Len(x)
For i = z To 1 Step -1
If Mid(x, i - 1, 1) = y Then
CharLoc = i - 1
Exit Function
End If
Next i
End Function

❹ Paste and save the code in the VBA editor.

Remove Characters after a Specific Character Using VBA Code

After that return to the data table and

❺ Select cell C5 and enter the formula:

=LEFT(B5,CharLoc(B5,",")-1)
within the cell.

❻ Press the ENTER button.

LEFT function in Excel

❼ After that drag the Fill Handle icon to the end of the Name Only column.

When you are done with all the steps above, you will get your desired characters removed as the picture below:

Usage of VBA code to remove trailing characters

Read More: How to Remove the First Character from a String in Excel with VBA


Things to Remember

📌 Press the CTRL + H to open up the Find and Replace dialog box.

📌 You can press ALT + F11 to open up the VBA editor.


Conclusion

To sum up, we have discussed 4 methods to remove characters after a specific character in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.


Related Articles

Mrinmoy

Mrinmoy

Hi! This is Mrinmoy. I write about Excel and VBA stuff for Exceldemy. I have my graduation completed from Khulna University of Engineering & Technology, Bangladesh. I have an immense interest in Datascience and Machine Learning. I like to play guitar and watch Tedtalks in my leisure time. I used to be an indie filmmaker and a wedding photographer. I'm liberal to any kind of constructive criticism and inclined towards expanding my learnings.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo