How to Remove Characters After a Specific Character in Excel

Get FREE Advanced Excel Exercises with Solutions!

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. Here, we will show a simple gif for a better understanding of removing characters.


Download the Practice Workbook

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


4 Easy Ways 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 at the dataset.

 

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


Method 1: Using Find and Replace Command 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 a 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, follow the below steps accordingly.

Steps:

  • Firstly,  copy the texts from Name & Country column to the Name only column.

Copy All the Texts in Another Column

  • Secondly, Press  CTRL+H  to open up the Find and Replace dialog box.
  • Thirdly, Enter the following characters in the Find what box

,*

.
  • Then,  leave the Replace with box blank.
  • After that, hit Replace All command.

Using Find and Replace Command to Remove Characters 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 in the picture attached below.

Showing Result by Using Find and Replace Command to Remove Characters in Excel

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


Method 2. Nesting LEFT and FIND Functions to Remove 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:

Steps:

  • First of all, select cell C5 ▶ to store the formula result.
  • Secondly, type the formula within the cell.

:

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

  • After that, press ENTER.

Nesting LEFT and FIND Functions to Remove Characters After a Specific Character in Excel

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.
  • Now drag the Fill Handle icon at the end of the Name Only column.

Using Fill Handle tool

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

Showing Result by Nesting LEFT and FIND Functions to Remove Characters After a Specific Character in Excel

 

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


Similar Readings:


Method 3: Using Flash Fill Feature to Remove Trailing Characters in Excel

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 following 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 by following some steps.

Steps:

  • To begin with, 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.

Sample Data Set

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

Using Flash Fill Feature to Remove Trailing Characters in Excel

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

Showing Result by Using Flash Fill Feature to Remove Trailing Characters in Excel

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


Method 4:  Applying VBA Code to Remove Characters after a Specific Character

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.

Steps:

  • Firstly, Press  ALT+F11  to open up the VBA editor.
  • Secondly,  go to Insert ▶Module.

Opening VBA Editor Window

  • Then, 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

  • After that, paste and save the code in the VBA editor.

Applying VBA Code to Remove Characters after a Specific Character

  • After that return to the data table and
  • Now, select cell C5 and enter the formula within the cell.

=LEFT(B5,CharLoc(B5,",")-1)

.
  • Therefore, press the ENTER button.

Applying Formula to Remove Characters after a Specific Character

  • 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 shown in the picture below.

Showing Result by Applying Formula with Excel VBA to Remove Characters after a Specific Character

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 to this article and practice all the methods. 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 Roy
Mrinmoy Roy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo