Skip to main content

Excel remove characters, words, numbers from text strings

Supposing, you have a long list of text strings which contain characters, numbers or other specific symbols. In certain case, you may need to remove some characters based on position, such as from right, left or middle from the text strings, or delete some unwanted characters, numbers from the list of strings. Finding the solutions one by one will get you a headache, this tutorial collects all kinds of methods for removing characters, words or numbers in Excel.

Table of contents:

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

2. Remove unwanted / special characters from text strings

3. Remove characters / text before or after specific character

4. Remove words from text strings


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:

=REPLACE(string, 1, num_chars, "")
  • 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:

=RIGHT(string, LEN(string) - num_chars)
  • 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:

=LEFT(string, LEN(string) - num_chars)
  • 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

Here, a User Defined Function also can help you to remove last n characters from a list of cells, 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 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.

After installing Kutools for Excel, please do as this:

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.

Download and free trial Kutools for Excel Now!


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.

=MID(string, left_chars + 1, LEN(string) - (left_chars + right_chars)
  • 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:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(string_cell, char1, ""), char2, ""), char3, "")
  • 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, but if you have dozens of characters to be removed, the formula will 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 with 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.

After installing Kutools for Excel, please do as this:

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:

Download and free trial Kutools for Excel Now!


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.

After installing Kutools for Excel, please do as this:

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:

Download and free trial Kutools for Excel Now!


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

After installing Kutools for Excel, please do as this:

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:

Download and free trial Kutools for Excel Now!


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.

After installing Kutools for Excel, please do as this:

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 popped out, 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:

Download and free trial Kutools for Excel Now!


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 case, you may want to delete the line breaks to make the cell content one line as below screenshot shown. 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 popped out 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 bar 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 that you want to remove the line breaks, see screenshot:

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.

After installing Kutools for Excel, please do as this:

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:

Download and free trial Kutools for Excel Now!


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.

After installing Kutools for Excel, please do as this:

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 one space type 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.

Download and free trial Kutools for Excel Now!


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:

=RIGHT(cell, LEN(cell)-FIND("char", cell))
  • 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:

=LEFT(cell,FIND("char",cell)-1)
  • 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:

=RIGHT(cell,LEN(cell)-FIND("#",SUBSTITUTE(cell,"char","#",N)))
  • 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:

=LEFT(cell, FIND("#", SUBSTITUTE(cell, "char", "#", N)) -1)
  • 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, and only leave the substrings after or before the last specific character as below screenshot shown, this section will talk about some formulas for solving 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:

=RIGHT(cell,LEN(cell)-SEARCH("#",SUBSTITUTE(cell,"char","#",LEN(cell)-LEN(SUBSTITUTE(cell,"char","")))))
  • 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 with a variable number of delimiters, now, you want to delete everything after the last instance of that delimiter, the generic syntax is:

=LEFT(cell,FIND("#",SUBSTITUTE(cell,"char","#",LEN(cell)-LEN(SUBSTITUTE(cell,"char",""))))-1)
  • 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:

=SUBSTITUTE(text,MID(LEFT(text,FIND(")",text)),FIND("(",text),LEN(text)),"")
  • 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, if you need to remove the texts from multiple pair 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. Then, return to the worksheet, and enter this formula into a blank cell: =remtxt(A2), then drag the fill handle down to the cells where you want to apply this formula, all the texts within all parentheses including the parentheses will be removed as below screenshot shown:


Remove words from text strings

In certain case, 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:

=RIGHT(text,LEN(text)-FIND(" ",text))
  • 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:

=MID(TRIM(text),1+FIND("~",SUBSTITUTE(TRIM(text)," ","~",N)),255)
  • 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:

=LEFT(TRIM(text),FIND("~",SUBSTITUTE(text," ","~",LEN(TRIM(text))-LEN(SUBSTITUTE(TRIM(text)," ",""))))-1)
  • 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:

=LEFT(text,FIND("~",SUBSTITUTE(text," ","~",LEN(text)-LEN(SUBSTITUTE(text," ",""))-(N-1))))
  • 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:

NoteA2 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:

=LEFT(text,FIND("~",SUBSTITUTE(text," ","~",N))-1)
  • 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:


  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...
kte tab 201905
  • 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!
officetab bottom
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations