Excel: remove characters, words, numbers from text strings

If you have a list of text strings in Excel that contain unwanted characters, numbers, or symbols, you might need to remove certain elements based on their position or type. Whether it's trimming characters from the left, right, or middle, or eliminating specific characters and numbers, Excel offers several ways to clean up your data. This tutorial provides step-by-step methods using formulas, User Defined Functions (UDFs), and built-in features to remove unwanted characters, words, and numbers from your text strings with ease.

 Remove characters from left, right or middle of the text strings

It may be a common job for most of us to remove some characters from left, right or middle of the text strings in Excel worksheets. This section will introduce some quick and easy tricks for solving this task.

1.1 Remove first n characters from text strings

If you need to remove first n characters from a list of text strings, the following methods may do you a favor.

 By using formulas

Normally, to delete the characters from beginning of the text strings, you can use either the REPLACE function or a combination of RIGHT and LEN functions.

REPLACE function to remove first N characters:

• "string": The text string that you want to remove characters from;
• "num_chars": The number of characters you want to remove.

For example, to remove the first 2 characters from the cells, please use the below formula, and then drag the fill handle to copy the formula to other cells, see screenshot:

=REPLACE(A4, 1, 2, "")

RIGHT and LEN functions to remove first N characters:

• "string": The text string that you want to remove characters from;
• "num_chars": The number of characters you want to remove.

To remove first 2 characters from cells, please apply the following formula:

=RIGHT(A4,LEN(A4)-2)

 By using User Defined Function

To remove the first n characters from cells, you can also create a User Defined Function to solve this task. Please do with the following steps:

1. Hold down the "Alt + F11" keys to open the "Microsoft Visual Basic for Applications" window.

2. Click "Insert" > "Module", and paste the following code in the Module Window.

VBA code: Remove first n characters from text strings

Function removeFirstx(rng As String, cnt As Long)
'Updateby Extendoffice
removeFirstx = Right(rng, Len(rng) - cnt)
End Function

3. Then go back to the worksheet, and then enter this formula: "=removefirstx(A4,2)" into a blank cell, and then drag the fill handle down to get the results as you need, see screenshot:

Note: In this formula: "A4" is the cell that you want to remove characters from; The number "2" indicates the number of characters you would like to remove from the beginning of the text string.

1.2 Remove last n characters from text strings

To remove specific number of characters from the right side of text strings, you can also use a formula or User Defined Function.

 By using formula

To remove last n characters from the text strings, you can use a formula based on the LEFT and LEN functions.

LEFT and LEN functions to remove last N characters:

• "string": The text string that you want to remove characters from;
• "num_chars": The number of characters you want to remove.

To delete 3 characters from the end of the text strings, please use this formula, and then drag the fill handle to copy the formula to other cells, see screenshot:

=LEFT(A4, LEN(A4) - 3)

 By using User Defined Function

A User Defined Function can also help you remove the last n characters from a list of cells. Follow these steps:

1. Hold down the "Alt + F11" keys to open the "Microsoft Visual Basic for Applications" window.

2. Click "Insert" > "Module", and paste the following code in the Module Window.

VBA code: Remove last n characters from text strings

Function removeLastx(rng As String, cnt As Long)
'Updateby Extendoffice
removeLastx = Left(rng, Len(rng) - cnt)
End Function

3. Then return to the worksheet, and enter this formula: "=removelastx(A4,3)" into a blank cell, and then drag the fill handle down to get the results as you need, see screenshot:

Note: In this formula: "A4" is the cell that you want to remove characters from; The number "3" indicates the number of characters you would like to remove from the end of the text string.

1.3 Remove first, last n characters or certain position characters by using a powerful feature

It may be painful for you to remember various of formulas, to remove characters from left, right or a specific position of the text strings, "Kutools for Excel" supports a powerful feature – "Remove by Position". With this small tool, you can handle these tasks with a few clicks without remembering any formulas.

1. Select the cells you want to remove characters, and then click "Kutools" > "Text" > "Remove by Position", see screenshot:

2. In the "Remove by Position" dialog box, please do the following operations:

2.1 Remove first n characters from cells:

• In "Numbers" textbox, type the number of characters you want to remove from strings. In this example, I will remove first 2 characters.
• Select "From left" option in" Position" section.
• Then, click "Ok" or "Apply" button to get the result as below screenshot shown.

2.2 Remove last n characters from cells:

• In" Numbers" textbox, type the number of characters you want to remove from strings. In this example, I will remove last 3 characters.
• Select" From right" option in "Position" section.
• Then, click "Ok" or "Apply" button to get the result as below screenshot shown.

2.3 Remove n characters from specific position of cells:

If you need to remove specific number of characters from a certain position of the text strings, for instance, removing 3 characters start from the third character of the strings.

• In "Numbers" textbox, type the number of characters you want to remove from strings. In this example, I will remove 3 characters from a certain position.
• Select "Specify" option, and type the number which you want to remove characters start from in the textbox of the "Position" section. Here, I will remove characters from the third character.
• Then, click" Ok" or "Apply" button to get the result as below screenshot shown.

1.4 Remove both first n and last n characters from text strings with formula

When you need to remove some characters on both sides of text strings in Excel, you can combine the MID and LEN functions to create a formula for dealing with this task.

• "string": The text string that you want to remove characters from;
• "left_chars": The number of characters to remove from left;
• "right_chars": The number of characters to remove from right.

For example, you need to remove first 7 characters and last 5 characters from text strings at the same time, please enter the following formula into a blank cell:

=MID(A4, 7+1, LEN(A4) - (7+5))

Note: In this formula: "A4" is the cell that you want to remove characters from; The number "7" is the number of characters you want to remove from the left side; The number "5" is the number of characters you want to remove from the right side.

And then, drag the fill handle down to where you want to apply this formula, and you will get the result as below screenshot shown:

 Remove unwanted / special characters from text strings

When importing data from somewhere else to Excel, a lot of special or unwanted characters may be pasted to your worksheet. To remove theses unwanted characters such as #@$%^&, spaces, numbers, non-numeric numbers, line breaks, etc., this section will provide some useful methods to help you.

2.1 Remove some special characters from text strings

If there are some special characters such as %^&*() within the text strings, to remove this type of characters, you can apply the below three tricks.

 Remove several special characters from text strings with SUBSTITUTE function

Normally, in Excel, you can nest several SUBSTITUTE functions to replace each specific character with nothing, the generic syntax is:

• "string_cell": The cell contains the text string that you want to remove special characters from;
• "char1, char2, char3": The unwanted characters you want to remove.

Now, please copy or enter the below formula into a blank cell:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "#", ""), "&", ""), "*", ""), "%", "")

And then, drag the fill handle down to the cells where you want to apply this formula, and all the unwanted characters you specified will be removed at once, see screenshot:

Tips: If there are more characters you want to remove, you just need to nest more SUBSTITUTE functions inside the formula.

 Remove multiple special characters from text strings with User Defined Function

The above nested SUBSTITUTE functions work well if there are few special characters to be removed. However, if you have dozens of characters to remove, the formula can become too long and difficult to manage. In this case, the following User Defined Function may help you finish this task quickly and easily.

1. Hold down the "Alt + F11" keys to open the "Microsoft Visual Basic for Applications" window.

2. Click "Insert" > "Module", and paste the following code into the Module Window.

VBA code: Remove multiple special characters from text strings

Function RemoveUnwantedChars(Str As String, xchars As String)
'Updateby Extendoffice
    For Index = 1 To Len(xchars)
        Str = Replace(Str, Mid(xchars, Index, 1), "")
    Next
    RemoveUnwantedChars = Str
End Function

3. Then, close the code window and go back to the worksheet, enter this formula "=RemoveUnwantedChars(A2, $D$2)" into a blank cell where to output the result, and then drag the fill handle down to get the results as you need, see screenshot:

Note: In the above formula: "A2" is the cell that you want to remove characters from; "$D$2" contains the special characters you would like to remove (you can type any other special characters you need).

 Remove multiple special characters from text strings by using an amazing feature

If you have installed "Kutools for Excel", with its "Remove Characters" feature, you can remove all kinds of characters, such as numeric characters, alpha characters, non-printing characters…from a list of cells as you need.

1. Select the range of cells you want to remove special characters from, and then click "Kutools" > "Text" > "Remove Characters", see screenshot:

2. In the "Remove Characters" dialog box:

• Check "Custom" option under the "Remove Characters" section.
• And then enter the special characters into the textbox that you want to remove.
• Then, click "Ok" or "Apply" button to remove the characters you specified at once. See screenshot:

2.2 Remove all numbers from text strings

If you have a list of text strings which mix with numbers, letters and special characters, and now, you just want to remove all numbers and keep other characters. This section will provide some handy ways to help you.

 Remove numbers from text strings with SUBSTITUTE function

In Excel, the nested SUBSTITUTE function can help to replace all numbers with nothing, so, you can use the below formula to remove all numbers from cells:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""),0,"")

Then drag the fill handle down to the cells where you want to apply this formula, and all numbers will be deleted from the list of text strings, see screenshot:

 Remove numbers from text strings with TEXTJOIN function

If you have Excel 2019, 2021 or 365, the new TEXTJOIN function also can help to remove numbers from text strings.

Please copy the following formula into a blank cell, and then press Ctrl + Shift + Enter keys together to get the first result:

=TEXTJOIN("", TRUE, IF(ISERR(MID(A2, ROW(INDIRECT( "1:"&LEN(A2) )), 1) *1), MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1), ""))

And then copy the formula into other cells below where you want to apply this formula, see screenshot:

Note: This TEXTJOIN is only available in Excel 2019, 2021 and Office 365.

 Remove numbers from text strings with User Defined Function

Besides above two formulas, a User Defined Function also can do you a favor, please do with the following steps:

1. Hold down the" Alt + F11" keys to open the "Microsoft Visual Basic for Applications" window.

2. Click "Insert "> "Module", and paste the following code into the Module Window.

VBA code: Remove numbers from text strings

Function RemoveNumbers(Txt As String) As String
'Updateby Extendoffice
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "[0-9]"
RemoveNumbers = .Replace(Txt, "")
End With
End Function

3. Then, close and exit the code window, go back to the worksheet, and enter this formula: =RemoveNumbers(A2) into a blank cell, then drag the fill handle down to the cells where you want to apply this formula, see screenshot:

 Remove numbers from text strings with a handy option

If you are tired with the complicated formulas, now, let me show you an easy tool – "Kutools for Excel"’s "Remove Characters". With this handy feature, you can accomplish this task with only a few clicks.

1. Select the range of cells where you want to remove numbers, and then click "Kutools" > "Text" > "Remove Characters".

2. In the "Remove Characters" dialog box, please do the following operations:

• Check "Numeric" option under the "Remove Characters" section.
• Then, click "Ok" or "Apply" button to remove the numbers immediately. See screenshot:

2.3 Remove non-numeric characters from text strings

For removing all non-numeric characters and only keep the numbers from text strings, this section will talk about some ways for solving this task in Excel.

 Remove non-numeric characters from text strings with formula in Excel 2016 and earlier versions

If you are using Excel 2016 or earlier versions, you should apply a complex formula to achieve this job, please copy or enter the below formula into a blank cell:

=SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1)) * ROW(INDIRECT("1:"&LEN(A2))), 0), ROW(INDIRECT("1:"&LEN(A2))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(A2)))/10)

And then copy the formula into other cells below where you want to apply this formula, see screenshot:

Note: If the numbers in a text string start with 0, the 0 will be lost.

 Remove non-numeric characters from text strings with TEXTJOIN function in Excel 2019, 2021, 365

The above formula may be too difficult to understand for most of us. If you have Excel 2019, 2021 or 365, there is a neat formula which can help you.

Please copy or enter the following formula into a blank cell, and press "Ctrl + Shift + Enter" keys together to get the first correct result:

=TEXTJOIN("",TRUE,IFERROR(MID(A2,ROW(INDIRECT("1:100")),1)+0,""))

Then, drag the fill handle down to the cells where you will apply this formula, and you will get the result as below screenshot shown:

Note: With this formula, you can see the leading 0s will be kept since the numbers are returned as text.

 Remove non-numeric characters from text strings with User Defined Function

Of course, you can also create your own User Defined Function with a simpler syntax, please do as this:

1. Hold down the "Alt + F11" keys to open the "Microsoft Visual Basic for Applications" window.

2. Click "Insert" > "Module", and paste the following code into the Module Window.

VBA code: Remove non-numeric characters from text strings

Function Removenonnumeric(str As String) As String
'Updateby Extendoffice
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "[^0-9]"
        Removenonnumeric = .Replace(str, "")
    End With
End Function

3. Then, close and exit the code window, go back to the worksheet, and enter this formula: =Removenonnumeric(A2) into a blank cell, then drag the fill handle down to the cells where you want to apply this formula, only numbers will be extracted as below screenshot shown:

 Remove non-numeric characters from text strings with a simple feature

To delete non-numeric characters in a range of cells directly, "Kutools for Excel"’s "Remove Characters" utility can get it done with only few clicks.

1. Select the range of cells from which you want to remove non-numeric characters, and then click "Kutools" > "Text" > "Remove Characters".

2. In the "Remove Characters" dialog box, please set the following operations:

• Check "Non-numeric" option under the "Remove Characters" section.
• Then, click "Ok" or "Apply" button to remove all non-numeric characters immediately. See screenshot:

2.4 Separate text and numbers from one cell into two columns

Sometimes, you may want to extract the text and numbers from text strings into two separated columns, with the help of the following methods, you can finish this task quickly and easily.

 Separate text and numbers from one cell into two columns with User Defined Function

By using the following User Defined Function, you can extract the text and numbers at once, please do with the below steps:

1. Hold down the "Alt + F11" keys to open the "Microsoft Visual Basic for Applications" window.

2. Click "Insert" >" Module", and paste the following code into the Module Window.

VBA code: Separate text and numbers from text strings into two columns

Function SplitText(pWorkRng As Range, pIsNumber As Boolean) As String
'Updateby Extendoffice
Dim xLen As Long
Dim xStr As String
xLen = VBA.Len(pWorkRng.Value)
For i = 1 To xLen
    xStr = VBA.Mid(pWorkRng.Value, i, 1)
    If ((VBA.IsNumeric(xStr) And pIsNumber) Or (Not (VBA.IsNumeric(xStr)) And Not (pIsNumber))) Then
        SplitText = SplitText + xStr
    End If
Next
End Function

3. Then, close and exit the code window, go back to the worksheet, and enter this formula: =SplitText(A2,FALSE) into a blank cell, and then drag the fill handle down to the cells that you want to fill this formula to get all the text, see screenshot:

4. And then, go on typing this formula: =SplitText(A2,TRUE) into another cell and drag the fill handle down to the cells that you want to fill this formula to get the numbers, see screenshot:

 Separate text and numbers from one cell into two columns with an easy feature

If you have "Kutools for Excel", its "Split Cells" utility can help you to split cells into multiple columns or rows based on any separators, specified width or text and number.

1. Select the range of cells you want to split, and then click "Kutools" > "Merge & Split" > "Split Cells", see screenshot:

2. In the "Split Cells" dialog box, select "Split to Columns" option under the "Type" section, and then check "Text and number" from the "Split by" section, see screenshot:

3. Then, click "Ok" button, and another" Split Cells" dialog box will be pop-up, select a cell to output the separated text and numbers, and then click the "OK" button. Now, you can see the text and numbers in selected cells are split into two columns at once as below demo shown:

2.5 Remove line break characters from text strings

A line break is something that allows you to have multiple lines in the same cell in Excel. Sometimes, when you copy data from the website or separate your cell contents with "Alt + Enter" keys manually, you will get the line breaks or carriage returns. In certain cases, you may want to delete the line breaks to make the cell content appear as one line, as shown in the screenshot below. Here, I will introduce some ways for solving this task in Excel.

 Remove line break characters from text strings with Find and Replace feature

In Excel, you can use the" Find and Replace" feature to remove line breaks, please do as this:

1. Select the data range from which you want to remove the line breaks.

2. Then, click "Home" > "Find & Select" > "Replace" (or press "Ctrl + H" keys) to go the" Find and Replace" dialog box, see screenshot:

3. In the pop-up "Find and Replace" dialog box, please do the following operations:

• Place the cursor in the "Find what" field and press "Ctrl + J" on the keyboard, you may not see anything, but the line break character has been inserted.
• In the "Replace With" field, leave this field empty to just delete the line breaks or press the" Space ba"r once to replace the line breaks with blanks.

4. Then, click "Replace All" button, all line breaks in the selected cells will be deleted or replaced with blanks at once. See screenshot:

 Remove line break characters from text strings with SUBSTITUTE function

You can also create a formula based on the SUBSTITUTE and CHAR functions to remove the line breaks from the text strings.

Please apply the below formula to get the result:

=SUBSTITUTE(A2,CHAR(10),"")

Tips: The SUBSTITUTE function finds and replaces the CHAR(10) character which represents the line break character with nothing. If you want the result to be separated by a comma and space, you can use the below formula:

=SUBSTITUTE(A2,CHAR(10),", ")

 Remove line break characters from text strings with VBA code

If you’re comfortable using VBA code, here also provides a code for you, please do with the following steps:

1. Hold down the "Alt + F11" keys to open the "Microsoft Visual Basic for Applications" window.

2. Click "Insert" > "Module", and paste the following code into the Module Window.

VBA code: Remove line breaks from text strings

Sub RemoveCarriage()
'Updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    Rng.Value = Replace(Rng.Value, Chr(10), "")
Next
End Sub

3. Then, press "F5" key to run this code, and a prompt box will appear. Select the range from which you want to remove the line breaks, as shown in the screenshot below:

4. And then, click "OK" button, all line breaks will be deleted from the selected data range.

 Remove line break characters from text strings with a smart option

Here, "Kutools for Excel"’s "Remove Characters" feature also can help you to remove line breaks with ease.

1. Select the range of cells where you want to remove line breaks, and then click "Kutools" >" Text" > "Remove Characters".

2. In the "Remove Characters" dialog box, please set the following operations:

• Check "Non-printing" option under the "Remove Characters" section.
• Then, click "Ok" or "Apply" button to remove all line breaks from the selected data range. See screenshot:

2.6 Remove spaces (leading, trailing, extra or all spaces) from text strings

Coping and pasting text from an external source to an Excel worksheet often brings some annoying leftover whitespaces, it will be tedious to remove the leading, trailing or other extra spaces manually. Fortunately, Excel provides some easy tricks for dealing with this task.

 Remove extra spaces (leading, trailing, excess) from text strings with TRIM function

In Excel, to delete the leading, trailing and extra spaces from text strings, the simple TRIM function can help you. This function deletes all spaces except for single spaces between words.

Please enter the below formula into a blank cell:

=TRIM(A2)

And then drag the fill handle down to copy the formula for other cells, now, you can see all the leading, trailing spaces and extra spaces among the words are removed at once from the screenshot:

 Remove all spaces from text strings

If you want to remove all whitespaces from text strings, the following SUBSTITUTE function and Find & Replace feature can do you a favor.

By using SUBSTITUTE function

You can use the SUBSTITUTE function to replace all spaces with nothing, please apply the below formula into a blank cell:

=SUBSTITUTE(A2," ","") 

Then, drag the fill handle down to copy this formula to other cells you need, and all spaces will be deleted as below screenshot shown:

By using Find and Replace feature

In fact, the "Find & Replace" feature in Excel also can help to get rid of all spaces from selected cells, please do with the following steps:

1. Select the data range from which you want to remove all spaces.

2. Then, click "Home" >" Find & Select" > "Replace" (or press "Ctrl + H" keys) to go to the "Find and Replace" dialog box, in the opened "Find and Replace" dialog box, please do the below operations:

• Press "Space bar" in the" Find what" field;
• In the "Replace With" field, leave this field empty.

3. And then, click "Replace All" button, all whitespaces in the selected cells will be deleted at once. See screenshot:

 Remove all kinds of spaces from text strings with a powerful feature

"Kutools for Excel" has a powerful feature – "Remove Spaces", with this utility, you can not only remove leading spaces, trailing spaces, excess spaces but also all spaces from selected ranges in one dialog box, which will enhance your work productivity.

1. Select the data range that you will remove spaces from, and then click "Kutools" > "Text" > "Remove Spaces". See screenshot:

2. In the "Remove Spaces" dialog box, select the type of space you want to remove from the "Spaces Type":

• Remove the leading spaces, please select the "Leading spaces" option;
• Remove the trailing spaces, please select the "Trailing spaces" option;
• Remove the leading spaces and the trailing spaces at once, please select the "Leading & Trailing spaces" option;
• Remove all extra spaces, please select the "All excess spaces" option;
• Remove all spaces, please select the "All spaces" option.

3. Then, click "Ok" or "Apply" button, you will get the result you need.

 Remove characters / text before or after specific character

In this section, I will introduce some operations for removing the text or characters before or after the first, last or nth occurrence of a specific character.

3.1 Remove text before or after the first specific character

If you want to remove the text before or after the first specific character, such as space, comma from a list of text strings as below screenshot shown, here I will post two methods for you.

 Remove text before the first specific character with a formula

To remove text or characters before the first specific character, you can create a formula based on the RIGHT, LEN and FIND functions, the generic syntax is:

• "cell": The cell reference or text string that you want to remove text from;
• "char": The specific separator that you want to remove text based on.

For instance, to remove everything before the first comma from the list strings, you should apply the following formula into a blank cell, and then drag it down to the cells you need, see screenshot:

=RIGHT(A2,LEN(A2)-FIND(",",A2))

Note:In the above formula: "A2" is the cell that you want to remove text from; "," is the specific character you want to remove text based on, you can change it to any other characters as you need.

 Remove text after the first specific character with a formula

To remove everything after the first specific character, you can use the LEFT and FIND functions to get the result, the generic syntax is:

• "cell": The cell reference or text string that you want to remove text from;
• "char": The specific separator that you want to remove text based on.

Now, please enter the below formula into a blank cell, and then drag the fill handle down to other cells where you want to apply this formula, and all the characters after the first comma will be removed at once, see screenshot:

=LEFT(A2,FIND(",",A2)-1)

3.2 Remove text before or after Nth occurrence of a character

Sometimes, the text strings contain multiple instances of a specific delimiter, you may want to remove all characters before or after a specific instance, such as the second, third or fourth as you need. To deal with this type of removing, you can use the following tricks:

 Remove text before Nth occurrence of a character with a formula

To remove the text before the Nth occurrence of a specific character, the following formula can help you, the generic syntax is:

• "cell": The cell reference or text string that you want to remove text from;
• "char": The specific separator that you want to remove text based on;
• "N": The character's occurrence before which to remove text.

For example, to remove everything before the second comma from the text strings, you should apply the below formula:

=RIGHT(A2,LEN(A2)-FIND("#",SUBSTITUTE(A2,",","#",2)))

Note:In the above formula: "A2" is the cell that you want to remove text from;" ," is the specific character you want to remove text based on, you can change it to any other characters as you need; "2" indicates the nth comma before which you want to remove text.

And then, drag the fill handle to copy the formula to other cells, see screenshot:

 Remove text after Nth occurrence of a character with a formula

To remove the text after the Nth occurrence of a specific separator, the LEFT, SUBSTITUTE and FIND functions can do you a favor. The generic syntax is:

• "cell": The cell reference or text string that you want to remove text from;
• "char": The specific separator that you want to remove text based on;
• "N": The character's occurrence after which to remove text.

Once you understand the basic syntax, please copy or enter the below formula into a blank cell:

=LEFT(A2, FIND("#", SUBSTITUTE(A2, ",", "#", 2)) -1)

Note:In the above formula: "A2" is the cell that you want to remove text from; "," is the specific character you want to remove text based on, you can change it to any other characters as you need; "2" indicates the nth comma after which you want to remove text.

Then, drag the fill handle to copy the formula to other cells, and all the characters after the second comma will be deleted at once, see screenshot:

 Remove text before or after Nth occurrence of a character with User Defined function

As you can see, you can solve the cases for removing text before or after Nth occurrence of a character by using Excel's native functions in different combinations. The problem is that you need to remember these tricky formulas. In this case, I will create a User Defined Function to cover all the scenarios, please do as this:

1. Hold down the "Alt + F11" keys to open the "Microsoft Visual Basic for Applications" window.

2. Click "Insert" > "Module", and paste the following code into the Module Window.

VBA code: Remove text before or after Nth occurrence of a character

Function RemoveTextOccurrence(Str As String, Delimiter As String, Occurrence As Integer, IsAfter As Boolean)
Dim xStr As String
Dim xStrLen, xF, xIntStart As Integer
xStr = Str
xStrLen = Len(xStr)
xIntStart = 1
For xF = 1 To Occurrence
xIntStart = InStr(xIntStart + 1, xStr, Delimiter, vbTextCompare)
If (xIntStart = 0) Or (xIntStart < 0) Then
    If IsAfter Then
    RemoveTextOccurrence = xStr
    Else
    RemoveTextOccurrence = ""
    End If
    Exit Function
End If
Next
If IsAfter Then
    RemoveTextOccurrence = Mid(Str, 1, xIntStart - 1)
Else
    RemoveTextOccurrence = Mid(Str, xIntStart + 1)
End If
End Function

3. Then, close and exit the code window, go back to the worksheet, use the following formulas:

Remove text before the second occurrence of a comma:

=RemoveTextOccurrence(A2, ", ", 2, FALSE)

Remove text after the second occurrence of a comma

=RemoveTextOccurrence(A2, ", ", 2, TRUE)

3.3 Remove text before or after the last occurrence of a character

If you need to remove all text before or after the last specific character, leaving only the substrings before or after it, this section provides formulas to solve this issue.

 Remove text before the last occurrence of a character with a formula

To remove all characters before the last occurrence of a character, the generic syntax is:

• "cell": The cell reference or text string that you want to remove text from;
• "char": The specific separator that you want to remove text based on;

Now, if you need to remove the text before the last occurrence of a comma, please copy or enter the below formula into a blank cell:

=RIGHT(A2,LEN(A2)-SEARCH("#",SUBSTITUTE(A2,",","#",LEN(A2)-LEN(SUBSTITUTE(A2,",","")))))

Note: In the above formula: "A2" is the cell that you want to remove text from; "," is the specific character you want to remove text based on, you can change it to any other characters as you need.

Then, drag the fill handle to copy the formula to other cells, and all the characters before the last comma will be deleted as below screenshot shown:

 Remove text after the last occurrence of a character with formulas

If the cell values are separated by a variable number of delimiters and you want to delete everything after the last occurrence of that delimiter, the generic syntax is:

• "cell": The cell reference or text string that you want to remove text from;
• "char": The specific separator that you want to remove text based on;

Please copy or enter the below formula into a blank cell, and then drag the fill handle down to get other results you need, see screenshot:

=LEFT(A2,FIND("#",SUBSTITUTE(A2,",>","#",LEN(A2)-LEN(SUBSTITUTE(A2,",",""))))-1)

"Note":In the above formula: "A2" is the cell that you want to remove text from; "," is the specific character you want to remove text based on, you can change it to any other characters as you need.

3.4 Remove text between parentheses

If you have a list of text strings with part of characters enclosed in the parentheses, now, you may want to remove all characters within the parentheses including the parentheses themselves as below screenshot shown. This section will talk about some tricks for solving this task in Excel.

 Remove text between parentheses with Find and Replace feature

In Excel, the built-in Find and Replace feature can help you to find all texts within the parentheses, and then replace them with nothing. Please do as this:

1. Select the data list that you want to remove the texts between the parentheses.

2. Then, click "Home" > "Find & Select" > "Replace" (or press "Ctrl + H" keys) to go the Find and Replace dialog box, in the "Find and Replace" dialog box, do the following operations:

• In the" Find what" field, type "(*)" into the textbox;
• In the "Replace With" field, leave this field empty.

3. Then, click "Replace All" button, all characters within the parentheses (including the parentheses) in the selected cells will be deleted at once. See screenshot:

Tips: The "Find and Replace" feature also works for two or more pairs of parentheses within the text strings.

 Remove text between parentheses with formula

Besides the Find and Replace feature, you can also use a formula for solving this task in Excel, the generic syntax is:

• "text": The text string or cell reference that you want to remove characters from.

Now, please copy or enter the following formula into a blank cell where you want to get the result:

=SUBSTITUTE(A2,MID(LEFT(A2,FIND(")",A2)),FIND("(",A2),LEN(A2)),"")

Then, drag the fill handle down to the cells where you want to apply this formula, and all texts within the parentheses including the parentheses will be removed at once, see screenshot:

Tips: If there are no parentheses in the cell value, an error will be displayed after applying the above formula, to ignore the error, please use the below formula:

=IFERROR(SUBSTITUTE(A2,MID(LEFT(A2,FIND(")",A2)),FIND("(",A2),LEN(A2)),""),A2)

 Remove text between parentheses with User Defined Function

The above formula works well for removing text from one pair of parentheses. However, if you need to remove text from multiple pairs of parentheses within the text strings, the formula will not work correctly. Here, I will create a simple User Defined Function to solve this task.

1. Hold down the "Alt + F11" keys to open the "Microsoft Visual Basic for Applications" window.

2. Click "Insert" > "Module", and paste the following code into the Module Window.

VBA code: Remove text between parentheses

Function remtxt(ByVal str As String) As String
'Updateby Extendoffice
  While InStr(str, "(") > 0 And InStr(str, ")") > InStr(str, "(")
    str = Left(str, InStr(str, "(") - 1) & Mid(str, InStr(str, ")") + 1)
  Wend
  remtxt = Trim(str)
End Function

3. Return to the worksheet, enter this formula into a blank cell: "=remtxt(A2)", then drag the fill handle down to apply the formula. All text within the parentheses, including the parentheses themselves, will be removed, as shown in the screenshot:

 Remove words from text strings

In certain cases, you may want to remove some words from a list of cells, such as first or last word, duplicate words from a cell. For resolving these kinds of removing, this section will introduce some methods for you.

4.1 Remove first or last word from text string

To remove the first or last word from a list of text strings, the following formulas may do you a favor.

 Remove first word from text string with formula

Remove the first words from a list of text strings, you can create a simple formula based on the RIGHT, LEN and FIND functions, the generic syntax is:

• "text": The text string or cell reference that you want to remove the first word from.

Now, please enter or copy the following formula into a blank cell:

=RIGHT(A2,LEN(A2)-FIND(" ",A2))

And then, drag the fill handle down to apply the formula to other cells, see screenshot:

Tips: If you need to remove the first N words from the cells, please use the below formula:

• "text": The text string or cell reference that you want to remove the first n words from;
• "N": Indicates how many words you want to remove from beginning of the text string.

For example, to remove the first two words from cells, please copy or enter the below formula into a blank cell to get the result as you need, see screenshot:

=MID(TRIM(A2),1+FIND("~",SUBSTITUTE(TRIM(A2)," ","~",2)),255)

  Remove last word from text string with formula

To remove the last word from text strings, you can also use a formula to solve this task, the generic syntax is:

• "text": The text string or cell reference that you want to remove the last word from;

Please use the below formula into a blank cell, and then drag the fill handle down to apply the formula to other cells, see screenshot:

=LEFT(TRIM(A2),FIND("~",SUBSTITUTE(A2," ","~",LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))))-1)

Tips: To remove the last N words from a list of cells, the generic syntax is:

• "text": The text string or cell reference that you want to remove the last n words from;
• "N": Indicates the number of words you want to remove from end of the text string.

Supposing, to delete the last 3 words from a list of cells, please use the below formula to return the result, see screenshot:

=LEFT(A2,FIND("~",SUBSTITUTE(A2," ","~",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-(3-1))))

4.2 Remove duplicate characters or words within a cell

When removing duplicate values or rows, Excel offers some different options, but, when it comes to removing some duplicate characters or words within a given cell, there may not be any good built-in feature for solving it. In this case, this section will help to create some User Defined Functions to deal with this puzzle.

 Remove duplicate characters within a cell by using User Defined Function

If you have multiple occurrences of the same character in a cell, to remove the duplicated characters within a cell and keep only the first occurrences as below screenshot shown, you can use the following User Defined Function.

1. Hold down the "Alt + F11" keys to open the "Microsoft Visual Basic for Applications" window.

2. Click "Insert" > "Module", and paste the following code into the Module Window.

VBA code: Remove duplicate characters within a cell

Function RemoveDupeschars(pWorkRng As Range) As String
'Updateby Extendoffice
Dim xValue As String
Dim xChar As String
Dim xOutValue As String
Set xDic = CreateObject("Scripting.Dictionary")
xValue = pWorkRng.Value
For i = 1 To VBA.Len(xValue)
    xChar = VBA.Mid(xValue, i, 1)
    If xDic.Exists(xChar) Then
    Else
        xDic(xChar) = ""
        xOutValue = xOutValue & xChar
    End If
Next
RemoveDupeschars = xOutValue
End Function

3. Then close the code window, go back to the worksheet, and enter this formula =RemoveDupeschars(A2) into a blank cell besides your data, and then drag the fill handle over to the cells that you want to apply this formula, see screenshot:

Note: "A2" is the data cell where you want to remove duplicate characters from.

Tip: The function is case-sensitive, so treats lowercase and uppercase letters as different characters.

 Remove duplicate words within a cell by using User Defined Function

Assuming, you have the same words or text strings in a cell and would like to remove all the same words from the cell as below screenshot shown. You can use the following User Defined Function to solve this task in Excel.

1. Hold down the "Alt + F11" keys to open the "Microsoft Visual Basic for Applications" window.

2. Click "Insert" > "Module", and paste the following code into the Module Window.

VBA code: Remove duplicate words within a cell

Function RemoveDupeswords(txt As String, Optional delim As String = " ") As String
'Updateby Extendoffice
    Dim x
    With CreateObject("Scripting.Dictionary")
        .CompareMode = vbTextCompare
        For Each x In Split(txt, delim)
            If Trim(x) <> "" And Not .exists(Trim(x)) Then .Add Trim(x), Nothing
        Next
        If .Count > 0 Then RemoveDupeswords = Join(.keys, delim)
    End With
End Function

3. Then close the code window, return to the worksheet, and enter this formula =RemoveDupeswords(A2,", ") into a blank cell besides your data, and then drag the fill handle over to the cells that you want to apply this formula, see screenshot:

Note: "A2" is the cell that you want to delete the duplicate words from, and the comma and space ("," ) are the delimiters to separate the text strings, you can change them to any other delimiters to your needs.

Tip: This function is not case-sensitive, lowercase and uppercase letters are treated as the same characters.

4.3 Trim text string to N words

If you have a long text string in a cell, sometimes, you may want to trim the text string to a certain number of words, which means to keep only first n words and cut the rest words. This section will talk about some tricks for helping you to achieve this job in Excel.

 Trim text string to N words with formula

To trim a text string to N words, you can create a formula based on the LEFT, FIND and SUBSTITUTE functions, the generic syntax is:

• "text": The text string or cell reference that you want to trim;
• "N": The number of words you want to keep from left side of the given text string.

To deal with this job, please copy or enter the below formula into a blank cell:

=LEFT(A2,FIND("~",SUBSTITUTE(A2," ","~",B2))-1)

And then, drag the fill handle down to apply this formula to other cells, see screenshot:

 Trim text string to N words with User Defined Function

Except the above formula, you can also create a User Defined Function for solving this task, please do as this:

1. Hold down the "Alt + F11" keys to open the" Microsoft Visual Basic for Applications" window.

2. Click "Insert" > "Module", and paste the following code into the Module Window.

VBA code: Trim text string to N words

Function GetNWords(StrWords As String, Num_of_Words As Integer) As String
'Updateby Extendoffice
Dim xArr
Dim xRes As String
Dim xF As Integer
xStr = StrWords
If (Num_of_Words < 1) Then
    GetNWords = ""
    Exit Function
End If
xArr = Split(xStr, " ")
xRes = ""
On Error Resume Next
For xF = 0 To UBound(xArr)
    If Trim(xArr(xF)) <> "" Then
    Num_of_Words = Num_of_Words - 1
        If xRes = "" Then
            xRes = Trim(xArr(xF))
        Else
            xRes = xRes & " " & Trim(xArr(xF))
        End If
    End If
    If Num_of_Words = 0 Then Exit For
Next
If Num_of_Words = 0 Then
    GetNWords = xRes & "..."
Else
    GetNWords = xRes & "..."
End If
End Function

3. Then close and quit the code window, go back to the worksheet, and enter this formula: =GetNWords(A2,B2) into a blank cell, and then drag the fill handle down to apply this formula to other cells, only the first specific number of words are kept as below screenshot shown:

Best Office Productivity Tools

🤖	Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
	Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
	Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
	Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
	Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
	Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
	Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

More details of Kutools for Excel... Free Download...

---

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

• Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
• Open and create multiple documents in new tabs of the same window, rather than in new windows.
• Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

More details of Office Tab... Free Download...

---

All Kutools add-ins. One installer

Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.

• All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
• One installer, one license — set up in minutes (MSI-ready)
• Works better together — streamlined productivity across Office apps
• 30-day full-featured trial — no registration, no credit card
• Best value — save vs buying individual add-in

More details of Kutools for Office... Free Download...