När du arbetar med textdata i Excel kan du behöva ta bort texten före eller efter ett specifikt tecken eller en textsträng.
Om du till exempel har namn och beteckningsdata för personer kanske du vill ta bort beteckningen efter komma och bara behålla namnet (eller vice versa där du behåller beteckningen och tar bort namnet).
Ibland kan det göras med en enkel formel eller en snabb Sök och ersätt, och ibland behöver den mer komplexa formler eller lösningar.
I den här självstudien visar jag hur du tar bort texten före eller efter ett specifikt tecken i Excel (med olika exempel).
Så låt oss komma igång med några enkla exempel.
Ta bort text efter ett tecken med hjälp av Sök och ersätt
Om du snabbt vill ta bort all text efter en specifik textsträng (eller före en textsträng) kan du göra det med Sök och ersätt och jokertecken.
Anta att du har en dataset som visas nedan och du vill ta bort beteckningen efter kommatecknet och behålla texten före kommatecknet.
Nedan följer stegen för att göra detta:
- Kopiera och klistra in data från kolumn A till kolumn B (detta är också för att behålla originaldata)
- Med cellerna i kolumn B markerade klickar du på fliken Hem
- Klicka på alternativet Sök och välj i gruppen Redigering
- Klicka på alternativet Ersätt i alternativen som visas i listrutan. Detta öppnar dialogrutan Sök och ersätt
- I fältet "Hitta vad" anger du ,* (dvs. komma följt av ett asterisk -tecken)
- Lämna fältet "Ersätt med" tomt
- Klicka på knappen Ersätt alla
Stegen ovan skulle hitta kommatecken i datamängden och ta bort all text efter kommatecken (inklusive kommatecken).
Eftersom detta ersätter texten från de markerade cellerna är det en bra idé att kopiera texten till en annan kolumn och sedan utföra denna sökning och ersättningsoperation, eller skapa en säkerhetskopia av dina data så att du har originaldata intakta
Hur fungerar detta?
* (asterisk sign) är ett jokertecken som kan representera valfritt antal tecken.
När jag använder det efter kommatecken (i fältet "Hitta vad") och sedan klickar på Ersätt alla -knappen, hittar det det första komma i cellen och anser att det är en matchning.
Detta beror på att asterisk (*) tecknet anses matcha hela textsträngen som följer kommatecken.
Så när du trycker på knappen ersätt alla, ersätter den kommatecken och all text som följer.
Notera: Denna metod fungerar bra då har du bara ett komma i varje cell (som i vårt exempel). Om du har flera kommatecken skulle den här metoden alltid hitta det första komma och sedan ta bort allt efter det. Så du kan inte använda den här metoden om du vill ersätta all text efter det andra komma och behålla den första som den är.Om du vill ta bort alla tecken före komma, ändra fältet Sök efter vilket fält genom att ha stjärntecknet före komma (*, istället för,*)
Ta bort text med formler
Om du behöver mer kontroll över hur du hittar och ersätter text före eller efter ett specifikt tecken är det bättre att använda de inbyggda textformlerna i Excel.
Anta att du har nedanstående datamängd där du vill ta bort all text efter kommatecken.
Nedan är formeln för att göra detta:
= VÄNSTER (A2, FIND (",", A2) -1)
Ovanstående formel använder FIND -funktionen för att hitta kommatens position i cellen.
Detta positionsnummer används sedan av VÄNSTER -funktionen för att extrahera alla tecken före komma. Eftersom jag inte vill ha kommatecken som en del av resultatet har jag subtraherat 1 från sökformelns resulterande värde.
Detta var ett enkelt scenario.
Låt oss ta en lite komplex.
Antag att jag har den här datauppsättningen nedan där jag vill ta bort all text efter det andra komma.
Här är formeln som gör detta:
= VÄNSTER (A2, HITTA ("!", ERSÄTTNING (A2, ",", "!", 2))-1)
Eftersom denna datamängd har flera kommatecken kan jag inte använda FIND -funktionen för att få positionen för det första komma och extrahera allt till vänster om det.
Jag måste på något sätt ta reda på positionen för det andra komma och sedan extrahera allt som är till vänster om det andra kommat.
För att göra detta har jag använt funktionen SUBSTITUTE för att ändra det andra komma till ett utropstecken. Nu ger detta mig en unik karaktär i cellen. Jag kan nu använda utropsteckenets position för att extrahera allt till vänster om det andra kommatecknet.
Denna position av utropstecknet används i VÄNSTER -funktionen för att extrahera allt före det andra komma.
Än så länge är allt bra!
Men tänk om det finns ett inkonsekvent antal kommatecken i datamängden.
Till exempel, i nedanstående datamängd har vissa celler två kommatecken och vissa celler har tre kommatecken, och jag måste extrahera all text före det sista kommatecknet.
I det här fallet måste jag på något sätt ta reda på positionen för kommans sista förekomst och sedan extrahera allt till vänster om det.
Nedan är formeln som gör det
= VÄNSTER (A2, HITTA ("!", SUBSTITUTE (A2, ",", "!", LEN (A2) -LEN (SUBSTITUTE (A2, ",", ""))))-1)
Ovanstående formel använder LEN -funktionen för att hitta textens totala längd i cellen såväl som textens längd utan kommatecken.
När jag subtraherar dessa två värden ger det mig det totala antalet kommatecken i cellen.
Så det skulle ge mig 3 för cell A2 och 2 för cell A4.
Detta värde används sedan inom SUBSTITUTE -formeln för att ersätta det sista kommat med ett utropstecken. Och sedan kan du använda den vänstra funktionen för att extrahera allt som är till vänster om utropstecknet (där det sista kommat var)
Som du kan se i exemplen kan du hantera många olika situationer med hjälp av en kombination av textformler.
Eftersom resultatet är kopplat till originaldata, uppdateras resultatet automatiskt när du ändrar originaldata.
Ta bort text med Flash Fill
Flash Fill är ett verktyg som introducerades i Excel 2013 och är tillgängligt i alla versioner efter det.
Det fungerar genom att identifiera mönster när du manuellt matar in data och sedan extrapoleras för att ge dig data för hela kolumnen.
Så om du vill ta bort texten före eller efter ett specifikt tecken behöver du bara visa flash fairy hur resultatet skulle se ut (genom att skriva in det manuellt ett par gånger), och flash fill skulle automatiskt förstå mönstret och ge dig resultaten.
Låt mig visa dig detta med ett exempel.
Nedan har jag datauppsättningen där jag vill ta bort all text efter kommatecken.
Här är stegen för att göra detta med Flash Fill:
- I cell B2, som är den intilliggande kolumnen i våra data, anger du manuellt ‘Jeffery Haggins’ (vilket är det förväntade resultatet)
- I cell B3 anger du 'Tim Scott' (vilket är det förväntade resultatet för den andra cellen)
- Välj intervallet B2: B10
- Klicka på fliken Hem
- Klicka på rullgardinsmenyn Fyll i gruppen Redigering
- Klicka på Flash Fill
Stegen ovan skulle ge dig resultatet enligt nedan:
Du kan också använda snabbtangenten Flash Fill Control + E efter att ha markerat cellerna i resultatkolumnen (kolumn B i vårt exempel)
Flash Fill är ett underbart verktyg och i de flesta fall kan den känna igen mönstret och ge dig rätt resultat. men i vissa fall kanske den inte kan känna igen mönstret korrekt och kan ge dig fel resultat.
Så se till att du dubbelkollar dina Flash Fill-resultat
Och precis som vi har tagit bort all text efter ett specifikt tecken med flashfyllning, kan du använda samma steg för att ta bort texten före ett specifikt tecken. visa bara flash fyll hur resultatet ska se ut som mitt internet manuellt i den intilliggande kolumnen, och det skulle göra resten.
Ta bort text med VBA (anpassad funktion)
Hela konceptet med att ta bort texten före eller efter ett specifikt tecken är beroende av att hitta karaktärens position.
Som om det ses ovan innebär det att hitta den sista förekomsten av den karaktären bra att använda en sammansättning av formler.
Om detta är något du behöver göra ganska ofta kan du förenkla denna process genom att skapa en anpassad funktion med VBA (kallad User Defined Functions).
När du har skapat den kan du återanvända den här funktionen om och om igen. Det är också mycket enklare och lättare att använda (eftersom de flesta tunga lyft görs med VBA -koden i bakänden).
Under VBA -koden som du kan använda för att skapa den anpassade funktionen i Excel:
Funktion LastPosition (rCell As Range, rChar As String) 'Denna funktion ger den sista positionen för det angivna tecknet' Denna kod har utvecklats av Sumit Bansal (https://trumpexcel.com) Dim rLen som heltal rLen = Len (rCell) För i = rLen Till 1 Steg -1 Om Mitt (rCell, i - 1, 1) = rChar Sedan LastPosition = i - 1 Avsluta funktion Avsluta Om Nästa i Avsluta funktion
Du måste placera VBA -koden i en vanlig modul i VB -redigeraren eller i den personliga makro -arbetsboken. När du väl har det där kan du använda det som alla andra vanliga kalkylbladsfunktioner i arbetsboken.
Denna funktion tar 2 argument:
- Cellreferensen som du vill hitta den sista förekomsten av ett specifikt tecken eller en textsträng för
- Tecknet eller textsträngen vars position du behöver hitta
Anta att du nu har nedanstående datamängd och att du vill ta bort all text efter det sista kommatecknet och bara ha texten före det sista kommatecknet.
Nedan är formeln som gör det:
= LEFT (A2, LastPosition (A2, ",")-1)
I ovanstående formel har jag specificerat för att hitta positionen för det sista kommat. Om du vill hitta positionen för ett annat tecken eller en textsträng, bör du använda det som det andra argumentet i funktionen.
Som du kan se är detta mycket kortare och lättare att använda jämfört med den långa textformeln som vi använde föregående avsnitt
Om du lägger VBA -koden i en modul i en arbetsbok skulle du bara kunna använda den här anpassade funktionen i den specifika arbetsboken. Om du vill använda detta i alla arbetsböcker på ditt system måste du kopiera och klistra in den här koden i Personal Macro Workbook.
Så det här är några av de e -postmeddelanden du kan använda för att snabbt ta bort texten före eller efter ett specifikt tecken i Excel.
Om det är en enkel engångsgrej kan du göra det med att hitta och ersätta. tänk om det är lite mer komplext, då måste du använda en kombination av inbyggda Excel -formler, eller till och med skapa din egen anpassade formel med VBA.
Jag hoppas att du fann denna handledning användbar.
