How to Remove Text After Character in Excel (4 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

While working with a large dataset, sometimes you may need to remove some 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.


Download Practice Workbook

You can download the workbook from here.


4 Easy Ways to Remove Text After Character in Excel

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 Find & Replace command tool, different functions, Flash Fill feature and also by utilizing VBA code.

Excel Remove Text After Character


1. Use Find & Replace Command to Delete Text After Character

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.

Ways to Remove Text After Character in Excel

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

Use Find & Replace Command to Delete Text After Character in Excel

  • Afterward, a pop-up will open to confirm your replacements.
  • Then, click on OK.

  • Thus, it will remove all the text after comma (,) in your dataset.

Read More: How to Remove Text After Character in Excel (3 Ways)


2. Apply Excel LEFT Function to Remove 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 produce 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 Delete 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 Fill Handle to apply the formula to the rest of the dataset.

Apply Excel LEFT Function to Remove Text After Character

  • Thus, it will remove all the text after a certain character.

Delete All Text After a Character Using LEFT Function in Excel

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

2.2 Remove 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.

Remove Text After N-th Occurrence of a Character in Excel

  • Finally, it will give you the result of the truncated dataset without all the text after comma (,).

🔎 How Does the Formula Work?

  • In the beginning, the SUBSTITUTE function will substitute 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 (#).

2.3 Remove 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 of 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 Fill Handle to apply the formula to the rest of the dataset.

Remove Text After Last Occurrence of a Character in Excel

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

Read More: How to Remove Text From Excel Cell (9 Easy Ways)


3. Utilize 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.

Utilize Flash Fill Feature to Remove Text After a Character in Excel

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


4. Remove 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.

Remove Text After Character Using VBA in Excel

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

Read More: How to Remove Specific Text from a Column in Excel (8 Ways)


Practice Section

In this section, we are giving you the dataset to practice on your own and learn to use these methods.

Practice Section


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. And visit ExcelDemy for many more articles like this. Thank you!


Related Articles

Sanjida Ahmed

Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

2 Comments
  1. 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.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo