Cookies help us deliver our services. By using our services, you agree to our use of cookies.
Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to remove text before or after a specific character in Excel?

Do you want to remove specified text before or after a specific character in Excel? Here I sort out some methods on sovling this problem.


Easily remove all numeric, non-numeric or specified characters from cells in Excel:

Kutools for Excel's Remove Characters utility helps you easily remove all numeric, non-numeric or specified characters from selected cells in Excel. Download the full feature 60-day free trail of Kutools for Excel now!

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download the free trial Now!


Delete text before or after character by Find and Replace in Excel

Find and Replace function is a function used widely in Excel, and with it, you can remove text before or after a specific character.

Press Ctrl + F to open the Find and Replace dialog, and click Replace tab, and type *, into the Find what text box, and leave blank in the Replace with text box, then click Replace All. See screenshot:

Now, you can see the text before comma are removed.

Notes:

1. If you want to remove the text after comma, you can type this ,* into the Find what text box.

2. You can change the comma to the character you need.

3. This method will remove all text before the last comma in specified cells.


Delete text before or after character by formula in Excel

In Excel, you also can use formula to delete before or after a specific character.

1. Select a blank cell and type this formula =RIGHT(A1,LEN(A1)-FIND(",",A1)) into it, and press Enter key.See screenshot:

2. Drag the Fill Handle down to the range until all text before comma are removed.

Note:

1. Remove the text after comma by using =LEFT(A1,FIND(",",A1)-1).

2. In the formulas, A1 is the cell you want to remove text, and "," is the character you want to remove text before or after it.

3. =RIGHT(A1,LEN(A1)-FIND(",",A1)) will remove all text before the first comma in Cell A1, while =LEFT(A1,FIND(",",A1)-1) will remove all text after the first comma in Cell A1.

Office Tab - Tabbed Browsing, Editing, and Managing of Workbooks in Excel:

Office Tab brings the tabbed interface as seen in web browsers such as Google Chrome, Internet Explorer new versions and Firefox to Microsoft Excel. It will be a time-saving tool and irreplaceble in your work. See below demo:

Click for free trial of Office Tab!

Office Tab for Excel


Delete text before character with VBA in Excel

There is a VBA code can help you to remove the text before a specific character.

1. Press Alt + F11 to display the Microsoft Visual Basic for Applications window.

2. In the window, click Insert > Module to show a new module window, then copy the following VBA code into the module window.

VBA: Delete text before a specific character in Excel.

Sub RemoveAllButLastWord()
'Updateby20140612
Dim Rng As Range
Dim WorkRng As Range
Dim xChar As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
xChar = Application.InputBox("String", xTitleId, "", Type:=2)
For Each Rng In WorkRng
    xValue = Rng.Value
    Rng.Value = VBA.Right(xValue, VBA.Len(xValue) - VBA.InStrRev(xValue, xChar))
Next
End Sub

3. Click the Run button, a dialog box pops up for you to select a cell or range you will delete texts before comma, and then click the OK button. See screenshots:

4. Then another dialog pops out, please enter the character you will delete all texts based on it (here we enter a comma), and then click the OK button.

Now all texts before the comma are removed immediately.

Note: The VBA will remove all text before the last character (such as comma) in a specified cell.


Remove text before/after character by splitting cells with Kutools for Excel

This section will introduce the Split Cells utility of Kutools for Excel to help y ou quickly split cells with specific character. Please do as follows:

1. Select the range with texts you want to remove before or after a specific character, and then click Kutools > Text > Split Cells. See screenshot:

2. In the Split Cells dialog box, select the Other option, then type the character into the blank box (here we enter a comma), and then click the OK button. See screenshot:

doc remove before after2

3. In another popping up dialog box, select a blank cell for locating the texts, and then click the OK button.

Then you can see the selected cells are split by specific character – comma. See screenshot:

Tip.If you want to have a free trial of this utility, please go to download the software freely first, and then go to apply the operation according above steps.


Easily remove text before/after character with Kutools for Excel


Relative Articles:



Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 300 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    krish srinivasan · 1 months ago
    Hi, I need help.
    I have data extracted and posted in excel like this: (cell B2)
    NWS_141118_FTP_DMP_SAMY_MURUGAN_FISTUVEL_1

    I want to extract only the text (characters, may be 2, 3 , 4 in length), between the third _ and fourth _.
    Any help is appreciated.
    Thanks in anticipation
  • To post as a guest, your comment is unpublished.
    Neil · 3 months ago
    How do I only recall everything after the last underscore? So in this case I need just the 36D from the end. Example: 32533WHT_Caress36D_White_36D


    Thanks in advance!!
  • To post as a guest, your comment is unpublished.
    Andes · 4 months ago
    I just wanna say thank you here, it's awesome using these formula with your help.. Thanks ^_^
  • To post as a guest, your comment is unpublished.
    Mina · 5 months ago
    Hey I got a text like this how do I remove everything after the hyphen?
    ABCD123456-ABC

    How do I delete or move everything after the 2 hyphen?
    ABCD12345-1234-ABCD
    ABCD12345-1234-X-123-AB-1

    Thank you in advance
    • To post as a guest, your comment is unpublished.
      crystal · 4 months ago
      Hi Mina,
      To remove everything after the first hyphen, please apply this formula: =LEFT(A1,FIND("-",A1)-1)
      To remove everything after the second hyphen, this formula can help you: =LEFT(A1,SEARCH("-",A1,SEARCH("-",A1)+1)-1)
  • To post as a guest, your comment is unpublished.
    Ryan · 5 months ago
    Thank you :)