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

While working with a large dataset, sometimes you may need to remove some certain part of the data. To do this manually is really time consuming and naive way to do. In Excel, you can remove text after a certain character in three efficient ways.


Practice Download Workbook

You can download the workbook from here.


3 Easy Ways to Remove Text After Character in Excel

Here you will learn how to remove text after a certain character by utilizing Find & Replace command tool in Excel, by utilizing formulas and also by utilizing VBA code.

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,

Step 1: First select your dataset.

Step 2: Then under the Home tab, go to Find & Select -> Replace.

excel remove text after character by find & replace

Step 3: From the pop-up Find and Replace box, beside the Find what label, write the character after which you want to remove the text and put an Asterisk (*) symbol after it.

Step 4: Keep the Replace with box empty.

Step 5: Press Replace All.

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.

finding text with asterisk

Step 4: It will remove all the text after comma (,) in your dataset.

removing all the text after comma

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


2. Insert an Excel Formula to Remove Text after Character

Unlike the above method, using 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 up several ways to delete the text after several specific points of the string.

i. Remove All Text after a Character

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,

Step 1: In the cell beside your dataset, write the above formula.

Step 2: Press Enter.

It will remove all the text after a certain character.

formula to delete text after string

Step 3: Drag the row down using Fill Handle to apply the formula to the rest of the dataset.

dragging down the row to apply the formula

  • Explanation:

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 B4) and passes it to the LEFT function which extracts the corresponding number of the character from the start of the string in the cell.


ii. 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 of removing text after N-th occurrence of a character are given below,

Step 1: In the cell beside your dataset, write the above formula.

Step 2: Press Enter.

It will remove all the text after a certain character.

removing text after nth occurrence

Step 3: Drag the row down using Fill Handle to apply the formula to the rest of the dataset.

dragging down the row to apply the formula

  • Explanations:

To understand more, check the above example where we want to remove everything after the 2nd comma (,) in the B4 cell so we SUBSTITUTE it with a hash (#) symbol.

SUBSTITUTE(B4, ",", "#", 2)

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 (,).

FIND("#", SUBSTITUTE(B4, ",", "#", 2)) -1

Next, it passes the value to the LEFT function with the instruction of pulling the character before the unique character (#).

LEFT(B4, FIND("#", SUBSTITUTE(B4, ",", "#", 2)) -1)

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


iii. Remove Text after the 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,

Step 1: In the cell beside your dataset, write the above formula.

Step 2: Press Enter.

It will remove all the text after a certain character.

removing text after last occurrence of character

Step 3: Drag the row down using Fill Handle to apply the formula to the rest of the dataset.

dragging down the row to apply the formula

  • Explanations:

To understand more, check the above example where we want to keep only the name of the cricketer, not the profession.

So first, we have to find out how many commas there are in the original text. So, we replace each comma with nothing (“”) with the SUBSTITUTE function and pass the output to the LEN function:

LEN(SUBSTITUTE(B4, ",",""))

Then subtract the resulting number from the total length of the original string to get the total number of commas (,) present in the string (which is also the ordinal number of the last comma).

LEN(B4) - LEN(SUBSTITUTE(B4, ",",""))

Next, you are already familiar with the FIND(SUBSTITUTE()) formula from the above discussion which was used to get the position of the last comma (,) in the string which we got from the above-discussed LEN(SUBSTITUTE()) formula. 

FIND("#", SUBSTITUTE(B4, ",", "#", LEN(B4) - LEN(SUBSTITUTE(B4, ",",""))))

Next, we just subtract the resulting output from the length of the original data to remove all the text.

LEFT(B4, FIND("#", SUBSTITUTE(B4, ",", "#", LEN(B4) - LEN(SUBSTITUTE(B4, ",","")))) -1)

It will produce the dataset of the cricketer’s name without the professions.

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


Similar Readings


3. Embed VBA Code to Remove Text after Character 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 do the job done.

Step 1: Press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.

turning on the visual basic editor

Step 2: From the menu bar, click Insert -> Module.

inserting module to write coding

Step 3: 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

Your code is now ready to run.

Step 4: 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.

Step 5: Then navigate to Developer -> Macros.

turning on the macros to run code

Step 6: Select the Macro name remove_text_after_character -> Run

selecting related macro file to run

It will show you the result of removing text after a character in Excel.

truncated text from VBA code

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


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 regarding the topic.


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

ExcelDemy
Logo