Vi vet att TRIM och CLEAN Excel -funktioner används för att rensa bort oskrivbara tecken och extra mellanslag från strängar, men de hjälper inte mycket för att identifiera strängar som innehåller något specialtecken som @ eller! etc. I sådana fall använder vi UDF.
Så om du vill veta om en sträng innehåller några specialtecken hittar du ingen Excel -formel eller funktion för att göra det. Vi kan hitta specialtecknen som finns på tangentbordet genom att skriva manuellt men du kan inte veta om det finns symboler i strängen eller inte.
Att hitta specialtecken kan vara mycket viktigt för datarensning. Och i vissa fall måste det göras. Så hur gör vi detta i Excel? Hur kan vi veta om en sträng innehåller några specialtecken? Tja, vi kan använda UDF för att göra det.
Nedanstående formel returnerar SANT om någon cell innehåller andra tecken än 1 till 0 och A till Z (i båda fallen). Om det inte hittar några specialtecken returnerar det FALSKT.
Generisk formel
| = ContainsSpecialCharacters (sträng) |
Sträng: Strängen som du vill kontrollera efter specialtecken.
För att denna formel ska fungera måste du ange koden nedan i modulen i din arbetsbok.
Så öppna Excel. Tryck på ALT+F11 för att öppna VBE. Sätt in en modul från menyn Infoga. Kopiera koden nedan och klistra in den i modulen.
Function ContainsSpecialCharacters (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 "Till" z "," "ContainsSpecialCharacters = Falskt fall Else ContainsSpecialCharacters = True Exit för slutet Välj nästa slutfunktion
Nu är funktionen redo att användas.
Gå till kalkylbladet i arbetsboken som innehåller strängarna som du vill kontrollera.
Skriv nedanstående formel i cell C2:
| = ContainsSpecialCharacters (B13) |
Den returnerar TRUE för den första strängen eftersom den innehåller ett specialtecken. När du kopierar formeln ner visas FALSE för B14 -sträng och så vidare.
Men konstigt nog visar det SANT för den sista strängen "Exceltip.com". Det är för att den innehåller en punkt (.). Men varför så? Låt oss undersöka koden för att förstå.
Hur fungerar funktionen?
Vi itererar igenom alla tecken i strängen med hjälp av For -slingan och mitten av VBA. Mid -funktionen extraherar ett tecken i taget från strängen och lagrar det i ch -variabel.
För I = 1 Till Len (str) ch = Mid (str, I, 1)
Nu kommer huvuddelen. Vi använder select case statement för att kontrollera vad ch -variabeln innehåller.
Vi säger åt VBA att kontrollera om ch innehåller något av de definierade värdena. Jag har definierat 0 till 9, A till Z, a till z och "" (mellanslag). Om c
h innehåller någon av dessa, sätter vi dess värde till False.
Välj fall ch ”0” till ”9”, ”A” till ”Z”, ”a” till ”z”, ”” ContainsSpecialCharacters = False
Du kan se att vi inte har en punkt (.) På listan och det är därför vi får SANT för strängen som innehåller punkt. Du kan lägga till vilket tecken som helst i listan för att undantas från formeln.
Om ch innehåller något annat tecken än de listade tecknen, ställer vi in funktionens resultat till True och avslutar slingan där.
Case Else ContainsSpecialCharacters = True Exit For
Så ja så här fungerar det. Men om du vill rengöra specialtecken måste du göra några ändringar i funktionen.
Hur rengör jag specialtecken från strängar i Excel?
För att rensa specialtecken från en sträng i Excel har jag skapat en annan anpassad funktion i VBA. Syntaxen för funktionen är:
| = CleanSpecialCharacters (sträng) |
Denna funktion returnerar en rengjord version av strängen som skickas in i den. Den nya strängen innehåller inga specialtecken.
Koden för denna funktion går så här:
Funktion CleanSpecialCharacters (str As String) Dim nstr As String nstr = str For I = 1 To Len (str) ch = Mid (str, I, 1) Välj Case ch Case "0" To "9", "A" To " Z "," a "Till" z "," "'' Gör ingenting Fall Annat nstr = Ersätt (nstr, ch," ") Slut Välj nästa CleanSpecialCharacters = nstr Avsluta funktion
Kopiera detta i samma modul som du kopierade i koden ovan.
När du använder den här formeln på strängarna så kommer resultaten att bli:
Du kan se att varje specialtecken tas bort från strängen inklusive pricken.
Hur fungerar det?
Det fungerar på samma sätt som ovanstående funktion. Den enda skillnaden är att denna funktion ersätter varje specialtecken med ett nulltecken. Bildar en ny sträng och returnerar den här strängen.
Välj Fall ch 0 "Till" 9 "," A "Till" Z "," a "Till" z "," "'ContainsSpecialCharacters = Falskt fall Else nstr = Ersätt (nstr, ch," ") Avsluta Välj
Om du vill undanta något tecken från att bli rengjord kan du lägga till det i listan i koden. Excel kommer att förlåta den där specialkaraktären.
Så ja killar, så här kan du hitta och ersätta specialtecken från en sträng i Excel. Jag hoppas att detta var tillräckligt förklarande och hjälpsamt. Om det inte hjälper dig att lösa ditt problem, meddela oss det i kommentarfältet nedan.
Så här använder du TRIM -funktionen i Excel: TRIM -funktionen används för att trimma strängar och rengöra alla bakre eller ledande mellanslag från strängen. Detta hjälper oss i rengöringsprocessen av data mycket.
Så här använder du CLEAN -funktionen i Excel: Clean -funktionen används för att rensa bort oskrivbara tecken från strängen. Denna funktion används mestadels med TRIM -funktionen för att städa upp importerade utländska data.
Ersätt text från slutet av en sträng med början från variabel position: För att ersätta text från slutet av strängen använder vi funktionen REPLACE. REPLACE -funktionen använder textens position i strängen för att ersätta den.
Så här kontrollerar du om en sträng innehåller en av många texter i Excel: För att kontrollera om en sträng innehåller någon av flera text använder vi denna formel. Vi använder SUMM -funktionen för att summera alla matchningar och utför sedan en logik för att kontrollera om strängen innehåller någon av de flera strängarna.
Räkna celler som innehåller specifik text: En enkel COUNTIF -funktion gör magin. För att räkna antalet flera celler som innehåller en given sträng använder vi jokerteckenoperatorn med funktionen RÄNTA.
Excel REPLACE vs SUBSTITUTE -funktion: Funktionerna REPLACE och SUBSTITUTE är de mest missförstådda funktionerna. För att hitta och ersätta en given text använder vi ersättningsfunktionen. Där REPLACE används för att ersätta ett antal tecken i strängen …
Populära artiklar:
50 Excel -genvägar för att öka din produktivitet | Bli snabbare på din uppgift. Dessa 50 genvägar får dig att arbeta ännu snabbare med Excel.
Hur man använder Excel VLOOKUP -funktion| Detta är en av de mest använda och populära Excel -funktionerna som används för att leta upp värde från olika intervall och ark.
Hur man använder Excel COUNTIF -funktion| Räkna värden med förhållanden med denna fantastiska funktion. Du behöver inte filtrera dina data för att räkna specifika värden. Countif -funktionen är avgörande för att förbereda din instrumentpanel.
Hur man använder SUMIF -funktionen i Excel | Detta är en annan viktig instrumentpanel. Detta hjälper dig att summera värden på specifika förhållanden.