Generieke formule
=LOWER(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"("," "),")"," "),"-"," "),":"," "),";"," "),"!"," "),","," "),"."," ")))
Samenvatting
Om een deel van de natuurlijke complexiteit van tekst te verwijderen (interpunctie verwijderen, hoofdlettergebruik normaliseren, extra spaties verwijderen), kunt u een formule gebruiken die is gebaseerd op de functie VERVANGEN, met hulp van de functies TRIM en LOWER.
Context
Het kan voorkomen dat u een deel van de variabiliteit van tekst moet verwijderen voordat u een andere bewerking uitvoert. Een voorbeeld is wanneer u specifieke woorden binnen grotere tekstreeksen wilt tellen. Omdat Excel geen ondersteuning biedt voor reguliere expressies, kunt u geen nauwkeurige overeenkomsten maken. Als u bijvoorbeeld wilt tellen hoe vaak het woord "vos" in een cel voorkomt, telt u uiteindelijk "vossen". U kunt zoeken naar "vos" (met een spatie), maar dat mislukt met "vos" of "vos". Een oplossing is om de tekst eerst te vereenvoudigen met een formule in een hulpkolom en vervolgens tellingen uit te voeren op de vereenvoudigde versie. Het voorbeeld op deze pagina laat een manier zien om dit te doen.
Uitleg
De formule die in dit voorbeeld wordt weergegeven, gebruikt een reeks geneste SUBSTITUTE-functies om haakjes, koppeltekens, dubbele punten, puntkomma's, uitroeptekens, komma's en punten te verwijderen. Het proces verloopt van binnen naar buiten, waarbij elke VERVANGING een teken vervangt door een enkele spatie en vervolgens wordt overgedragen aan de volgende VERVANGING. De binnenste VERVANGING verwijdert de linkerhaakjes, en het resultaat wordt overgedragen aan de volgende VERVANGING, die de rechterhaakjes verwijdert, enzovoort.
In de onderstaande versie zijn regeleinden toegevoegd voor leesbaarheid en om het gemakkelijker te maken om vervangingen te bewerken. Excel geeft niet om regeleinden in formules, dus u kunt de formule gebruiken zoals deze is.
= LOWER( TRIM( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( A1, "("," "), ")"," "), "-"," "), ":"," "), ";"," "), "!"," "), ","," "), "."," ")))
Nadat alle vervangingen zijn voltooid, wordt het resultaat door TRIM uitgevoerd om spaties te normaliseren en vervolgens de LOWER-functie om alle tekst in kleine letters te forceren.
Opmerking: u moet de daadwerkelijke vervangingen aanpassen aan uw gegevens.
Een voorloop- en volgspatie toevoegen
In sommige gevallen wilt u misschien een spatie aan het begin en einde van de opgeschoonde tekst toevoegen. Als u bijvoorbeeld woorden nauwkeurig wilt tellen, wilt u misschien zoeken naar het woord omgeven door spaties (dwz zoeken op "vos", "kaart") om valse overeenkomsten te vermijden. Om een voorloop- en volgspatie toe te voegen, voegt u gewoon een spatie ("") aan het begin en einde samen:
=" "&formula&" "
Waar "formule" de langere formule hierboven is.
