Vi ved, at TRIM og CLEAN Excel -funktioner bruges til at rydde op i tegn, der ikke kan udskrives, og ekstra mellemrum fra strenge, men de hjælper ikke meget med at identificere strenge, der indeholder specialtegn som @ eller! osv. I sådanne tilfælde bruger vi UDF'er.
Så hvis du vil vide, om en streng indeholder specialtegn, finder du ikke nogen Excel -formel eller funktion til at gøre det. Vi kan finde specialtegnene på tastaturet ved at skrive manuelt, men du kan ikke vide, om der er symboler i strengen eller ej.
At finde specialtegn kan være meget vigtigt til datarensning. Og i nogle tilfælde skal det gøres. Så hvordan gør vi dette i Excel? Hvordan kan vi vide, om en streng indeholder specialtegn? Nå, vi kan bruge UDF til at gøre det.
Nedenstående formel returnerer SAND, hvis en celle indeholder andre tegn end 1 til 0 og A til Z (i begge tilfælde). Hvis det ikke finder nogen specialtegn, returnerer det FALSKT.
Generisk formel
| = IndeholderSpecialCharacters (streng) |
Snor: Strengen, du vil kontrollere for specialtegn.
For at denne formel fungerer, skal du sætte nedenstående kode i modulet i din projektmappe.
Så åbn Excel. Tryk på ALT+F11 for at åbne VBE. Indsæt et modul fra menuen Indsæt. Kopier koden herunder og indsæt den i modulet.
Funktion IndeholderSpecialCharacters (str As String) As Boolean For I = 1 To Len (str) ch = Mid (str, I, 1) Select Case ch Case "0" To "9", "A" To "Z", "a "Til" z "," "ContainsSpecialCharacters = Falsk sag Else ContainsSpecialCharacters = True Exit For End Vælg Next End -funktion
Nu er funktionen klar til brug.
Gå til regnearket i projektmappen, der indeholder de strenge, du vil kontrollere.
Skriv nedenstående formel i celle C2:
| = IndeholderSpecialCharacters (B13) |
Den returnerer SAND for den første streng, da den indeholder et specialtegn. Når du kopierer formlen ned, viser den FALSK for B14 -streng og så videre.
Men mærkeligt det viser SAND for den sidste streng "Exceltip.com". Det er fordi den indeholder en prik (.). Men hvorfor så? Lad os undersøge koden for at forstå.
Hvordan fungerer funktionen?
Vi gentager alle tegnene i strengen ved hjælp af For -loop og mid -funktionen i VBA. Mid -funktionen udtrækker et tegn ad gangen fra strengen og gemmer det i variabel ch.
For I = 1 Til Len (str) ch = Midt (str, I, 1)
Nu kommer hoveddelen. Vi bruger select case statement til at kontrollere, hvad ch -variablen indeholder.
Vi fortæller VBA at kontrollere, om ch indeholder nogen af de definerede værdier. Jeg har defineret 0 til 9, A til Z, a til z og "" (mellemrum). Hvis c
h indeholder nogen af disse, sætter vi værdien til Falsk.
Vælg Case ch Case "0" To "9", "A" To "Z", "a" To "z", "" ContainsSpecialCharacters = False
Du kan se, at vi ikke har en prik (.) På listen, og derfor får vi SAND for den streng, der indeholder prik. Du kan tilføje et hvilket som helst tegn til listen for at blive fritaget for formlen.
Hvis ch indeholder et andet tegn end de angivne tegn, sætter vi funktionens resultat til True og afslutter sløjfen lige der.
Case Else ContainsSpecialCharacters = True Exit For
Så ja sådan fungerer det. Men hvis du vil rense specialtegn, skal du foretage nogle ændringer i funktionen.
Sådan rengøres specialtegn fra strenge i Excel?
For at rense specialtegn fra en streng i Excel har jeg oprettet en anden brugerdefineret funktion i VBA. Funktionens syntaks er:
| = CleanSpecialCharacters (streng) |
Denne funktion returnerer en renset version af strengen, der er sendt ind i den. Den nye streng indeholder ikke nogen af specialtegnene.
Koden for denne funktion går således:
Funktion CleanSpecialCharacters (str As String) Dim nstr As String nstr = str For I = 1 To Len (str) ch = Midt (str, I, 1) Vælg Case ch Case "0" To "9", "A" To " Z "," a "Til" z "," "'Gør intet tilfælde Ellers nstr = Erstat (nstr, ch," ") Afslut Vælg næste CleanSpecialCharacters = nstr Afslut funktion
Kopier dette i det samme modul, som du kopierede i ovenstående kode.
Når du bruger denne formel på strengene, er resultatet sådan:
Du kan se, at hvert specialtegn fjernes fra strengen inklusive prikken.
Hvordan virker det?
Det fungerer på samme måde som ovenstående funktion. Den eneste forskel er, at denne funktion erstatter hvert specialtegn med et nultegn. Danner en ny streng og returnerer denne streng.
Vælg Case ch Case "0" To "9", "A" To "Z", "a" To "z", "" 'ContainsSpecialCharacters = False Case Else nstr = Replace (nstr, ch, "") End Select
Hvis du vil undtage et tegn fra at blive renset, kan du tilføje det til listen i koden. Excel vil tilgive den særlige karakter.
Så ja fyre, sådan kan du finde og erstatte specialtegn fra en streng i Excel. Jeg håber, at dette var forklarende nok og nyttigt. Hvis det ikke hjælper dig med at løse dit problem, så lad os vide det i kommentarfeltet herunder.
Sådan bruges TRIM -funktionen i Excel: TRIM -funktionen bruges til at trimme strenge og rense eventuelle efterfølgende eller førende mellemrum fra streng. Dette hjælper os meget i rengøringsprocessen af data.
Sådan bruges CLEAN -funktionen i Excel: Clean -funktion bruges til at rydde op i tegn, der ikke kan udskrives fra strengen. Denne funktion bruges mest med TRIM -funktionen til at rydde op i importerede udenlandske data.
Erstat tekst fra enden af en streng, der starter fra variabel position: For at erstatte tekst fra slutningen af strengen bruger vi funktionen REPLACE. Funktionen REPLACE bruger tekstens placering i strengen til at erstatte den.
Sådan kontrolleres, om en streng indeholder en af mange tekster i Excel: For at finde ud af, om en streng indeholder flere tekst, bruger vi denne formel. Vi bruger SUM -funktionen til at opsummere alle matches og udfører derefter en logik for at kontrollere, om strengen indeholder en af de flere strenge.
Tæl celler, der indeholder specifik tekst: En simpel COUNTIF -funktion gør magien. For at tælle antallet af flere celler, der indeholder en given streng, bruger vi wildcard -operatoren med COUNTIF -funktionen.
Excel REPLACE vs SUBSTITUTE -funktion: Funktionerne REPLACE og SUBSTITUTE er de mest misforståede funktioner. For at finde og erstatte en given tekst bruger vi SUBSTITUTE -funktionen. Hvor REPLACE bruges til at erstatte et antal tegn i en streng …
Populære artikler:
50 Excel -genveje til at øge din produktivitet | Få hurtigere til din opgave. Disse 50 genveje får dig til at arbejde endnu hurtigere i Excel.
Sådan bruges Excel VLOOKUP -funktion| Dette er en af de mest anvendte og populære funktioner i excel, der bruges til at slå værdi op fra forskellige områder og ark.
Sådan bruges Excel COUNTIF -funktion| Tæl værdier med betingelser ved hjælp af denne fantastiske funktion. Du behøver ikke at filtrere dine data for at tælle bestemte værdier. Countif -funktion er afgørende for at forberede dit dashboard.
Sådan bruges SUMIF -funktionen i Excel | Dette er endnu en vigtig instrumentbrætfunktion. Dette hjælper dig med at opsummere værdier på bestemte betingelser.