We weten dat de TRIM- en CLEAN Excel-functies worden gebruikt om niet-afdrukbare tekens en extra spaties uit tekenreeksen op te ruimen, maar ze helpen niet veel bij het identificeren van tekenreeksen die een speciaal teken bevatten, zoals @ of ! enz. In dergelijke gevallen gebruiken we UDF's.
Dus als je wilt weten of een string speciale tekens bevat, zul je daar geen Excel-formule of -functie voor vinden. We kunnen de speciale tekens op het toetsenbord vinden door handmatig te typen, maar u kunt niet weten of er symbolen in de tekenreeks zitten of niet.
Het vinden van speciale tekens kan erg belangrijk zijn voor het opschonen van gegevens. En in sommige gevallen moet het gebeuren. Dus hoe doen we dit in Excel? Hoe kunnen we weten of een string speciale tekens bevat? Welnu, we kunnen UDF hiervoor gebruiken.
De onderstaande formule retourneert TRUE als een cel andere tekens bevat dan 1 tot 0 en A tot Z (in beide gevallen). Als het geen speciale tekens vindt, wordt FALSE geretourneerd.
Generieke formule
| =BevatSpeciale Tekens(string) |
Snaar: De tekenreeks die u op speciale tekens wilt controleren.
Om deze formule te laten werken, moet u de onderstaande code in de module van uw werkmap plaatsen.
Dus Excel openen. Druk op ALT+F11 om VBE te openen. Voeg een module in vanuit het menu Invoegen. Kopieer onderstaande code en plak deze in de module.
Functie BevatSpeciale Karakters(str As String) As Boolean For I = 1 To Len(str) ch = Mid(str, I, 1) Selecteer Case ch Case "0" To "9", "A" To "Z", "a " To "z", " " BevatSpecialCharacters = False Case Anders BevatSpecialCharacters = True Exit For End Selecteer Volgende End Functie
Nu is de functie klaar voor gebruik.
Ga naar het werkblad in de werkmap met de tekenreeksen die u wilt controleren.
Schrijf de onderstaande formule in cel C2:
| =BevatSpeciale Tekens(B13) |
Het retourneert TRUE voor de eerste tekenreeks omdat deze een speciaal teken bevat. Wanneer u de formule naar beneden kopieert, wordt FALSE weergegeven voor B14-tekenreeks, enzovoort.
Maar vreemd genoeg toont het TRUE voor de laatste string "Exceltip.com". Het is omdat het een punt (.) bevat. Maar waarom zo? Laten we de code eens bekijken om het te begrijpen.
Hoe werkt de functie?
We herhalen alle karakters van de string met behulp van de For-lus en mid-functie van VBA. De functie Mid haalt één teken tegelijk uit de tekenreeks en slaat deze op in de variabele ch.
Voor I = 1 Tot Len(str) ch = Mid(str, I, 1)
Nu komt het belangrijkste deel. We gebruiken de select case-statement om te controleren wat de ch-variabele bevat.
We vertellen VBA om te controleren of ch een van de gedefinieerde waarden bevat. Ik heb 0 tot 9, A tot Z, a tot z en " " (spatie) gedefinieerd. Als c
h een van deze bevat, stellen we de waarde ervan in op False.
Selecteer Case ch Case "0" Tot "9", "A" Tot "Z", "a" Tot "z", " " BevatSpecialCharacters = False
Je kunt zien dat we geen punt (.) in de lijst hebben en daarom krijgen we TRUE voor de tekenreeks die punt bevat. U kunt elk teken aan de lijst toevoegen om te worden vrijgesteld van de formule.
Als ch een ander teken dan de vermelde tekens bevat, stellen we het resultaat van de functie in op True en beëindigen we de lus daar.
Case Else BevatSpecialCharacters = True Exit For
Dus ja zo werkt het. Maar als u speciale tekens wilt opschonen, moet u enkele wijzigingen in de functie aanbrengen.
Hoe speciale tekens uit strings in Excel te verwijderen?
Om speciale tekens uit een tekenreeks in Excel te verwijderen, heb ik een andere aangepaste functie in VBA gemaakt. De syntaxis van de functie is:
| =SchoneSpecialeTekens(string) |
Deze functie retourneert een opgeschoonde versie van de string die erin is doorgegeven. De nieuwe tekenreeks bevat geen van de speciale tekens.
De code van deze functie gaat als volgt:
Functie CleanSpecialCharacters(str As String) Dim nstr As String nstr = str For I = 1 To Len(str) ch = Mid(str, I, 1) Selecteer Case ch Case "0" To "9", "A" To " Z", "a" Tot "z", " " 'Niets doen Case Else nstr = Replace(nstr, ch, "") End Select Next CleanSpecialCharacters = nstr End Function
Kopieer dit in dezelfde module die u in de bovenstaande code hebt gekopieerd.
Wanneer u deze formule op de strings gebruikt, is dit hoe de resultaten zullen zijn:
Je kunt zien dat elk speciaal teken uit de tekenreeks is verwijderd, inclusief de punt.
Hoe werkt het?
Het werkt hetzelfde als de bovenstaande functie. Het enige verschil is dat deze functie elk speciaal teken vervangt door een null-teken. Vormt een nieuwe tekenreeks en retourneert deze tekenreeks.
Select Case ch Case "0" To "9", "A" To "Z", "a" To "z", " " 'ContainsSpecialCharacters = False Case Else nstr = Replace(nstr, ch, "") End Select
Als u een teken wilt uitsluiten van het opschonen, kunt u het toevoegen aan de lijst in de code. Excel zal dat speciale teken vergeven.
Dus ja jongens, dit is hoe je speciale tekens uit een string in Excel kunt vinden en vervangen. Ik hoop dat dit voldoende verhelderend en nuttig was. Als het u niet helpt bij het oplossen van uw probleem, laat het ons dan weten in de opmerkingen hieronder.
Hoe de TRIM-functie in Excel te gebruiken: De TRIM-functie wordt gebruikt om strings in te korten en eventuele volg- of voorloopspaties van strings te verwijderen. Dit helpt ons enorm bij het opschonen van data.
Hoe de CLEAN-functie in Excel te gebruiken?: De functie Opschonen wordt gebruikt om niet-afdrukbare tekens uit de tekenreeks te verwijderen. Deze functie wordt meestal gebruikt met de TRIM-functie om geïmporteerde buitenlandse gegevens op te schonen.
Vervang tekst vanaf het einde van een tekenreeks vanaf de variabele positie: Om tekst vanaf het einde van de string te vervangen, gebruiken we de REPLACE-functie. De functie VERVANGEN gebruikt de positie van tekst in de tekenreeks om deze te vervangen.
Hoe te controleren of een tekenreeks een van de vele teksten in Excel bevat: Om te controleren of een tekenreeks meerdere tekst bevat, gebruiken we deze formule. We gebruiken de SOM-functie om alle overeenkomsten op te tellen en voeren vervolgens een logica uit om te controleren of de string een van de meerdere strings bevat.
Cellen tellen die specifieke tekst bevatten: Een eenvoudige AANTAL.ALS-functie zal de magie doen. Om het aantal meerdere cellen te tellen die een bepaalde string bevatten, gebruiken we de jokertekenoperator met de AANTAL.ALS-functie.
Excel REPLACE vs SUBSTITUTE-functie: De functies REPLACE en SUBSTITUTE zijn de meest verkeerd begrepen functies. Om een bepaalde tekst te vinden en te vervangen, gebruiken we de SUBSTITUTE-functie. Waar REPLACE wordt gebruikt om een aantal karakters in string te vervangen…
Populaire artikels:
50 Excel-snelkoppelingen om uw productiviteit te verhogen | Word sneller in uw taak. Met deze 50 sneltoetsen werk je nog sneller in Excel.
Hoe de Excel VERT.ZOEKEN-functie te gebruiken| Dit is een van de meest gebruikte en populaire functies van Excel die wordt gebruikt om waarde op te zoeken uit verschillende bereiken en bladen.
Hoe de Excel te gebruiken? AANTAL.ALS-functie| Tel waarden met voorwaarden met behulp van deze geweldige functie. U hoeft uw gegevens niet te filteren om specifieke waarden te tellen. Countif-functie is essentieel om uw dashboard voor te bereiden.
Hoe de SUMIF-functie in Excel te gebruiken? | Dit is een andere essentiële functie van het dashboard. Dit helpt u bij het optellen van waarden voor specifieke voorwaarden.