How to Truncate Text After Character in Excel (4 Suitable Ways)

In this article, we will learn how to truncate text after a character in Excel. Sometimes we need to truncate any cell information dependent on special characters. We can easily do this task in Excel. So, without any delay, let’s start the discussion.

Practice Download Workbook

To practice by yourself, download the following workbook

4 Suitable Ways to Truncate Text After Character in Excel

We will use a dataset of players’ information to demonstrate the methods. In a single cell, there is information about their First Name, Last Name and the type of sports he plays. We want to truncate the cell’s information after a special character. We will show you 4 suitable ways of truncating text after a character in Excel.

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

Using Find & Replace command, you can easily truncate text after a special character. Here, letter by letter Excel will search for the character. And after finding the character, replace the remaining letters as null. Let’s see the steps to truncate text after a special character in Excel.

STEPS:

• Firstly, copy the data by pressing Ctrl + C.
• We have copied from B5 to B12 cells.
• After that, paste the data into another column.
• We have pasted the copied data in the Result column by pressing Ctrl + V.

• In the following step, select the data from the Result column.
• After that, go to the Home tab and click on the Find & Select option of the Editing group from the ribbon.
• As a result, a drop-down menu will come out and select Replace from here.

• Therefore, the Find and Replace window will come out.
• Now, inside the Replace tab, look for Find what label.
• After that, write the character after which you want to remove the text.
• And put an Asterisk (*) symbol after it.
• We want to truncate text after the (–) symbol.
• So, we put -* in the Find what label.
• Keep the Replace with box empty and click Replace All.

• As a result, a floating window will be shown.
• In this window, there is information about replacements.
• Press OK.

• Finally, we have been able to truncate text after the (–) character.

Read More: How to Truncate Text from Left in Excel (5 Suitable Ways)

2. Insert Excel Formula to Truncate Text After Character

Inserting Formulas we can simply truncate text after character. Here we will use 4 formulas. Let’s follow the steps to learn the methods.

2.1. Truncate Text Using TEXTBEFORE Function

The TEXTBEFORE function is a function that we get with Excel 365. This function finds specific characters. And after finding, it returns the text presented before that character.

Let’s see the steps to know how to truncate using the TEXTBEFORE function in Excel.

STEPS:

• Firstly, select the C5 cell and write the following formula:

• Press Enter and you can see the truncated data.

• After that, drag the formula from C5 to C12 cells with the help of AutoFill.

• Finally, you can observe the result in the Result column.
• We have been able to truncate text after the (–) character.

2.2. Remove All Texts After a Character

Using the LEFT and SEARCH functions, we can easily truncate text after a special character. Here we will remove the remaining texts after the character. Let’s see the steps to learn the method.

STEPS:

• Firstly, select the C5 cell and write the following formula:

• Press Enter and you can see the truncated data.

• After that, drag the formula from C5 to C12 cells with the help of AutoFill.

• Finally, you can observe the result in the Result column.
• We have been able to truncate text after the (–) character.
• Here, we have removed all remaining text after finding the first (–) character.

2.3. Truncate Text After N-th Occurrence of a Character

Sometimes we may need to truncate the texts after finding the n-th occurrence of a character. Using LEFT, FIND and SUBSTITUTE functions, we can easily do this task. Let’s see the steps to learn the method.

STEPS:

• Firstly, select the C5 cell and write the following formula:

• Press Enter.
• As a result, you can see the truncated data after 2nd occurrence of our special character.

• After that, drag the formula from C5 to C12 cells with the help of AutoFill.

• Finally, you can observe the result in the Result column.
• Here, we have been able to truncate text after 2nd occurrence of the (–) character.

2.4. Erase Text After Last Occurrence of a Character

Sometimes we may need to truncate the texts after finding the last occurrence of a character. Using LEFT, FIND, LEN and SUBSTITUTE Functions, we can easily do this task. Let’s see the steps to learn the method.

STEPS:

• Firstly, select the C5 cell and write the following formula:

• Press Enter and you can see the truncated data.

• After that, drag the formula from C5 to C12 cells with the help of AutoFill.

• Finally, you can observe the result in the Result column.
• Here, we have been able to truncate text after the last occurrence of the (–) character.

Read More: How to Truncate Text from Right in Excel (5 Easy Methods)

3. Trim Text After Character Using Flash Fill Feature in Excel

Using Flash Fill Feature, you can easily truncate text after a special character. Let’s see the steps to truncate text after a special character in Excel.

STEPS:

• Firstly, select the C5 cell.
• Then, type your desired trimmed text here.
• We want to trim after the first occurrence of the (–) character.
• Therefore, we have typed Christian.

• Similarly, type Josh on the next cell.

• After that, go to the Home tab and click on Fill of the Editing group from the ribbon.
• As a result, a drop-down menu will come out and select Flash Fill from here.

• Finally, You can observe the result in the Result column.
• Here, we have been able to truncate text after 1st occurrence of the (–) character.

Read More: How to Stop Excel from Truncating Text (3 Easy Methods)

4. Utilize User-Defined Function to Remove Text After Character

We can also build a function and do the task of removing text after a special character. Let’s follow the steps to learn the method.

STEPS:

• First, you have to select Visual Basic from the Developer tab.

• After that, Microsoft Visual Basic for Applications window will open up.
• Then, select Module from the Insert tab.

• Now, write the following code in the module:

Function Truncate(Cell As Range, Character As String)
Dim Length As Integer
Length = Len(Cell)
For k = Length To 1 Step -1
If Mid(Cell, k - 1, 1) = Character Then
Truncate = k - 1
Exit Function
End If
Next k
End Function

• Now, return to the dataset.
• Then, select the C5 cell and write the following formula:

• Press Enter and you can see the truncated data after the last occurrence of the (–) character.

• After that, drag the formula from C5 to C12 cells with the help of AutoFill.

• Finally, you can observe the result in Result column.
• Here, we have been able to truncate text after the last occurrence of the (–) character.

Conclusion

In this article, we have demonstrated how to truncate text after a character in Excel. There is a practice workbook at the beginning of the article. Go ahead and give it a try. To read similar articles, check out the ExcelDemy website. Lastly, please use the comment section below to post any questions or make any suggestions you might have.

Related Articles

• How to Truncate Decimal in Excel (6 Easy Ways)
• Truncate Date in Excel (4 Simple Methods)
• How to Truncate Numbers in Excel (8 Suitable Methods)