While working with a large dataset, sometimes you may need to remove certain parts of the data. To do this manually is really time-consuming and naive. In Excel, you can remove text after a certain character in 4 efficient ways.
Suppose we have a dataset containing some data of the name and profession of some people and we want to remove the profession from the dataset or just have the first name.
Now, using this dataset we will show you how to remove text after a certain character by using the Find & Replace command tool, different functions, the Flash Fill feature, and also by utilizing VBA code.
1. Using Find & Replace Command to Remove Text After Character in Excel
We will learn how to remove text after a certain character by using the Find & Replace feature in Excel. In Excel, this is the easiest way to delete any text after a specific character.
The steps to do that are given below.
Steps:
- Firstly, select your dataset.
- Then, under the Home tab, go to Find & Select >> click on Replace.
- Now, the Find and Replace box will open.
- After that, beside the Find what label, write the character after which you want to remove the text and put an Asterisk (*) symbol after it.
- Next, keep the Replace with box empty.
- Lastly, press Replace All.
- However, to understand more, check the following picture, where we want to remove everything after comma (,), so we write the symbol comma (,) and put an Asterisk (*) symbol along with it.
- Afterward, a pop-up will open to confirm your replacements.
- Then, click on OK.
- Thus, it will remove all the text after the comma (,) in your dataset.
2. Applying Excel LEFT Function to Delete Text After Character
Unlike the above method, using the LEFT function in formulas to remove strings after a specific character in Excel is a more efficient and reliable method. Implementing formulas produces the more unchanged and controllable result of your dataset.
Using formulas opens several ways to delete the text after several specific points of the string.
2.1 Deleting All Text After a Character
Firstly, we will use the LEFT and SEARCH functions to delete all text after a character in Excel.
Generic Formula:
=LEFT(cell,SEARCH("character",cell)-1)
Here,
- cell = cell reference number of your data.
- character = the character after which you want to remove the text.
If you want to remove all text after a character, then just follow the given steps below.
Steps:
- In the beginning, in the cell beside your dataset, write the following formula.
=LEFT(B5,SEARCH(",",B5)-1)
- Then, press Enter and drag the row down using the Fill Handle to apply the formula to the rest of the dataset.
- Thus, it will remove all the text after a certain character.
🔎 How Does the Formula Work?
- Firstly, the SEARCH function identifies the position of the character from the cell (in our case, it identifies the position of comma (,) in the data of Cell B5)
- After that, this function passes it to the LEFT function which extracts the corresponding number of the character from the start of the string in the cell.
Read More: How to Remove Text between Two Characters in Excel
2.2 Removing Text After N-th Occurrence of a Character
In the situation, when you have to remove text after a certain character, but that character occurs multiple times in the string, then you need to use the following formula,
Generic Formula:
=LEFT(cell,FIND("#",SUBSTITUTE(cell,"character","#", n))-1)
Here,
- cell = cell reference number of your data.
- character = the character after which you want to remove the text.
- n = the character’s occurrence after which to remove text.
- # = to introduce a new character that is not present anywhere in the source dataset (you can use any character that you want but keep in mind that, that character has to be unique).
The steps for removing text after the N-th occurrence of a character are given below.
Steps:
- To start with, In the cell beside your dataset, write the following formula and press Enter.
=LEFT(B5,FIND("#",SUBSTITUTE(B5,",","#", 2))-1)
- Then, drag down the Fill Handle tool for the rest of the cells.
- Moreover, to understand it clearly, check the above example where we want to remove everything after the 2nd comma (,) in Cell B5.
- Finally, it will give you the result of the truncated dataset without all the text after the comma (,).
🔎 How Does the Formula Work?
- In the beginning, the SUBSTITUTE function will substitute the comma (,) in Cell B5 with a hash (#) symbol.
- Then, pass the output to the FIND function which identifies the position of the hash (#). After identifying the position of the character, the FIND function then removes 1 to know the number preceding the character (,).
- Lastly, it passes the value to the LEFT function with the instruction of pulling the character before the unique character (#).
Read More: How to Remove Everything After a Character in Excel
2.3 Removing Text After Last Occurrence of a Character
In case of deleting all the text after a certain character that occurred multiple times, you need to implement the following formula,
Generic Formula:
=LEFT(cell,FIND("#",SUBSTITUTE(cell,"character","#",LEN(cell)-LEN(SUBSTITUTE(cell,"character",""))))-1)
Here,
- cell = cell reference number of your data.
- character = the character after which you want to remove the text.
- # = to introduce a new character that is not present anywhere in the source dataset (you can use any character that you want but keep in mind that, that character has to be unique).
The steps for removing text after the last occurrence of a character are given below.
Steps:
- Firstly, In the cell beside your dataset, write the following formula.
=LEFT(B5,FIND("#",SUBSTITUTE(B5,",","#",LEN(B5)-LEN(SUBSTITUTE(B5,",",""))))-1)
- After that, press Enter and drag the row down using the Fill Handle to apply the formula to the rest of the dataset.
- Thus, it will remove all the text after a certain character.
🔎 How Does the Formula Work?
- To start with, we have to find out the number of commas present in the given cell.
- So, we will substitute each comma using the SUBSTITUTE function with nothing (“”) and then pass the output into the LEN function.
- The total number of commas (,) in the string will then be calculated by deducting the resultant number from the string’s overall length.
- Next, as you already know the FIND(SUBSTITUTE()) formula from the above discussion using which we determined where the final comma should be (,) in the string.
- Finally, we just subtract the resulting output from the length of the original data to remove all the text.
3. Using Flash Fill Feature to Remove Text in Excel
You can also utilize the Flash Fill feature to remove text after a character in Excel. To do that, you have to introduce Excel to some patterns it has to follow.
Here are the steps to do that.
Steps:
- In the beginning, manually type “Josh” (our expected result) in Cell C5 as it is the adjacent cell of our data.
- Similarly, type “Joe” in Cell C6.
- Then, select cell range C5:C9.
- After that, go to the Home tab >> expand Editing >> click on Fill >> select Flash Fill.
- However, you can also press Ctrl + E after selecting the cell range to use the Flash Fill feature.
- Thus, following the pattern all the cells will remove text after a character.
Read More: How to Remove Letters from Cell in Excel
4. Removing Text After Character Using VBA in Excel
If you are an experienced Excel user, then this method is only for you. Using VBA to remove text after a character is the quickest way to get the job done.
Steps:
- Firstly, press Alt + F11 on your keyboard or go to the tab Developer >> click on Visual Basic to open Visual Basic Editor.
- Then, from the menu bar, click on Insert >> select Module.
- Next, copy the following code and paste it into the code window.
Sub remove_text_after_character()
Dim range As range
Dim cell As range
Set range = Application.Selection
For Each cell In range
cell.Offset(0, 1).Value = Left(cell, InStr(cell, ",") - 1)
Next cell
End Sub
Code Breakdown
- To start with, we created a Sub Procedure named remove_text_after_character.
- After that, we declared range as range and cell as cell.
- Then, we set the range as the selected cell range.
- Lastly, we used a For loop for each cell and used the LEFT function to remove text after a character (comma (,)).
- After that, click on the Save button to save the code.
- Now, switch to the worksheet of interest, select the desired range, and make sure to keep the column next to it blank because the macro will display the result here.
- Then, navigate to Developer >> click on Macros.
- Lastly, select the macro name remove_text_after_character >> click on Run.
- Finally, it will show you the result of removing text after a character in Excel.
Practice Section
In this section, we are giving you the dataset to practice on your own and learn to use these methods.
Download Practice Workbook
You can download the workbook from here.
Conclusion
In this article, we have learned how to remove text after a certain character in Excel. We find out how to do that not only from the 1st occurrence of the character but also the last and Nth occurrences. I hope that this article has been very beneficial to you. Feel free to ask any questions if you have any questions regarding the topic. Have a nice day!
i can’t thank you enough!! you’ve saved me mega hours of manually deleting words after a semicolon (;) in million cells that’ve I’ve been doing for ages!
Glad that we could help you. Don’t hesitate to ask if you have any more Excel related problems. Here, our experts will be happy to assist you.