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 texts before or after a specific character from cells in Excel?

This article introduces different methods of removing texts before or after a specific character or the nth occurrence character from cells in Excel.


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

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


Delete texts before or after specific character by Find and Replace in Excel

For removing all texts before or after a specific character with the Find and Replace function, please do as follows.

1. Select the cells you will remove texts before or after a specific character, press Ctrl + H keys to open the Find and Replace dialog.

In the Find and Replace dialog box:

1. For removing all before the specific character such as comma, please type *, into the Find what text box;

2. For removing all after the specific character such as comma, please type ,* into the Find what text box;

Notes:

1. You can change the comma to any character as you need.

2. In this case, all texts before the last comma or after the first comma will be removed from the selected cells.

2. Keep the Replace with text box empty, and then click the Replace All button. See screenshot:

Bring Tabbed Editing and Browsing in Office
--- Make Your Work Much Easier
Office Tab will save 50% of your work time when browsing & editing multiple documents.
Unbelievable! The operation of two or more documents is even pleasant than the single document operation.
Reduce thousands of keyboard & mouse operations every day, farewell to occupational diseases now.
The interface of Office Tab is far more powerful and efficient than internet browsers.

Delete texts before or after the first/last specific character by formula

This section will show you formulas of deleting everything before or after the first/last specific character from cells in Excel.

To remove everything before the first comma, please:

Select a blank cell, copy and paste the below formula into it, and press Enter key. Then drag the Fill Handle to apply the formula to other cells. See screenshot:

Formula: Remove everything before the first comma

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

Notes:

1. In the above formula, B5 is the cell you will remove texts from, and "," is the character you will remove texts based on.

2. To remove all before the last specific character, use this formula:

=RIGHT(B5,LEN(B5)-FIND("@",SUBSTITUTE(B5,"Character","@",(LEN(B5)-LEN(SUBSTITUTE(B5,"Character","")))/LEN("Character"))))

To remove everything after the first comma, please:

Select a blank cell, copy and paste the below formula into it, and press Enter key. Then drag the Fill Handle to apply the formula to other cells. See screenshot:

Formula: Remove everything after the first comma

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

Notes:

1. In the above formula, B5 is the cell you will remove texts from, and "," is the character you will remove texts based on.

2. To remove all after the last specific character, use this formula:

=LEFT(B5,FIND("@",SUBSTITUTE(B5,"character","@",LEN(B5)-LEN(SUBSTITUTE(B5,"character",""))))-1)


Delete texts before or after the nth occurrence character by formula

The below formulas can help to delete all before or after the nth occurrence character from cells in Excel.

To remove all before the nth occurrence character from cells, you need to:

Select a blank cell to output the result, copy the below formula into it, and press Enter key. Then drag the Fill Handle to apply the formula to other cells. See screenshot:

Formula: Remove everything before the second occurrence comma

=RIGHT(SUBSTITUTE(B5, ",", CHAR(9), 2), LEN(B5)- FIND(CHAR(9), SUBSTITUTE(B5, ",", CHAR(9), 2), 1) + 1)

Notes:

1. In the formulas, B5, "," and 2 number mean that all contents after the second occurrence comma will be removed from cell B5.

2. You can change the "," and 2 number to any character and occurrence position number as you need.

To remove all after the nth occurrence character from cells, you need to:

Select a blank cell to output the result, copy the below formula into it, and press Enter key. Then drag the Fill Handle to apply the formula to other cells. See screenshot:


Formula: Remove everything after the second occurrence comma

=LEFT(SUBSTITUTE(B5,",",CHAR(9),2),FIND(CHAR(9),SUBSTITUTE(B5,",",CHAR(9),2),1)-1)

Notes:

1. In the formulas, B5, "," and 2 number mean that all contents after the second occurrence comma will be removed from cell A7.

2. You can change the "," and 2 number to any character and occurrence position number as you need.


Easily remove texts before/after a specific character with Kutools for Excel

If there is only one comma separator for each cell in a range, and you want to remove everything before or after this comma from cells, please try the Split Cells utility of Kutools for Excel. This utility will help you to solve the problem with only several clicks:

1. Select the cells which you will remove everything before or after the comma from, and then click Kutools > Text > Split Cells. See screenshot:

2. In the Split Cells dialog, select the Split to Columns option in the Type section, and in the Split by section, choose the Other option and type a comma into the blank box, and then click the OK button. See screenshot:

3. Another Split Cells dialog pops up, 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:

  If you want to have a free trial ( 30-day) of this utility, please click to download it, and then go to apply the operation according above steps.


Kutools for Excel - Helps You Always Finish Work Ahead of Time, Have More Time to Enjoy Life
Do you often find yourself playing catch-up with work, lack of time to spend for yourself and family?  Kutools for Excel can help you to deal with 80% Excel puzzles and improve 80% work efficiency, give you more time to take care of family and enjoy life.
300 advanced tools for 1500 work scenarios, make your job so much easier than ever.
No longer need memorizing formulas and VBA codes, give your brain a rest from now on.
Complicated and repeated operations can be done a one-time processing in seconds.
Reduce thousands of keyboard & mouse operations every day, say goodbye to occupational diseases now.
Become an Excel expert in 3 minutes, help you quickly get recognized and a pay raise promotion.
110,000 highly effective people and 300+ world-renowned companies' choice.
Make your $39.0 worth more than $4000.0 training of others.
Full feature free trial 30-day. 60-Day Money Back Guarantee without reason.

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.
    Kathleen · 1 months ago
    I have: E4U19-31C20010093021EI 3032AER LINGUS 190805000010. I need to remove everything before the first space and everything after the last space but I need to keep the spaces. I have this formula but it removes the spaces: =LEFT(RIGHT(A19,LEN(A19)-FIND(" ",A19)),FIND("^^",SUBSTITUTE(RIGHT(A19,LEN(A19)-FIND(" ",A19))," ","^^",LEN(RIGHT(A19,LEN(A19)-FIND(" ",A19)))-LEN(SUBSTITUTE(RIGHT(A19,LEN(A19)-FIND(" ",A19))," ",""))))-1)
  • To post as a guest, your comment is unpublished.
    Abdul Wahab · 1 months ago
    Great buddy. Too much helpful post.
  • To post as a guest, your comment is unpublished.
    Adrian · 5 months ago
    hello, I have the next string of numbers and characters in a cell.( 80E:1,85B:3,90B:3,90C:2,90D:2,95B:2,95C:2
    ) I want the cell to remain so ( 80E, 85B, 90B, 90C, 90D, 95B, 95C ) what formula I have to apply or how to proceed. Thanks a lot !
    • To post as a guest, your comment is unpublished.
      Trevor · 13 days ago
      You can use Find and Replace using the single character wildcard (?)

      eg. Find What: ":?" Replace with: ""
    • To post as a guest, your comment is unpublished.
      crystal · 3 months ago
      Hi Adrian,
      Sorry can't help you solving this problem yet. Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    Ivy · 7 months ago
    How do I create a multiple if search functtion where I place a left or righr function after a specific criteria. For example if the number starts in 501 then left 5, if the starts in 303 then left 6 and all other numbers are left 4. I have,
    • To post as a guest, your comment is unpublished.
      crystal · 7 months ago
      Hi Ivy,
      Would you please provide a screenshot of your spreadsheet showing what you are exactly trying to do? Thanks for commenting.
  • To post as a guest, your comment is unpublished.
    krish srinivasan · 10 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.
      Jeyakumar Kannan · 8 months ago
      Given String: NWS_141118_FTP_DMP_SAMY_MURUGAN_FISTUVEL_1

      Desired to extract : DMP

      Position of 3rd _ : 16 : Try this formula =FIND("_",E6,FIND("_",E6,FIND("_",E6,1)+1)+1)+1

      Position of 4th _ : 19 : Try this formula =FIND("_",E6,FIND("_",E6,FIND("_",E6,FIND("_",E6,1)+1)+1)+1+1)
    • To post as a guest, your comment is unpublished.
      Jku · 8 months ago
      Given String: NWS_141118_FTP_DMP_SAMY_MURUGAN_FISTUVEL_1

      Desired to extract: DMP

      Position of 3rd _ : 16 : Please try this function =FIND("_",E6,FIND("_",E6,FIND("_",E6,1)+1)+1)+1

      Position of 4th _ : 19 : Please try this function =FIND("_",E6,FIND("_",E6,FIND("_",E6,FIND("_",E6,1)+1)+1)+1+1)
    • To post as a guest, your comment is unpublished.
      BOG · 8 months ago
      You can use excels inbuilt text to columns for this and just use, "_" as the separator
  • To post as a guest, your comment is unpublished.
    Neil · 1 years 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.
      crystal · 8 months ago
      Hi Neil,
      This formula can help you: =RIGHT(A15,LEN(A3)-FIND("@",SUBSTITUTE(A15,"_","@",(LEN(A15)-LEN(SUBSTITUTE(A15,"_","")))/LEN("_"))))
  • To post as a guest, your comment is unpublished.
    Andes · 1 years 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 · 1 years 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 · 1 years 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.
        Peter · 8 months ago
        My excel is on PT-BR but this formula doesnt work, im using EXCEL 07, does i need a plugin or something to make that work?
      • To post as a guest, your comment is unpublished.
        Mina · 1 years ago
        Thank you life saver!
  • To post as a guest, your comment is unpublished.
    Ryan · 1 years ago
    Thank you :)
  • To post as a guest, your comment is unpublished.
    Marites · 1 years ago
    Hi. I have am trying to use formula =LEFT(B5,FIND(".",B5)-1 but instead of "." I would like to use "/" so it will take up only the required text & number. For example:
    ABCD-5008/2 and XYZ-5010/2. I need to capture only ABCD-5008 and XYZ-5010. But when I use =LEFT(B5,FIND("/",B5)-1) it gives me #VALUE!. Please advise me how to approach this concern. Thank you very much!
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi Marites,
      The formula works well in my case. Which Office version do you use?
  • To post as a guest, your comment is unpublished.
    Pjotr · 1 years ago
    Hello.
    I have text string in one cell similar to this: example1, example2, example3, example4, example5, example6, example7, example8, example9.
    I need to extract text before 5th comma for example. So as a result i would have something like this: example1, example2, example3, example4, example5.
    any help?
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Good Day,
      The below formula can help you extracting all texts before the 5th comma in a cell.
      =LEFT(SUBSTITUTE(A26," ","-",5),FIND("-",SUBSTITUTE(A26," ","-",5),1)-1)
  • To post as a guest, your comment is unpublished.
    Artik · 1 years ago
    Hello, I need help with formula.
    Two columns in first information about address, in second I need just country.
    For example:
    XXX34, AAABBBCCC VS, Šveice
    How I with formula can take just “Šveice”?
    Thanks!
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi Artik,
      This formula can help you: =TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),LEN(A1)))
  • To post as a guest, your comment is unpublished.
    Jonathan Piette · 1 years ago
    Haaaaaa I got it. I still don't know what I was doing wrong but I used different formula and it works. This is then a GOOD method to split cell in 3 or more categories using formula. Thanks to you crystal
    Here's my new revised formulas.
    B21: =TRIM(LEFT(SUBSTITUTE(A21,"_",REPT(" ",99)),99))
    C21: =RIGHT(A21,LEN(A21)-FIND("_",A21)) cell to be hide
    D21: =TRIM(LEFT(SUBSTITUTE(C21,"_",REPT(" ",99)),99))
    E21: =TRIM(RIGHT(SUBSTITUTE(A21,"_",REPT(" ",99)),99))
    Result:
    A21 (source): Bay12_PRL_Cb
    B21: Bay21
    D21: PRL
    E21: Cb

    I'm sure there is a way to simplify this, but it works for me.
  • To post as a guest, your comment is unpublished.
    Jonathan Piette · 1 years ago
    I post a snapshot, but for some reason, it doesn't show it!!!
    So here's the formula:
    B21: =LEFT(A21,LEN(A21)-FIND("_",A21))
    C21: =RIGHT(A21,LEN(A21)-FIND("_",A21))
    D21: =LEFT(C21,LEN(C21)-FIND("_",C21))
    E21: =TRIM(RIGHT(SUBSTITUTE(A21,"_",REPT(" ",99)),99))
  • To post as a guest, your comment is unpublished.
    Jonathan Piette · 1 years ago
    Hi Guys
    I try to split multiple element from a cell using forumas shown on this page, but I have problems, I have inconstancy and caractere missing! Check the snapshots to see the formula. Here's for example row 21 situation:
    A21: Bay12_PRL_Cb is my source cell. I want to split Bay21/PRL/Cb in 3 cells
    B21: Bay12_ (The "_" should not be there)
    C21: PRL_Cb (This one works fine, I will use and hide this cell to create D21)
    D21: PR (I should have PRL, the L is missing)
    E21: Cb (This one works fine)

    Can anyone point me what I'm doing wrong??
    This is excel mac 2008, version 12.3.2 Licensed
  • To post as a guest, your comment is unpublished.
    dharmendra · 1 years ago
    how to right STVP1-AMDC1-DELC2- value remove (-)
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Good Day,
      Do you mean remove all dash "-" from the string?
      Please try this formula =SUBSTITUTE(A1,"-","")
  • To post as a guest, your comment is unpublished.
    MakoSipper · 1 years ago
    Bear in mind that, with
    =RIGHT(A1,LEN(A1)-FIND(",",A1))
    you're keeping the space after the comma. An easy solution would be:
    =RIGHT(A1,LEN(A1)-FIND(",",A1)-1)
  • To post as a guest, your comment is unpublished.
    Atul · 1 years ago
    I have multiple / in my string and want to separate the text or string after the last / found in the string, please tell me how to do this
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Good Day,
      Supposing your cell is A4, you can apply this formula =LEFT(A4,FIND("@",SUBSTITUTE(A4,"/","@",LEN(A4)-LEN(SUBSTITUTE(A4,"/",""))))-1) to get all text before the last / symbol.

      and then apply formula =TRIM(RIGHT(SUBSTITUTE(A4,"/",REPT(" ",99)),99)) to get text after the last / symbol found in the string. See screenshot:
  • To post as a guest, your comment is unpublished.
    TarunKumar · 2 years ago
    Please share the formula for finding multiple spaces in a text string, to extract what we wish from that string, easily.
    Thankyou
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Dear TarunKumar,
      Sorry I am not sure I got your question. Would be nice if you could provide a screenshot of what you are trying to do.
  • To post as a guest, your comment is unpublished.
    Tom · 2 years ago
    This was very useful indeed to help me create / extract new logins from our email database - thank you! (and thanks, Excel)!
  • To post as a guest, your comment is unpublished.
    Barnett Frankel · 2 years ago
    I need to delete all text after the first word.
    • To post as a guest, your comment is unpublished.
      crystal · 2 years ago
      Dear Barnett Frankel,

      If you assume that a space is the division between words, this formula =LEFT(A2,FIND(" ",A2&" ")-1) can help you. And if your words are separated by others such as comma, please replace the spaces in the formula with commas: =LEFT(A2,FIND(",",A2&",")-1)

      Best Regards, Crystal
  • To post as a guest, your comment is unpublished.
    Ken · 2 years ago
    The formula for deleting text after a character is exactly what I needed. Thank you!
  • To post as a guest, your comment is unpublished.
    Otep · 3 years ago
    Thank you for this! Got to save some precious time for a 600 line item.
  • To post as a guest, your comment is unpublished.
    Mariela · 3 years ago
    I like this way better than the mid formula!
  • To post as a guest, your comment is unpublished.
    Mohammed · 4 years ago
    So grateful to you, that was helpful
  • To post as a guest, your comment is unpublished.
    Vinoda · 4 years ago
    It is too helpfull .
  • To post as a guest, your comment is unpublished.
    willie gluck · 4 years ago
    Some great tips there! thanks.
  • To post as a guest, your comment is unpublished.
    Bill Liew · 4 years ago
    A big thank you. Perfect solution.
  • To post as a guest, your comment is unpublished.
    Sean · 4 years ago
    Fantastic, thanks for that! So easy