Mrinmoy Roy
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.
- 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.
- 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.
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.
- 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.
- When you are done with all the steps above, you will see your texts trimmed off like in the picture below.
Read More: How to Remove Last Character in Excel(Easiest 6 Ways)
Similar Readings:
- How to Remove Single Quotes in Excel (6 Ways)
- Remove First 3 Characters in Excel (4 Methods)
- How to Remove Hidden Double Quotes in Excel (6 Easy Ways)
- Remove Semicolon in Excel (4 Methods)
- How to Remove Apostrophe in Excel (5 Easy Methods)
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.
- 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.
- 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)
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.
- 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.
- 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.
- 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.
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.
