Mrinmoy
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:
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.
❷ Press CTRL + H to open up the Find and Replace dialog box.
❸ Enter
,*❹ Leave the Replace with box blank.
❺ Hit the 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 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)❸ Finally press the ENTER button.
❹ 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:
␥ 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:
- 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)
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.
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.
❸ 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.
After that return to the data table and
❺ Select cell C5 and enter the formula:
=LEFT(B5,CharLoc(B5,",")-1)❻ 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 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 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.
