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

Get FREE Advanced Excel Exercises with Solutions!

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.

excel truncate text after character


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.

Use Find & Replace Command for Truncating Text after Character

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

Use Find & Replace Command for Truncating Text after Character

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

Use Find & Replace Command for Truncating Text after 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:
=TEXTBEFORE(B5,"-")
  • Press Enter and you can see the truncated data.

Truncate Text Using TEXTBEFORE Function

  • 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:
=LEFT(B5,SEARCH("-",B5)-1)
  • Press Enter and you can see the truncated data.

Remove All Texts After a Character

The SEARCH function identifies the position of the character from the cell and passes it to the LEFT Function which extracts the corresponding number of the character from the start of the string in the cell.
  • 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:
=LEFT(B5,FIND("#",SUBSTITUTE(B5,"-","#",2))-1)
  • Press Enter.
  • As a result, you can see the truncated data after 2nd occurrence of our special character.

Truncate Text after N-th Occurrence of a Character

Here, the SUBSTITUTE function changes the 2nd () character with the (#) character and the FIND function looks for the (#) character and the LEFT function extracts everything to the left side 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 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:
=LEFT(B5,FIND("#",SUBSTITUTE(B5,"-","#",LEN(B5)-LEN(SUBSTITUTE(B5,"-",""))))-1)
  • Press Enter and you can see the truncated data.

Erase Text after Last Occurrence of a Character

Here, LEN function is used to calculate the length of the text and also text without special characters. So, the number of () characters is calculated.The Find function finds out the last () character. Then, by SUBSTITUTION function, we have replaced the last () with (#) and the LEFT Function extracted the text before the (#) 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 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.

Trim Text after Character Using Flash Fill Feature

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

Utilize User-Defined Function to Remove Text after Character

  • 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

Utilize User-Defined Function to Remove Text after Character

  • Now, return to the dataset.
  • Then, select the C5 cell and write the following formula:
=LEFT(B5,Truncate(B5,"-")-1)
  • 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.

Utilize User-Defined Function to Remove Text after 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

Sudipta Chandra Sarker

Sudipta Chandra Sarker

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel. Here I will be posting articles related to this. My educational degree is BSc in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology, Bangladesh. I have a great interest in research and development. I always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo