Hoe u de Regex-functie van Excel kunt gebruiken om uw zoekopdrachten te verbeteren

Filteren en zoeken in Excel is geen eenvoudige taak. Regex-functies brengen daar verandering in. Nu kunt u precies opgeven wat u nodig hebt: complexe patronen, gedeeltelijke overeenkomsten of gestructureerde gegevensextractie, zonder dat u daar moeite voor hoeft te doen.

Inhoudsopgave

• Wat is Regex?
• Zoeken naar patronen
• Aanvullende gegevens met behulp van Regex
• Zoeken en vervangen met Regex
• Regex combineren met andere functies

Wat is Regex?

Regex is een type patroon dat wordt gebruikt om in een tekst of tekenreeks naar overeenkomsten te zoeken. Heb je je ooit afgevraagd hoe websites je kunnen vertellen dat de e-mailsjabloon die je op de inlogpagina hebt ingevoerd, ongeldig is? Hier ziet u een voorbeeld van een regex-patroon met een e-mailhandtekening in actie.

Reguliere expressies zijn niet uniek voor Excel. Ze zijn beschikbaar in veel teksteditors, programmeertalen, opdrachtregelprogramma's, IDE's en zelfs in Excel's concurrent Google Sheets .

Regex lijkt misschien ingewikkeld en dat is het ook als u het optimaal wilt benutten. U hoeft echter geen programmeur te zijn om het effectief te gebruiken. In sommige gevallen is het al voldoende als u weet hoe u een aantal basissymbolen en -patronen moet gebruiken. Deze gids zal zo eenvoudig mogelijk worden gehouden, zodat u er meteen mee aan de slag kunt.

In deze handleiding worden de volgende symbolen gebruikt:

Eenvoudige reguliere-expressiepatronen die u met behulp van deze symbolen kunt bouwen, zijn onder andere:

Regex-functies zijn vooraf gedefinieerde Excel-formules waarmee u een patroon kunt definiëren voor het zoeken en bewerken van tekstreeksen. Er zijn momenteel 3 regex-functies. We zullen zien hoe we ze afzonderlijk en in combinatie met andere functies kunnen gebruiken.

Zoeken naar patronen

De eerste functie die we zullen bekijken is REGEXTEST. Deze functie neemt een tekstreeks die u wilt gebruiken voor de zoekopdracht en een regex-patroon. Vervolgens wordt het laatste patroon gebruikt om overeenkomsten in het eerste patroon te vinden. De functie retourneert True of False.

De syntaxis van de functie REGEXTEST is als volgt:

REGEXTEST(string_to_search, regex_pattern_to_use, [case_senstivity])

De eerste twee parameters, string_to_search en regex_pattern_to_use, spreken voor zich. De parameter [case_sensitivity] is optioneel. Alles wat tussen vierkante haken staat in de Excel-syntaxis is optioneel. Hiermee wordt aangegeven of de zoekopdracht hoofdlettergevoelig (0) of niet-hoofdlettergevoelig (1) moet zijn. Standaard is hoofdlettergevoelig.

In het voorbeeld wordt REGEXTEST gebruikt om te controleren of de gebruiker een geldig e-mailadres heeft ingevoerd, met behulp van de volgende formule:

REGEXTEST(B3, "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$")

Hier zoeken we in cel B3 om te zien of deze een e-mailadres bevat, met behulp van het onderstaande reguliere-expressiepatroon:

^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$

Als u de formule in cel C3 zet en [email protected] in cel B3 invoert, zal de formule True retourneren omdat deze overeenkomt met de e-mailhandtekening.

Aanvullende gegevens met behulp van Regex

Laten we nu eens naar de functie REXEXEXTRACT kijken. Deze functie retourneert een subtekenreeks (deel van een tekenreeks) die overeenkomt met het opgegeven regex-patroon.

De syntaxis van de functie REXEXEXTRACT is als volgt:

REGEXEXTRACT(string_to_search, regex_pattern_to_use, [return_mode], [case_senstivity])

Laten we verdergaan met het e-mailvoorbeeld en een formule toevoegen aan cel B4 om de gebruikersnaam uit het e-mailgedeelte te extraheren.

De formule ziet er dan als volgt uit:

=REGEXEXTRACT(B3, "([^@]+)")

In deze formule halen we alles vóór het @ -symbool uit het e-mailadres dat in B3 is ingevoerd.

Zoeken en vervangen met Regex

De laatste regex-functie die we zullen bekijken is REGEXREPLACE. Deze functie is vergelijkbaar met de VERVANGEN-functie van Excel, maar ondersteunt ook RegEx. Hierbij wordt de tekstreeks die u wilt wijzigen gecontroleerd en of er subreeksen zijn die overeenkomen met het gedefinieerde regex-patroon. Als de tekenreeks wordt gevonden, wordt deze vervangen door de opgegeven vervangende tekenreeks.

De syntaxis van de REGEXREPLACE-functie is als volgt:

REGEXREPLACE(string_to_modify, regex_pattern_to_use, replacement_string, [number_of_occurrences], [case_senstivity])

Dit zijn de belangrijke parameters waar u bij deze functie op moet letten:

• string_to_modify : De tekstreeks die u wilt wijzigen.
• replacement_string : String waarmee de substring moet worden vervangen.
• number_of_occurrences : De exacte instanties die u wilt vervangen.

Hier is een voorbeeld van hoe u de functie kunt gebruiken om het gebruikersnaamgedeelte van een e-mail te vervangen door een andere tekstreeks:

=REGEXREPLACE(B3, "^[^@]+", "jane.doe")

De waarde van B3 is [email protected] en nadat we de bovenstaande formule in cel C3 invoeren, zal deze [email protected] retourneren.

Regex combineren met andere functies

U kunt regex-functies ook combineren met andere functies in Excel. U kunt bijvoorbeeld de functie REGEXT.TOETS combineren met de ALS-instructie van Excel en passende berichten weergeven op basis van de resultaten.

Hier is een voorbeeldformule:

=IF(REGEXTEST(B3, "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$"), "This is a valid email address!", "The email address is invalid!")

Deze formule gebruikt een ALS-instructie om te controleren of het in cel B6 ingevoerde e-mailadres geldig is en geeft vervolgens Dit is een geldig e-mailadres! weer. indien WAAR of Het e-mailadres is ongeldig! indien ONWAAR . Bovendien kunt u deze formule combineren met de functie VIND.ALLES om snel gegevens in Excel te vinden.

Dit is een goede manier om aan de slag te gaan met RegEx in Excel. De toepassingsmogelijkheden en mogelijkheden worden alleen beperkt door uw verbeelding.