Vi vet at TRIM og CLEAN Excel -funksjoner brukes til å rydde opp i utskrivbare tegn og ekstra mellomrom fra strenger, men de hjelper ikke mye med å identifisere strenger som inneholder noen spesialtegn som @ eller! etc. I slike tilfeller bruker vi UDF.
Så hvis du vil vite om en streng inneholder spesialtegn, finner du ingen Excel -formel eller funksjon for å gjøre det. Vi kan finne spesialtegnene på tastaturet ved å skrive manuelt, men du kan ikke vite om det er symboler i strengen eller ikke.
Å finne spesialtegn kan være svært viktig for datarensing. Og i noen tilfeller må det gjøres. Så hvordan gjør vi dette i Excel? Hvordan kan vi vite om en streng inneholder spesialtegn? Vel, vi kan bruke UDF til å gjøre det.
Formelen nedenfor vil returnere TRUE hvis en celle inneholder andre tegn enn 1 til 0 og A til Z (i begge tilfeller). Hvis den ikke finner noen spesialtegn, returnerer den FALSK.
Generisk formel
| = InneholderSpecialCharacters (streng) |
Streng: Strengen du vil se etter spesialtegn.
For at denne formelen skal fungere, må du sette koden nedenfor i modulen i arbeidsboken.
Så åpne Excel. Trykk ALT+F11 for å åpne VBE. Sett inn en modul fra Sett inn -menyen. Kopier koden nedenfor og lim den inn i modulen.
Funksjonen inneholderSpesialtegn (str som streng) Som boolsk For I = 1 Til Len (str) ch = Midt (str, I, 1) Velg store bokstaver bokstav "0" til "9", "A" til "Z", "a "Til" z "," "ContainsSpecialCharacters = Falsk bokstav Ellers ContainsSpecialCharacters = True Exit For End Velg Next End -funksjon
Nå er funksjonen klar til bruk.
Gå til regnearket i arbeidsboken som inneholder strengene du vil kontrollere.
Skriv formelen nedenfor i celle C2:
| = InneholderSpecialCharacters (B13) |
Den returnerer SANN for den første strengen siden den inneholder et spesialtegn. Når du kopierer formelen ned, viser den FALSK for B14 -streng og så videre.
Men merkelig viser den SANN for den siste strengen "Exceltip.com". Det er fordi den inneholder en prikk (.). Men hvorfor så? La oss undersøke koden for å forstå.
Hvordan fungerer funksjonen?
Vi gjentar alle tegnene i strengen ved å bruke For -løkken og midtfunksjonen til VBA. Midtfunksjonen trekker ut ett tegn om gangen fra strengen og lagrer det i ch -variabelen.
For I = 1 Til Len (str) ch = Midt (str, I, 1)
Nå kommer hoveddelen. Vi bruker select case statement til å sjekke hva ch -variabelen inneholder.
Vi forteller VBA å sjekke om ch inneholder noen av de definerte verdiene. Jeg har definert 0 til 9, A til Z, a til z og "" (mellomrom). Hvis c
h inneholder noen av disse, setter vi verdien til Falsk.
Velg mellom bokstaver i bokstaver "0" til "9", "A" til "Z", "a" til "z", "" ContainsSpecialCharacters = False
Du kan se at vi ikke har en prikk (.) På listen, og det er derfor vi får TRUE for strengen som inneholder prikk. Du kan legge til et hvilket som helst tegn i listen for å bli unntatt formelen.
Hvis ch inneholder et annet tegn enn de listede tegnene, setter vi funksjonens resultat til True og avslutter løkken akkurat der.
Case Else ContainsSpecialCharacters = True Exit For
Så ja, slik fungerer det. Men hvis du vil rense spesialtegn, må du gjøre noen endringer i funksjonen.
Hvordan rengjøre spesialtegn fra strenger i Excel?
For å rense spesialtegn fra en streng i Excel, har jeg opprettet en annen egendefinert funksjon i VBA. Syntaksen til funksjonen er:
| = CleanSpecialCharacters (streng) |
Denne funksjonen returnerer en renset versjon av strengen som ble sendt inn i den. Den nye strengen vil ikke inneholde noen av spesialtegnene.
Koden for denne funksjonen går slik:
Funksjon CleanSpecialCharacters (str As String) Dim nstr As String nstr = str For I = 1 To Len (str) ch = Midt (str, I, 1) Velg Case ch Case "0" To "9", "A" To " Z "," a "To" z "," "'Do nothing Case Else nstr = Replace (nstr, ch," ") End Velg neste CleanSpecialCharacters = nstr Avslutt funksjon
Kopier dette i samme modul som du kopierte i koden ovenfor.
Når du bruker denne formelen på strengene, blir resultatene slik:
Du kan se at hvert spesialtegn er fjernet fra strengen inkludert prikken.
Hvordan virker det?
Det fungerer på samme måte som funksjonen ovenfor. Den eneste forskjellen er at denne funksjonen erstatter hvert spesialtegn med et nulltegn. Danner en ny streng og returnerer denne strengen.
Velg 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 unnta et tegn fra å bli renset, kan du legge det til i listen i koden. Excel vil tilgi det spesielle tegnet.
Så ja gutta, dette er hvordan du kan finne og erstatte spesialtegn fra en streng i Excel. Jeg håper dette var forklarende nok og nyttig. Hvis det ikke hjelper deg med å løse problemet, gi oss beskjed i kommentarfeltet nedenfor.
Slik bruker du TRIM -funksjonen i Excel: TRIM -funksjonen brukes til å trimme strenger og rense eventuelle etterfølgende eller ledende mellomrom fra strengen. Dette hjelper oss i rengjøringsprosessen av data mye.
Slik bruker du CLEAN -funksjonen i Excel: Clean -funksjonen brukes til å rydde opp i utskrivbare tegn fra strengen. Denne funksjonen brukes mest med TRIM -funksjonen for å rydde opp importerte utenlandske data.
Erstatt tekst fra enden av en streng fra variabel posisjon: For å erstatte tekst fra enden av strengen bruker vi REPLACE -funksjonen. REPLACE -funksjonen bruker plasseringen av tekst i strengen for å erstatte den.
Hvordan sjekke om en streng inneholder en av mange tekster i Excel: For å finne ut om en streng inneholder flere tekst, bruker vi denne formelen. Vi bruker SUM -funksjonen til å oppsummere alle treffene og deretter utføre en logikk for å sjekke om strengen inneholder noen av de flere strengene.
Tell celler som inneholder spesifikk tekst: En enkel COUNTIF -funksjon vil gjøre magien. For å telle antallet flere celler som inneholder en gitt streng bruker vi jokertegnoperatoren med COUNTIF -funksjonen.
Excel REPLACE vs SUBSTITUTE -funksjon: Funksjonene REPLACE og SUBSTITUTE er de mest misforståtte funksjonene. For å finne og erstatte en gitt tekst bruker vi SUBSTITUTE -funksjonen. Hvor REPLACE brukes til å erstatte et antall tegn i strengen …
Populære artikler:
50 Excel -snarveier for å øke produktiviteten | Bli raskere på oppgaven din. Disse 50 snarveiene får deg til å jobbe enda raskere med Excel.
Slik bruker du Excel VLOOKUP -funksjon| Dette er en av de mest brukte og populære funksjonene til excel som brukes til å slå opp verdi fra forskjellige områder og ark.
Slik bruker du Excel COUNTIF -funksjon| Tell verdier med betingelser ved hjelp av denne fantastiske funksjonen. Du trenger ikke å filtrere dataene dine for å telle spesifikke verdier. Countif -funksjonen er avgjørende for å forberede dashbordet.
Slik bruker du SUMIF -funksjonen i Excel | Dette er en annen viktig funksjon på dashbordet. Dette hjelper deg med å oppsummere verdier på spesifikke forhold.