Note: The other languages of the website are Google-translated. Back to English
Inloggen  \/ 
x
or
x
Registreer  \/ 
x

or

Hoe verwijder ik teksten voor of na een specifiek teken uit cellen in Excel?

Dit artikel introduceert verschillende methoden om teksten voor of na een specifiek teken of het n-de voorkomende teken uit cellen in Excel te verwijderen.


Verwijder teksten voor of na een specifiek teken met Zoeken en vervangen in Excel

Ga als volgt te werk om alle teksten voor of na een specifiek teken te verwijderen met de functie Zoeken en vervangen.

1. Selecteer de cellen waarvoor u teksten voor of na een specifiek teken wilt verwijderen, druk op Ctrl + H toetsen om de te openen Zoeken en vervangen dialoogvenster.

In het dialoogvenster Zoeken en vervangen:

1. Om alles vóór het specifieke teken, zoals een komma, te verwijderen, typt u *, in de Zoeken naar tekstvak;

2. Om alles na het specifieke teken, zoals een komma, te verwijderen, typt u ,* in de Zoeken naar tekstvak;

Opmerkingen:

1. U kunt de komma naar elk gewenst teken wijzigen.

2. In dit geval worden alle teksten voor de laatste komma of na de eerste komma uit de geselecteerde cellen verwijderd.

2. Houd de Vervangen door tekstvak leeg en klik vervolgens op het Alles vervangen knop. Zie screenshot:


Verwijder eenvoudig alle numerieke, niet-numerieke of gespecificeerde tekens uit cellen in Excel

Kutools voor Excel's Teken verwijderen hulpprogramma helpt u gemakkelijk alle numerieke, niet-numerieke of opgegeven tekens uit geselecteerde cellen in Excel te verwijderen.
Download nu het volledige gratis 30-daagse gratis parcours van Kutools voor Excel!


Verwijder teksten voor of na het eerste / laatste specifieke teken per formule

In dit gedeelte ziet u formules voor het verwijderen van alles voor of na het eerste / laatste specifieke teken uit cellen in Excel.

Om alles vóór de eerste komma te verwijderen, alstublieft:

Selecteer een lege cel, kopieer en plak de onderstaande formule erin en druk op Enter sleutel. Sleep vervolgens het Vul de handgreep om de formule op andere cellen toe te passen. Zie screenshot:

Formule: Verwijder alles vóór de eerste komma

= RECHTS (B5, LEN (B5)-VIND(",",B5))

Opmerkingen:

1. In de bovenstaande formule is B5 de cel waaruit u teksten verwijdert, en "," is het teken waarvan u teksten verwijdert op basis van.

2. Gebruik deze formule om alles vóór het laatste specifieke teken te verwijderen:

= RECHTS (B5, LEN (B5) -FIND ("@", SUBSTITUTE (B5,"Karakter"," @ ", (LEN (B5) -LEN (VERVANGING (B5,"Karakter"," "))) / LEN ("Karakter"))))

Om alles na de eerste komma te verwijderen, alstublieft:

Selecteer een lege cel, kopieer en plak de onderstaande formule erin en druk op Enter sleutel. Sleep vervolgens het Vul de handgreep om de formule op andere cellen toe te passen. Zie screenshot:

Formule: Verwijder alles na de eerste komma

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

Opmerkingen:

1. In de bovenstaande formule is B5 de cel waaruit u teksten verwijdert, en "," is het teken waarvan u teksten verwijdert op basis van.

2. Gebruik deze formule om alles na het laatste specifieke teken te verwijderen:

= LINKS (B5, FIND ("@", SUBSTITUTE (B5,"karakter"," @ ", LEN (B5) -LEN (VERVANGING (B5,"karakter"," ")))) - 1)


Verwijder teksten voor of na het n-de voorkomingsteken per formule

De onderstaande formules kunnen helpen om alles voor of na het n-de voorkomen-teken uit cellen in Excel te verwijderen.

Om alles vóór het n-de voorkomen-teken uit cellen te verwijderen, je moet:

Selecteer een lege cel om het resultaat uit te voeren, kopieer de onderstaande formule erin en druk op Enter sleutel. Sleep vervolgens het Vul de handgreep om de formule op andere cellen toe te passen. Zie screenshot:

Formule: Verwijder alles vóór de tweede komma

= RECHTS (VERVANGEN (B5',", CHAR (9), 2), LEN (B5) - FIND (CHAR (9), SUBSTITUTE (B5',", CHAR (9), 2), 1) + 1)

Opmerkingen:

1. In de formules, B5',"en 2 getal betekent dat alle inhoud na de tweede komma wordt verwijderd uit cel B5.

2. U kunt de ","en 2 nummer naar een willekeurig teken en een positienummer voor het voorkomen als u nodig hebt.

Om alles na het n-de voorkomen-teken uit cellen te verwijderen, je moet:

Selecteer een lege cel om het resultaat uit te voeren, kopieer de onderstaande formule erin en druk op Enter sleutel. Sleep vervolgens het Vul de handgreep om de formule op andere cellen toe te passen. Zie screenshot:


Formule: Verwijder alles na de tweede komma die voorkomt

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

Opmerkingen:

1. In de formules, B5',"en 2 getal betekent dat alle inhoud na de tweede komma wordt verwijderd uit cel A7.

2. U kunt de ","en 2 nummer naar een willekeurig teken en een positienummer voor het voorkomen als u nodig hebt.


Verwijder eenvoudig teksten voor / na een specifiek teken met Kutools voor Excel

Als er slechts één komma-scheidingsteken is voor elke cel in een bereik en u alles voor of na deze komma uit cellen wilt verwijderen, probeer dan de Gespleten cellen nut van Kutools for Excel. Met dit hulpprogramma kunt u het probleem met slechts enkele klikken oplossen:

1. Selecteer de cellen waarvan u alles voor of na de komma wilt verwijderen en klik op Kutools > Tekst > Gespleten cellen. Zie screenshot:

2. In de Gespleten cellen dialoogvenster, selecteer het Splitsen in kolommen optie in het Type sectie en in het Gesplitst door sectie, kies het Overig optie en typ een komma in het lege vak en klik vervolgens op het OK knop. Zie screenshot:

3. Een ander Gespleten cellen verschijnt, selecteert u een lege cel om de teksten te zoeken en klikt u op het OK knop.

Vervolgens kunt u zien dat de geselecteerde cellen zijn opgesplitst door een specifiek teken - komma. Zie screenshot:

  Als u een gratis proefperiode (30 dagen) van dit hulpprogramma wilt, klik om het te downloaden, en ga vervolgens de bewerking toepassen volgens de bovenstaande stappen.


Kutools voor Excel - Helpt u uw werk altijd op tijd af te werken, meer tijd te hebben om van het leven te genieten
Vind je jezelf vaak een inhaalslag met werk, gebrek aan tijd om voor jezelf en je gezin te besteden?  Kutools for Excel kan u helpen om 80% Excel-puzzels aan te pakken en 80% werkefficiëntie te verbeteren, u meer tijd te geven om voor uw gezin te zorgen en van het leven te genieten.
300 geavanceerde tools voor 1500 werkscenario's, maken uw werk zo veel gemakkelijker dan ooit.
U hoeft geen formules en VBA-codes meer te onthouden, geef uw hersenen vanaf nu rust.
Ingewikkelde en herhaalde bewerkingen kunnen binnen enkele seconden een eenmalige verwerking worden uitgevoerd.
Verminder elke dag duizenden toetsenbord- en muisbewerkingen, zeg nu vaarwel tegen beroepsziekten.
Word Excel-expert in 3 minuten, help u snel herkend te worden en maak een promotie voor loonsverhoging.
110,000 zeer effectieve mensen en meer dan 300 keuze van wereldberoemde bedrijven.
Maak uw $ 39.0 meer waard dan $ 4000.0 training van anderen.
Gratis proefperiode van 30 dagen met volledige functionaliteit. 60 dagen geld-terug-garantie zonder reden.

Say something here...
symbols left.
You are guest
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.
    Olof · 2 months ago
    Hi I have the next text:

    /credit-loan/super-credit/- from 1251 to 1016( -18.78%)
    /credit-loan/no-credit/- from 1018 to 703( -30.94%)
    /loan-loan-credit-cards- from 773 to 633( -18.11%)
    /loan-loan/fair-loan/- from 321 to 502( -29.69%)

    I need to delete everything after "-" before the from, including the character "-" so the above to become:

    /credit-loan/super-credit/
    /credit-loan/no-credit/
    /loan-loan-credit-cards
    /loan-loan/fair-loan/

    And after I would also need to have the above without the "/" as in (but I think I can manage that later):

    /credit-loan/super-credit
    /credit-loan/no-credit
    /loan-loan-credit-cards
    /loan-loan/fair-loan
  • To post as a guest, your comment is unpublished.
    Olof · 2 months ago
    Hi I have the next text:

    /credit-loan/super-credit/- from 1251 to 1016( -18.78%)  
    /credit-loan/no-credit/- from 1018 to 703( -30.94%)  
    /loan-loan-credit-cards- from 773 to 633( -18.11%)  
    /loan-loan/fair-loan/- from 321 to 502( -29.69%)  

    I need to delete everything after "-" before the from, including the character "-" so the above to become:

    /credit-loan/super-credit/
    /credit-loan/no-credit/
    /loan-loan-credit-cards
    /loan-loan/fair-loan/

    And after I would also need to have the above without the "/" as in (but I think I can manage that later): 

    /credit-loan/super-credit
    /credit-loan/no-credit
    /loan-loan-credit-cards
    /loan-loan/fair-loan
  • To post as a guest, your comment is unpublished.
    Chris · 7 months ago
    Thank you!
  • To post as a guest, your comment is unpublished.
    Michael Chan · 1 years ago
    What is the formula to keep only 160USCAIRAPPW01 in below excel spreadsheet?

    16780187,160-USC-NOV-Updates-Server,160 - ALL LAC+USC Servers,160USCAIRAPPW01,Non-compliant,11/23/2019 11:33 AM,,,,,,16891741,Non-compliant,0X00000000,Success,11/23/2019 11:22 AM,(SYSTEM),Yes,No,Yes,
  • To post as a guest, your comment is unpublished.
    jk.mishra · 1 years ago
    remove everything before
    the last hyphen?
    BABU RAM (622)
    JAGAN NATH
  • To post as a guest, your comment is unpublished.
    Kathleen · 2 years 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.
      crystal · 1 years ago
      Hi, please try this formula:
      =LEFT(RIGHT(B9,LEN(B9)-FIND(" ",B9)+1),FIND("^^",SUBSTITUTE(RIGHT(B9,LEN(B9)-FIND(" ",B9))," ","^^",LEN(RIGHT(B9,LEN(B9)-FIND(" ",B9)))-LEN(SUBSTITUTE(RIGHT(B9,LEN(B9)-FIND(" ",B9))," ",""))))-1)
  • To post as a guest, your comment is unpublished.
    Abdul Wahab · 2 years ago
    Great buddy. Too much helpful post.
  • To post as a guest, your comment is unpublished.
    Adrian · 2 years 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 · 2 years 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 · 2 years 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 · 2 years 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 · 2 years 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.
    krshsrinivasan@gmail.com · 2 years 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.
      jeykuu@gmail.com · 2 years 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 · 2 years 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 · 2 years 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 · 3 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 · 2 years 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 · 3 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 · 3 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 · 3 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 · 2 years 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 · 3 years ago
        Thank you life saver!
  • To post as a guest, your comment is unpublished.
    Ryan · 3 years ago
    Thank you :)
  • To post as a guest, your comment is unpublished.
    Marites · 3 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 · 3 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 · 3 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 · 3 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 · 3 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 · 3 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.
    jonathanpiette@gmail.com · 3 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.
      crystal · 3 years ago
      Dear Jonathan Piette,
      So happy for you.
  • To post as a guest, your comment is unpublished.
    jonathanpiette@gmail.com · 3 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.
    jonathanpiette@gmail.com · 3 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 · 3 years ago
    how to right STVP1-AMDC1-DELC2- value remove (-)
    • To post as a guest, your comment is unpublished.
      crystal · 3 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@gmail.com · 3 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.
      crystal · 3 years ago
      Good Day,
      Thank you for sharing.
  • To post as a guest, your comment is unpublished.
    Atul · 4 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 · 4 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.
        crystal · 4 years ago
        See screenshot:
  • To post as a guest, your comment is unpublished.
    TarunKumar · 4 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 · 4 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 · 4 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 · 4 years ago
    I need to delete all text after the first word.
    • To post as a guest, your comment is unpublished.
      crystal · 4 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 · 4 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 · 5 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 · 6 years ago
    I like this way better than the mid formula!
  • To post as a guest, your comment is unpublished.
    Mohammed · 6 years ago
    So grateful to you, that was helpful
  • To post as a guest, your comment is unpublished.
    Vinoda · 6 years ago
    It is too helpfull .
  • To post as a guest, your comment is unpublished.
    willie gluck · 6 years ago
    Some great tips there! thanks.
  • To post as a guest, your comment is unpublished.
    Bill Liew · 6 years ago
    A big thank you. Perfect solution.
  • To post as a guest, your comment is unpublished.
    Sean · 6 years ago
    Fantastic, thanks for that! So easy