Excel: tekens, woorden of cijfers uit tekstreeksen verwijderen

Als je een lijst met tekstreeksen in Excel hebt die ongewenste tekens, cijfers of symbolen bevatten, kan het nodig zijn om bepaalde elementen te verwijderen op basis van hun positie of type. Of het nu gaat om het inkorten van tekens aan de linker-, rechter- of middenkant, of het verwijderen van specifieke tekens en cijfers, Excel biedt verschillende manieren om je gegevens op te schonen. In deze handleiding vind je stapsgewijze methoden met behulp van formules, User Defined Functions (UDF's) en ingebouwde functies om ongewenste tekens, woorden en cijfers eenvoudig uit je tekstreeksen te verwijderen.

Tekens verwijderen uit het begin, einde of midden van tekstreeksen

Voor velen van ons is het een veelvoorkomende taak om tekens uit het begin, einde of midden van tekstreeksen in Excel-werkbladen te verwijderen. In dit gedeelte worden enkele snelle en eenvoudige methoden geïntroduceerd om deze taak op te lossen.

1.1 Eerste n tekens uit tekstreeksen verwijderen

Als je de eerste n tekens uit een lijst met tekstreeksen wilt verwijderen, kunnen de volgende methoden je helpen.

Met behulp van formules

Normaal gesproken kun je om tekens aan het begin van tekstreeksen te verwijderen, gebruikmaken van de REPLACE-functie of een combinatie van de functies RIGHT en LEN.

REPLACE-functie om de eerste N tekens te verwijderen:

• "string": De tekstreeks waarvan je tekens wilt verwijderen;
• "num_chars": Het aantal tekens dat je wilt verwijderen.

Als voorbeeld: om de eerste2 tekens uit de cellen te verwijderen, gebruik je onderstaande formule en sleep je de vulgreep naar andere cellen, zie screenshot:

=REPLACE(A4, 1, 2, "")

RIGHT- en LEN-functies om de eerste N tekens te verwijderen:

• "string": De tekstreeks waarvan je tekens wilt verwijderen;
• "num_chars": Het aantal tekens dat je wilt verwijderen.

Om de eerste2 tekens uit cellen te verwijderen, gebruik je de volgende formule:

=RIGHT(A4,LEN(A4)-2)

Met behulp van een User Defined Function

Om de eerste n tekens uit cellen te verwijderen, kun je ook een User Defined Function maken. Volg hiervoor de onderstaande stappen:

1. Houd de toetsen "Alt + F11" ingedrukt om het venster "Microsoft Visual Basic for Applications" te openen.

2. Klik op "Invoegen" > "Module" en plak de volgende code in het modulevenster.

VBA-code: Eerste n tekens uit tekstreeksen verwijderen

Function removeFirstx(rng As String, cnt As Long)
'Updateby Extendoffice
removeFirstx = Right(rng, Len(rng) - cnt)
End Function

3. Ga vervolgens terug naar het werkblad en voer deze formule in: "=removefirstx(A4,2)" in een lege cel, en sleep de vulgreep naar beneden om de resultaten te krijgen zoals gewenst, zie screenshot:

Opmerking: In deze formule is "A4" de cel waarvan je tekens wilt verwijderen; Het getal "2" geeft het aantal tekens aan dat je vanaf het begin van de tekstreeks wilt verwijderen.

1.2 Laatste n tekens uit tekstreeksen verwijderen

Om een specifiek aantal tekens van de rechterkant van tekstreeksen te verwijderen, kun je ook een formule of een User Defined Function gebruiken.

Met behulp van een formule

Om de laatste n tekens uit tekstreeksen te verwijderen, kun je een formule gebruiken op basis van de functies LEFT en LEN.

LEFT- en LEN-functies om de laatste N tekens te verwijderen:

• "string": De tekstreeks waarvan je tekens wilt verwijderen;
• "num_chars": Het aantal tekens dat je wilt verwijderen.

Om3 tekens van het einde van de tekstreeksen te verwijderen, gebruik je deze formule en sleep je de vulgreep naar andere cellen, zie screenshot:

=LEFT(A4, LEN(A4) - 3)

Met behulp van een User Defined Function

Een User Defined Function kan je ook helpen om de laatste n tekens uit een lijst met cellen te verwijderen. Volg deze stappen:

1. Houd de toetsen "Alt + F11" ingedrukt om het venster "Microsoft Visual Basic for Applications" te openen.

2. Klik op "Invoegen" > "Module" en plak de volgende code in het modulevenster.

VBA-code: Laatste n tekens uit tekstreeksen verwijderen

Function removeLastx(rng As String, cnt As Long)
'Updateby Extendoffice
removeLastx = Left(rng, Len(rng) - cnt)
End Function

3. Ga vervolgens terug naar het werkblad en voer deze formule in: "=removelastx(A4,3)" in een lege cel, en sleep de vulgreep naar beneden om de resultaten te krijgen zoals gewenst, zie screenshot:

Opmerking: In deze formule is "A4" de cel waarvan je tekens wilt verwijderen; Het getal "3" geeft het aantal tekens aan dat je vanaf het einde van de tekstreeks wilt verwijderen.

1.3 Eerste, laatste n tekens of tekens op een bepaalde positie verwijderen met een krachtige functie

Het kan lastig zijn om allerlei formules te onthouden om tekens uit het begin, einde of een specifieke positie van tekstreeksen te verwijderen. "Kutools voor Excel" biedt een krachtige functie – "Verwijder tekens op een specifieke positie". Met deze handige tool kun je deze taken uitvoeren met slechts een paar klikken, zonder formules te hoeven onthouden.

1. Selecteer de cellen waarvan je tekens wilt verwijderen en klik vervolgens op "Kutools" > "Tekst" > "Verwijder tekens op een specifieke positie", zie screenshot:

2. Voer in het dialoogvenster "Verwijder tekens op een specifieke positie" de volgende handelingen uit:

2.1 Eerste n tekens uit cellen verwijderen:

• Typ in het tekstvak "Aantal" het aantal tekens dat je uit de tekstreeksen wilt verwijderen. In dit voorbeeld verwijder ik de eerste2 tekens.
• Selecteer de optie "Begin vanaf de linkerkant" in het gedeelte "Positie".
• Klik vervolgens op de knop "Ok" of "Toepassen" om het resultaat te krijgen zoals hieronder weergegeven.

2.2 Laatste n tekens uit cellen verwijderen:

• Typ in het tekstvak "Aantal" het aantal tekens dat je uit de tekstreeksen wilt verwijderen. In dit voorbeeld verwijder ik de laatste3 tekens.
• Selecteer de optie "Begin vanaf de rechterkant" in het gedeelte "Positie".
• Klik vervolgens op de knop "Ok" of "Toepassen" om het resultaat te krijgen zoals hieronder weergegeven.

2.3 N tekens uit een specifieke positie van cellen verwijderen:

Als je een specifiek aantal tekens vanaf een bepaalde positie in de tekstreeks wilt verwijderen, bijvoorbeeld3 tekens vanaf het derde teken van de reeks.

• Typ in het tekstvak "Aantal" het aantal tekens dat je wilt verwijderen. In dit voorbeeld verwijder ik3 tekens vanaf een bepaalde positie.
• Selecteer de optie "Specifiek" en typ het nummer vanaf welk teken je wilt beginnen met verwijderen in het tekstvak van het gedeelte "Positie". Hier verwijder ik tekens vanaf het derde teken.
• Klik vervolgens op de knop "Ok" of "Toepassen" om het resultaat te krijgen zoals hieronder weergegeven.

1.4 Zowel de eerste n als de laatste n tekens uit tekstreeksen verwijderen met een formule

Als je tekens aan beide kanten van tekstreeksen in Excel wilt verwijderen, kun je de functies MID en LEN combineren om een formule te maken voor deze taak.

• "string": De tekstreeks waarvan je tekens wilt verwijderen;
• "left_chars": Het aantal tekens dat je aan de linkerkant wilt verwijderen;
• "right_chars": Het aantal tekens dat je aan de rechterkant wilt verwijderen.

Stel dat je tegelijkertijd de eerste7 tekens en de laatste5 tekens uit tekstreeksen wilt verwijderen, voer dan de volgende formule in een lege cel in:

=MID(A4, 7+1, LEN(A4) - (7+5))

Opmerking: In deze formule is "A4" de cel waarvan je tekens wilt verwijderen; Het getal "7" is het aantal tekens dat je aan de linkerkant wilt verwijderen; Het getal "5" is het aantal tekens dat je aan de rechterkant wilt verwijderen.

Sleep vervolgens de vulgreep naar beneden om deze formule toe te passen waar je wilt, en je krijgt het resultaat zoals hieronder weergegeven.

Ongewenste / speciale tekens uit tekstreeksen verwijderen

Bij het importeren van gegevens van elders naar Excel kunnen er veel speciale of ongewenste tekens in je werkblad terechtkomen. Om deze ongewenste tekens, zoals #@$%^&, spaties, cijfers, niet-numerieke tekens, regeleindes, enzovoort te verwijderen, vind je in dit gedeelte enkele handige methoden.

2.1 Bepaalde speciale tekens uit tekstreeksen verwijderen

Als er speciale tekens zoals %^&*() in de tekstreeksen staan, kun je een van de onderstaande drie methoden gebruiken om deze tekens te verwijderen.

Meerdere speciale tekens uit tekstreeksen verwijderen met de SUBSTITUTE-functie

In Excel kun je normaal gesproken meerdere SUBSTITUTE-functies nesten om elk specifiek teken te vervangen door niets. De algemene syntaxis is:

• "string_cell": De cel met de tekstreeks waarvan je speciale tekens wilt verwijderen;
• "char1, char2, char3": De ongewenste tekens die je wilt verwijderen.

Kopieer of voer nu de onderstaande formule in een lege cel in:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "#", ""), "&", ""), "*", ""), "%", "")

Sleep vervolgens de vulgreep naar de cellen waar je deze formule wilt toepassen. Alle opgegeven ongewenste tekens worden in één keer verwijderd, zie screenshot:

Tips: Als je meer tekens wilt verwijderen, hoef je alleen maar meer SUBSTITUTE-functies in de formule te nesten.

Meerdere speciale tekens uit tekstreeksen verwijderen met een User Defined Function

De bovenstaande geneste SUBSTITUTE-functies werken goed als er maar een paar speciale tekens verwijderd moeten worden. Maar als je tientallen tekens wilt verwijderen, wordt de formule te lang en lastig te beheren. In dat geval kan de volgende User Defined Function je snel en eenvoudig helpen.

1. Houd de toetsen "Alt + F11" ingedrukt om het venster "Microsoft Visual Basic for Applications" te openen.

2. Klik op "Invoegen" > "Module" en plak de volgende code in het modulevenster.

VBA-code: Meerdere speciale tekens uit tekstreeksen verwijderen

Function RemoveUnwantedChars(Str As String, xchars As String)
'Updateby Extendoffice
    For Index = 1 To Len(xchars)
        Str = Replace(Str, Mid(xchars, Index, 1), "")
    Next
    RemoveUnwantedChars = Str
End Function

3. Sluit het codevenster en ga terug naar het werkblad. Voer deze formule "=RemoveUnwantedChars(A2, $D$2)" in een lege cel in waar je het resultaat wilt tonen, en sleep de vulgreep naar beneden om de resultaten te krijgen zoals gewenst, zie screenshot:

Opmerking: In de bovenstaande formule is "A2" de cel waarvan je tekens wilt verwijderen; "$D$2" bevat de speciale tekens die je wilt verwijderen (je kunt hier andere gewenste tekens typen).

Meerdere speciale tekens uit tekstreeksen verwijderen met een handige functie

Als je "Kutools voor Excel" hebt geïnstalleerd, kun je met de functie "Specifieke tekens verwijderen" alle soorten tekens verwijderen, zoals cijfers, alfabetische tekens, niet-afdrukbare tekens, enzovoort, uit een lijst met cellen.

1. Selecteer het bereik van cellen waarvan je speciale tekens wilt verwijderen en klik vervolgens op "Kutools" > "Tekst" > "Specifieke tekens verwijderen", zie screenshot:

2. In het dialoogvenster "Specifieke tekens verwijderen":

• Vink de optie "Aangepast" aan onder het gedeelte "Specifieke tekens verwijderen".
• Voer vervolgens de speciale tekens in het tekstvak in die je wilt verwijderen.
• Klik vervolgens op de knop "Ok" of "Toepassen" om de opgegeven tekens direct te verwijderen. Zie screenshot:

2.2 Alle cijfers uit tekstreeksen verwijderen

Als je een lijst met tekstreeksen hebt die een mix van cijfers, letters en speciale tekens bevatten, en je wilt alleen de cijfers verwijderen en de andere tekens behouden, vind je in dit gedeelte enkele handige methoden.

Cijfers uit tekstreeksen verwijderen met de SUBSTITUTE-functie

In Excel kun je met geneste SUBSTITUTE-functies alle cijfers vervangen door niets. Gebruik de onderstaande formule om alle cijfers uit cellen te verwijderen:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""),0,"")

Sleep vervolgens de vulgreep naar de cellen waar je deze formule wilt toepassen. Alle cijfers worden uit de lijst met tekstreeksen verwijderd, zie screenshot:

Cijfers uit tekstreeksen verwijderen met de TEXTJOIN-functie

Als je Excel2019,2021 of365 hebt, kan de nieuwe TEXTJOIN-functie ook helpen om cijfers uit tekstreeksen te verwijderen.

Kopieer de volgende formule in een lege cel en druk vervolgens tegelijkertijd op Ctrl + Shift + Enter om het eerste resultaat te krijgen:

=TEXTJOIN("", TRUE, IF(ISERR(MID(A2, ROW(INDIRECT( "1:"&LEN(A2) )), 1) *1), MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1), ""))

Kopieer de formule vervolgens naar de andere cellen waar je deze wilt toepassen, zie screenshot:

Opmerking: Deze TEXTJOIN is alleen beschikbaar in Excel2019,2021 en Office365.

Cijfers uit tekstreeksen verwijderen met een User Defined Function

Naast bovenstaande twee formules kun je ook een User Defined Function gebruiken. Volg hiervoor de onderstaande stappen:

1. Houd de toetsen "Alt + F11" ingedrukt om het venster "Microsoft Visual Basic for Applications" te openen.

2. Klik op "Invoegen" > "Module" en plak de volgende code in het modulevenster.

VBA-code: Cijfers uit tekstreeksen verwijderen

Function RemoveNumbers(Txt As String) As String
'Updateby Extendoffice
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "[0-9]"
RemoveNumbers = .Replace(Txt, "")
End With
End Function

3. Sluit het codevenster, ga terug naar het werkblad en voer deze formule in:=RemoveNumbers(A2)in een lege cel. Sleep vervolgens de vulgreep naar de cellen waar je deze formule wilt toepassen, zie screenshot:

Cijfers uit tekstreeksen verwijderen met een handige optie

Als je de ingewikkelde formules zat bent, kun je met de handige tool "Kutools voor Excel" en de functie "Specifieke tekens verwijderen" deze taak met slechts enkele klikken uitvoeren.

1. Selecteer het bereik van cellen waarvan je cijfers wilt verwijderen en klik vervolgens op "Kutools" > "Tekst" > "Specifieke tekens verwijderen".

2. Stel in het dialoogvenster "Specifieke tekens verwijderen" de volgende opties in:

• Vink de optie "Numerieke tekens" aan onder het gedeelte "Specifieke tekens verwijderen".
• Klik vervolgens op de knop "Ok" of "Toepassen" om de cijfers direct te verwijderen. Zie screenshot:

2.3 Niet-numerieke tekens uit tekstreeksen verwijderen

Om alle niet-numerieke tekens te verwijderen en alleen de cijfers uit tekstreeksen te behouden, vind je in dit gedeelte enkele methoden om deze taak in Excel op te lossen.

Niet-numerieke tekens uit tekstreeksen verwijderen met een formule in Excel2016 en eerdere versies

Als je Excel2016 of eerdere versies gebruikt, moet je een complexe formule toepassen om deze taak uit te voeren. Kopieer of voer de onderstaande formule in een lege cel in:

=SUMPRODUCT(MID(0&A2, LARGE(INDEX(ISNUMBER(--MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1)) * ROW(INDIRECT("1:"&LEN(A2))), 0), ROW(INDIRECT("1:"&LEN(A2))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(A2)))/10)

Kopieer de formule vervolgens naar de andere cellen waar je deze wilt toepassen, zie screenshot:

Opmerking: Als de cijfers in een tekstreeks beginnen met0, zal de0 verdwijnen.

Niet-numerieke tekens uit tekstreeksen verwijderen met de TEXTJOIN-functie in Excel2019,2021,365

De bovenstaande formule kan voor de meesten van ons te ingewikkeld zijn. Als je Excel2019,2021 of365 hebt, is er een eenvoudige formule die je kan helpen.

Kopieer of voer de volgende formule in een lege cel in en druk tegelijkertijd op "Ctrl + Shift + Enter" om het eerste correcte resultaat te krijgen:

=TEXTJOIN("",TRUE,IFERROR(MID(A2,ROW(INDIRECT("1:100")),1)+0,""))

Sleep vervolgens de vulgreep naar de cellen waar je deze formule wilt toepassen. Je krijgt het resultaat zoals hieronder weergegeven.

Opmerking: Met deze formule blijven de voorloopnullen behouden, omdat de cijfers als tekst worden weergegeven.

Niet-numerieke tekens uit tekstreeksen verwijderen met een User Defined Function

Natuurlijk kun je ook je eigen User Defined Function maken met een eenvoudigere syntaxis. Doe dit als volgt:

1. Houd de toetsen "Alt + F11" ingedrukt om het venster "Microsoft Visual Basic for Applications" te openen.

2. Klik op "Invoegen" > "Module" en plak de volgende code in het modulevenster.

VBA-code: Niet-numerieke tekens uit tekstreeksen verwijderen

Function Removenonnumeric(str As String) As String
'Updateby Extendoffice
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "[^0-9]"
        Removenonnumeric = .Replace(str, "")
    End With
End Function

3. Sluit het codevenster, ga terug naar het werkblad en voer deze formule in: =Removenonnumeric(A2)in een lege cel. Sleep vervolgens de vulgreep naar de cellen waar je deze formule wilt toepassen. Alleen de cijfers worden geëxtraheerd, zoals hieronder weergegeven.

Niet-numerieke tekens uit tekstreeksen verwijderen met een eenvoudige functie

Om niet-numerieke tekens direct uit een bereik van cellen te verwijderen, kun je de functie "Specifieke tekens verwijderen" van "Kutools voor Excel" gebruiken. Hiermee is het in slechts enkele klikken geregeld.

1. Selecteer het bereik van cellen waarvan je niet-numerieke tekens wilt verwijderen en klik vervolgens op "Kutools" > "Tekst" > "Specifieke tekens verwijderen".

2. Stel in het dialoogvenster "Specifieke tekens verwijderen" de volgende opties in:

• Vink de optie "Niet-numerieke tekens" aan onder het gedeelte "Specifieke tekens verwijderen".
• Klik vervolgens op de knop "Ok" of "Toepassen" om alle niet-numerieke tekens direct te verwijderen. Zie screenshot:

2.4 Tekst en cijfers uit één cel splitsen in twee kolommen

Soms wil je tekst en cijfers uit tekstreeksen in twee aparte kolommen plaatsen. Met de volgende methoden kun je deze taak snel en eenvoudig uitvoeren.

Tekst en cijfers uit één cel splitsen in twee kolommen met een User Defined Function

Met de volgende User Defined Function kun je tekst en cijfers in één keer extraheren. Volg deze stappen:

1. Houd de toetsen "Alt + F11" ingedrukt om het venster "Microsoft Visual Basic for Applications" te openen.

2. Klik op "Invoegen" > "Module" en plak de volgende code in het modulevenster.

VBA-code: Tekst en cijfers uit tekstreeksen splitsen in twee kolommen

Function SplitText(pWorkRng As Range, pIsNumber As Boolean) As String
'Updateby Extendoffice
Dim xLen As Long
Dim xStr As String
xLen = VBA.Len(pWorkRng.Value)
For i = 1 To xLen
    xStr = VBA.Mid(pWorkRng.Value, i, 1)
    If ((VBA.IsNumeric(xStr) And pIsNumber) Or (Not (VBA.IsNumeric(xStr)) And Not (pIsNumber))) Then
        SplitText = SplitText + xStr
    End If
Next
End Function

3. Sluit het codevenster, ga terug naar het werkblad en voer deze formule in: =SplitText(A2,FALSE)in een lege cel. Sleep vervolgens de vulgreep naar de cellen waar je deze formule wilt toepassen om alle tekst te krijgen, zie screenshot:

4. Voer vervolgens deze formule in: =SplitText(A2,TRUE)in een andere cel en sleep de vulgreep naar de cellen waar je deze formule wilt toepassen om de cijfers te krijgen, zie screenshot:

Tekst en cijfers uit één cel splitsen in twee kolommen met een eenvoudige functie

Als je "Kutools voor Excel" hebt, kan de functie "Cellen splitsen" je helpen om cellen op te splitsen in meerdere kolommen of rijen op basis van scheidingstekens, opgegeven breedte of tekst en cijfers.

1. Selecteer het bereik van cellen dat je wilt splitsen en klik vervolgens op "Kutools" > "Samenvoegen & Opsplitsen" > "Cellen splitsen", zie screenshot:

2. Selecteer in het dialoogvenster "Cellen splitsen" de optie "Verdeel Gegevens" onder het gedeelte "Type" en vink "Splitsen op tekst en nummer" aan onder het gedeelte "Scheidingsteken", zie screenshot:

3. Klik vervolgens op de knop "Ok". Er verschijnt een ander dialoogvenster "Cellen splitsen". Selecteer een cel om de gescheiden tekst en cijfers weer te geven en klik op de knop "OK". Nu zie je dat de tekst en cijfers in de geselecteerde cellen direct in twee kolommen zijn gesplitst, zoals hieronder getoond:

2.5 Regeleindetekens uit tekstreeksen verwijderen

Een regeleinde zorgt ervoor dat je meerdere regels in dezelfde cel in Excel kunt hebben. Soms, wanneer je gegevens van een website kopieert of handmatig de inhoud van een cel met "Alt + Enter" opdeelt, krijg je regeleindes of carriage returns. In bepaalde gevallen wil je deze regeleindes verwijderen zodat de celinhoud op één regel komt te staan, zoals in de onderstaande screenshot. Hier vind je enkele methoden om deze taak in Excel op te lossen.

Regeleindetekens uit tekstreeksen verwijderen met de functie Zoeken en Vervangen

In Excel kun je de functie "Zoeken en Vervangen" gebruiken om regeleindes te verwijderen. Doe dit als volgt:

1. Selecteer het gegevensbereik waarvan je de regeleindes wilt verwijderen.

2. Klik vervolgens op "Start" > "Zoeken en Selecteren" > "Vervangen" (of druk op "Ctrl + H") om het dialoogvenster "Zoeken en Vervangen" te openen, zie screenshot:

3. Voer in het pop-upvenster "Zoeken en Vervangen" de volgende handelingen uit:

• Plaats de cursor in het veld "Zoeken naar" en druk op "Ctrl + J" op het toetsenbord. Je ziet misschien niets, maar het regeleindeteken is ingevoerd.
• Laat het veld "Vervangen door" leeg om de regeleindes te verwijderen, of druk eenmaal op de spatiebalk om de regeleindes te vervangen door spaties.

4. Klik vervolgens op de knop "Alles vervangen". Alle regeleindes in de geselecteerde cellen worden direct verwijderd of vervangen door spaties. Zie screenshot:

Regeleindetekens uit tekstreeksen verwijderen met de SUBSTITUTE-functie

Je kunt ook een formule maken op basis van de functies SUBSTITUTE en CHAR om de regeleindes uit tekstreeksen te verwijderen.

Pas de onderstaande formule toe om het resultaat te krijgen:

=SUBSTITUTE(A2,CHAR(10),"")

Tips: De SUBSTITUTE-functie zoekt en vervangt het teken CHAR(10), dat het regeleindeteken voorstelt, door niets. Wil je het resultaat gescheiden door een komma en spatie, gebruik dan onderstaande formule:

=SUBSTITUTE(A2,CHAR(10),", ")

Regeleindetekens uit tekstreeksen verwijderen met VBA-code

Als je vertrouwd bent met VBA-code, kun je ook onderstaande code gebruiken. Volg deze stappen:

1. Houd de toetsen "Alt + F11" ingedrukt om het venster "Microsoft Visual Basic for Applications" te openen.

2. Klik op "Invoegen" > "Module" en plak de volgende code in het modulevenster.

VBA-code: Regeleindes uit tekstreeksen verwijderen

Sub RemoveCarriage()
'Updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    Rng.Value = Replace(Rng.Value, Chr(10), "")
Next
End Sub

3. Druk op de toets "F5" om deze code uit te voeren. Er verschijnt een prompt. Selecteer het bereik waarvan je de regeleindes wilt verwijderen, zoals hieronder weergegeven:

4. Klik vervolgens op de knop "OK". Alle regeleindes worden uit het geselecteerde gegevensbereik verwijderd.

Regeleindetekens uit tekstreeksen verwijderen met een slimme optie

Met de functie "Specifieke tekens verwijderen" van "Kutools voor Excel" kun je ook eenvoudig regeleindes verwijderen.

1. Selecteer het bereik van cellen waarvan je regeleindes wilt verwijderen en klik vervolgens op "Kutools" > "Tekst" > "Specifieke tekens verwijderen".

2. Stel in het dialoogvenster "Specifieke tekens verwijderen" de volgende opties in:

• Vink de optie "Niet-afdrukbare tekens" aan onder het gedeelte "Specifieke tekens verwijderen".
• Klik vervolgens op de knop "Ok" of "Toepassen" om alle regeleindes uit het geselecteerde gegevensbereik te verwijderen. Zie screenshot:

2.6 Spaties (voorafgaand, volgend, extra of alle spaties) uit tekstreeksen verwijderen

Tekst kopiëren en plakken uit een externe bron naar een Excel-werkblad zorgt vaak voor vervelende overtollige spaties. Het handmatig verwijderen van voorafgaande, volgende of andere extra spaties is tijdrovend. Gelukkig biedt Excel enkele eenvoudige methoden om deze taak uit te voeren.

Overtollige spaties (voorafgaand, volgend, extra) uit tekstreeksen verwijderen met de TRIM-functie

In Excel kun je met de eenvoudige TRIM-functie voorafgaande, volgende en extra spaties uit tekstreeksen verwijderen. Deze functie verwijdert alle spaties, behalve enkele spaties tussen woorden.

Voer de onderstaande formule in een lege cel in:

=TRIM(A2)

Sleep vervolgens de vulgreep naar beneden om de formule naar andere cellen te kopiëren. Nu zie je dat alle voorafgaande, volgende en extra spaties tussen de woorden in één keer zijn verwijderd, zie screenshot:

Alle spaties uit tekstreeksen verwijderen

Als je alle spaties uit tekstreeksen wilt verwijderen, kun je de volgende SUBSTITUTE-functie en de functie Zoeken & Vervangen gebruiken.

Met behulp van de SUBSTITUTE-functie

Je kunt de SUBSTITUTE-functie gebruiken om alle spaties te vervangen door niets. Pas de onderstaande formule toe in een lege cel:

=SUBSTITUTE(A2," ","") 

Sleep vervolgens de vulgreep naar beneden om deze formule naar andere cellen te kopiëren. Alle spaties worden verwijderd, zoals hieronder weergegeven:

Met behulp van de functie Zoeken en Vervangen

De functie "Zoeken & Vervangen" in Excel kan ook helpen om alle spaties uit geselecteerde cellen te verwijderen. Volg hiervoor de onderstaande stappen:

1. Selecteer het gegevensbereik waarvan je alle spaties wilt verwijderen.

2. Klik vervolgens op "Start" > "Zoeken en Selecteren" > "Vervangen" (of druk op "Ctrl + H") om het dialoogvenster "Zoeken en Vervangen" te openen. Voer in het geopende dialoogvenster de onderstaande handelingen uit:

• Druk op de spatiebalk in het veld "Zoeken naar";
• Laat het veld "Vervangen door" leeg.

3. Klik vervolgens op de knop "Alles vervangen". Alle spaties in de geselecteerde cellen worden direct verwijderd. Zie screenshot:

Alle soorten spaties uit tekstreeksen verwijderen met een krachtige functie

"Kutools voor Excel" heeft een krachtige functie – "Verwijder spaties". Met deze functie kun je niet alleen voorafgaande, volgende en overtollige spaties verwijderen, maar ook alle spaties uit geselecteerde bereiken in één dialoogvenster. Dit verhoogt je productiviteit.

1. Selecteer het gegevensbereik waarvan je spaties wilt verwijderen en klik vervolgens op "Kutools" > "Tekst" > "Verwijder spaties". Zie screenshot:

2. Selecteer in het dialoogvenster "Verwijder spaties" het type spatie dat je wilt verwijderen bij "Type ruimte":

• Om voorafgaande spaties te verwijderen, selecteer je de optie "Voorafgaande spaties";
• Om volgende spaties te verwijderen, selecteer je de optie "Volgende spaties";
• Om zowel voorafgaande als volgende spaties in één keer te verwijderen, selecteer je de optie "Voorafgaande & Volgende spaties";
• Om alle overtollige spaties te verwijderen, selecteer je de optie "Alle overtollige spaties";
• Om alle spaties te verwijderen, selecteer je de optie "Alle spaties".

3. Klik vervolgens op de knop "Ok" of "Toepassen" om het gewenste resultaat te krijgen.

Tekens / tekst vóór of na een specifiek teken verwijderen

In dit gedeelte worden enkele bewerkingen geïntroduceerd voor het verwijderen van tekst of tekens vóór of na de eerste, laatste of N-de keer dat een specifiek teken voorkomt.

3.1 Tekst vóór of na het eerste specifieke teken verwijderen

Als je tekst vóór of na het eerste specifieke teken wilt verwijderen, zoals een spatie of komma uit een lijst met tekstreeksen zoals hieronder weergegeven, vind je hier twee methoden.

Tekst vóór het eerste specifieke teken verwijderen met een formule

Om tekst of tekens vóór het eerste specifieke teken te verwijderen, kun je een formule maken op basis van de functies RIGHT, LEN en FIND. De algemene syntaxis is:

• "cell": De celverwijzing of tekstreeks waarvan je tekst wilt verwijderen;
• "char": Het specifieke scheidingsteken waarop je de tekst wilt verwijderen.

Bijvoorbeeld: om alles vóór de eerste komma uit de lijst te verwijderen, gebruik je de onderstaande formule in een lege cel en sleep je deze naar de gewenste cellen, zie screenshot:

=RIGHT(A2,LEN(A2)-FIND(",",A2))

Opmerking: In de bovenstaande formule is "A2" de cel waarvan je tekst wilt verwijderen; "," is het specifieke teken waarop je wilt verwijderen. Je kunt dit aanpassen naar andere tekens indien gewenst.

Tekst na het eerste specifieke teken verwijderen met een formule

Om alles na het eerste specifieke teken te verwijderen, kun je de functies LEFT en FIND gebruiken. De algemene syntaxis is:

• "cell": De celverwijzing of tekstreeks waarvan je tekst wilt verwijderen;
• "char": Het specifieke scheidingsteken waarop je de tekst wilt verwijderen.

Voer nu de onderstaande formule in een lege cel in en sleep de vulgreep naar de andere cellen waar je deze formule wilt toepassen. Alle tekens na de eerste komma worden direct verwijderd, zie screenshot:

=LEFT(A2,FIND(",",A2)-1)

3.2 Tekst vóór of na de N-de keer dat een teken voorkomt verwijderen

Soms bevatten tekstreeksen meerdere exemplaren van een specifiek scheidingsteken. Je wilt misschien alle tekens vóór of na een bepaalde keer verwijderen, zoals de tweede, derde of vierde keer. Gebruik hiervoor de volgende methoden:

Tekst vóór de N-de keer dat een teken voorkomt verwijderen met een formule

Om tekst vóór de N-de keer dat een specifiek teken voorkomt te verwijderen, kun je de volgende formule gebruiken. De algemene syntaxis is:

• "cell": De celverwijzing of tekstreeks waarvan je tekst wilt verwijderen;
• "char": Het specifieke scheidingsteken waarop je de tekst wilt verwijderen;
• "N": Het voorkomen van het teken vóór welke je tekst wilt verwijderen.

Bijvoorbeeld: om alles vóór de tweede komma uit de tekstreeksen te verwijderen, gebruik je de onderstaande formule:

=RIGHT(A2,LEN(A2)-FIND("#",SUBSTITUTE(A2,",","#",2)))

Opmerking: In de bovenstaande formule is "A2" de cel waarvan je tekst wilt verwijderen; "," is het specifieke teken waarop je wilt verwijderen (dit kun je aanpassen); "2" geeft de N-de komma aan vóór welke je tekst wilt verwijderen.

Sleep vervolgens de vulgreep om de formule naar andere cellen te kopiëren, zie screenshot:

Tekst na de N-de keer dat een teken voorkomt verwijderen met een formule

Om tekst na de N-de keer dat een specifiek scheidingsteken voorkomt te verwijderen, kun je de functies LEFT, SUBSTITUTE en FIND gebruiken. De algemene syntaxis is:

• "cell": De celverwijzing of tekstreeks waarvan je tekst wilt verwijderen;
• "char": Het specifieke scheidingsteken waarop je de tekst wilt verwijderen;
• "N": Het voorkomen van het teken waarna je tekst wilt verwijderen.

Als je de basis syntaxis begrijpt, kopieer of voer dan de onderstaande formule in een lege cel in:

=LEFT(A2, FIND("#", SUBSTITUTE(A2, ",", "#", 2)) -1)

Opmerking: In de bovenstaande formule is "A2" de cel waarvan je tekst wilt verwijderen; "," is het specifieke teken waarop je wilt verwijderen (dit kun je aanpassen); "2" geeft de N-de komma aan waarna je tekst wilt verwijderen.

Sleep vervolgens de vulgreep om de formule naar andere cellen te kopiëren. Alle tekens na de tweede komma worden direct verwijderd, zie screenshot:

Tekst vóór of na de N-de keer dat een teken voorkomt verwijderen met een User Defined Function

Zoals je ziet kun je de gevallen voor het verwijderen van tekst vóór of na de N-de keer dat een teken voorkomt oplossen met verschillende combinaties van Excel-functies. Het nadeel is dat je deze lastige formules moet onthouden. In dit geval maak ik een User Defined Function die alle scenario's dekt. Doe dit als volgt:

1. Houd de toetsen "Alt + F11" ingedrukt om het venster "Microsoft Visual Basic for Applications" te openen.

2. Klik op "Invoegen" > "Module" en plak de volgende code in het modulevenster.

VBA-code: Tekst vóór of na de N-de keer dat een teken voorkomt verwijderen

Function RemoveTextOccurrence(Str As String, Delimiter As String, Occurrence As Integer, IsAfter As Boolean)
Dim xStr As String
Dim xStrLen, xF, xIntStart As Integer
xStr = Str
xStrLen = Len(xStr)
xIntStart = 1
For xF = 1 To Occurrence
xIntStart = InStr(xIntStart + 1, xStr, Delimiter, vbTextCompare)
If (xIntStart = 0) Or (xIntStart < 0) Then
    If IsAfter Then
    RemoveTextOccurrence = xStr
    Else
    RemoveTextOccurrence = ""
    End If
    Exit Function
End If
Next
If IsAfter Then
    RemoveTextOccurrence = Mid(Str, 1, xIntStart - 1)
Else
    RemoveTextOccurrence = Mid(Str, xIntStart + 1)
End If
End Function

3. Sluit het codevenster, ga terug naar het werkblad en gebruik de volgende formules:

Tekst vóór de tweede keer dat een komma voorkomt verwijderen:

=RemoveTextOccurrence(A2, ", ", 2, FALSE)

Tekst na de tweede keer dat een komma voorkomt verwijderen

=RemoveTextOccurrence(A2, ", ", 2, TRUE)

3.3 Tekst vóór of na de laatste keer dat een teken voorkomt verwijderen

Als je alle tekst vóór of na het laatste specifieke teken wilt verwijderen en alleen de delen vóór of na dat teken wilt behouden, vind je in dit gedeelte formules om dit probleem op te lossen.

Tekst vóór de laatste keer dat een teken voorkomt verwijderen met een formule

Om alle tekens vóór de laatste keer dat een teken voorkomt te verwijderen, is de algemene syntaxis:

• "cell": De celverwijzing of tekstreeks waarvan je tekst wilt verwijderen;
• "char": Het specifieke scheidingsteken waarop je de tekst wilt verwijderen;

Als je bijvoorbeeld de tekst vóór de laatste komma wilt verwijderen, kopieer of voer dan de onderstaande formule in een lege cel in:

=RIGHT(A2,LEN(A2)-SEARCH("#",SUBSTITUTE(A2,",","#",LEN(A2)-LEN(SUBSTITUTE(A2,",","")))))

Opmerking: In de bovenstaande formule is "A2" de cel waarvan je tekst wilt verwijderen; "," is het specifieke teken waarop je wilt verwijderen (dit kun je aanpassen).

Sleep vervolgens de vulgreep om de formule naar andere cellen te kopiëren. Alle tekens vóór de laatste komma worden verwijderd, zoals hieronder weergegeven:

Tekst na de laatste keer dat een teken voorkomt verwijderen met formules

Als de celwaarden worden gescheiden door een variabel aantal scheidingstekens en je alles na het laatste scheidingsteken wilt verwijderen, is de algemene syntaxis:

• "cell": De celverwijzing of tekstreeks waarvan je tekst wilt verwijderen;
• "char": Het specifieke scheidingsteken waarop je de tekst wilt verwijderen;

Kopieer of voer de onderstaande formule in een lege cel in en sleep de vulgreep naar beneden om de andere resultaten te krijgen, zie screenshot:

=LEFT(A2,FIND("#",SUBSTITUTE(A2,",>","#",LEN(A2)-LEN(SUBSTITUTE(A2,",",""))))-1)

Opmerking: In de bovenstaande formule is "A2" de cel waarvan je tekst wilt verwijderen; "," is het specifieke teken waarop je wilt verwijderen (dit kun je aanpassen).

3.4 Tekst tussen haakjes verwijderen

Als je een lijst met tekstreeksen hebt waarvan een deel van de tekens tussen haakjes staat, wil je misschien alle tekens binnen de haakjes, inclusief de haakjes zelf, verwijderen zoals hieronder weergegeven. In dit gedeelte vind je enkele methoden om deze taak in Excel op te lossen.

Tekst tussen haakjes verwijderen met de functie Zoeken en Vervangen

In Excel kun je met de ingebouwde functie Zoeken en Vervangen alle tekst binnen haakjes vinden en vervangen door niets. Doe dit als volgt:

1. Selecteer de gegevenslijst waarvan je de tekst tussen haakjes wilt verwijderen.

2. Klik vervolgens op "Start" > "Zoeken en Selecteren" > "Vervangen" (of druk op "Ctrl + H") om het dialoogvenster Zoeken en Vervangen te openen. Voer in het dialoogvenster de volgende handelingen uit:

• Typ "(*)" in het veld "Zoeken naar";
• Laat het veld "Vervangen door" leeg.

3. Klik vervolgens op de knop "Alles vervangen". Alle tekens binnen de haakjes (inclusief de haakjes) in de geselecteerde cellen worden direct verwijderd. Zie screenshot:

Tips: De functie "Zoeken en Vervangen" werkt ook voor twee of meer haakjesparen binnen tekstreeksen.

Tekst tussen haakjes verwijderen met een formule

Naast de functie Zoeken en Vervangen kun je ook een formule gebruiken om deze taak in Excel op te lossen. De algemene syntaxis is:

• "text": De tekstreeks of celverwijzing waarvan je tekens wilt verwijderen.

Kopieer of voer nu de volgende formule in een lege cel in waar je het resultaat wilt krijgen:

=SUBSTITUTE(A2,MID(LEFT(A2,FIND(")",A2)),FIND("(",A2),LEN(A2)),"")

Sleep vervolgens de vulgreep naar de cellen waar je deze formule wilt toepassen. Alle tekst binnen de haakjes, inclusief de haakjes zelf, wordt direct verwijderd, zie screenshot:

Tips: Als er geen haakjes in de celwaarde staan, wordt er een fout weergegeven na het toepassen van bovenstaande formule. Om de fout te negeren, gebruik je onderstaande formule:

=IFERROR(SUBSTITUTE(A2,MID(LEFT(A2,FIND(")",A2)),FIND("(",A2),LEN(A2)),""),A2)

Tekst tussen haakjes verwijderen met een User Defined Function

De bovenstaande formule werkt goed voor het verwijderen van tekst uit één haakjespaar. Maar als je tekst uit meerdere haakjesparen binnen tekstreeksen wilt verwijderen, werkt de formule niet correct. Hier maak ik een eenvoudige User Defined Function om deze taak op te lossen.

1. Houd de toetsen "Alt + F11" ingedrukt om het venster "Microsoft Visual Basic for Applications" te openen.

2. Klik op "Invoegen" > "Module" en plak de volgende code in het modulevenster.

VBA-code: Tekst tussen haakjes verwijderen

Function remtxt(ByVal str As String) As String
'Updateby Extendoffice
  While InStr(str, "(") > 0 And InStr(str, ")") > InStr(str, "(")
    str = Left(str, InStr(str, "(") - 1) & Mid(str, InStr(str, ")") + 1)
  Wend
  remtxt = Trim(str)
End Function

3. Ga terug naar het werkblad, voer deze formule in een lege cel in: "=remtxt(A2)", en sleep de vulgreep naar beneden om de formule toe te passen. Alle tekst binnen de haakjes, inclusief de haakjes zelf, wordt verwijderd, zoals hieronder weergegeven:

Woorden uit tekstreeksen verwijderen

In bepaalde gevallen wil je misschien enkele woorden uit een lijst met cellen verwijderen, zoals het eerste of laatste woord, of dubbele woorden uit een cel. Voor het oplossen van dit soort verwijderingen vind je in dit gedeelte enkele methoden.

4.1 Eerste of laatste woord uit tekstreeks verwijderen

Om het eerste of laatste woord uit een lijst met tekstreeksen te verwijderen, kunnen de volgende formules je helpen.

Eerste woord uit tekstreeks verwijderen met een formule

Om het eerste woord uit een lijst met tekstreeksen te verwijderen, kun je een eenvoudige formule maken op basis van de functies RIGHT, LEN en FIND. De algemene syntaxis is:

• "text": De tekstreeks of celverwijzing waarvan je het eerste woord wilt verwijderen.

Voer nu de volgende formule in een lege cel in:

=RIGHT(A2,LEN(A2)-FIND(" ",A2))

Sleep vervolgens de vulgreep naar beneden om de formule naar andere cellen te kopiëren, zie screenshot:

Tips: Als je de eerste N woorden uit de cellen wilt verwijderen, gebruik dan onderstaande formule:

• "text": De tekstreeks of celverwijzing waarvan je de eerste n woorden wilt verwijderen;
• "N": Geeft aan hoeveel woorden je vanaf het begin van de tekstreeks wilt verwijderen.

Om bijvoorbeeld de eerste twee woorden uit cellen te verwijderen, kopieer of voer je de onderstaande formule in een lege cel in om het gewenste resultaat te krijgen, zie screenshot:

=MID(TRIM(A2),1+FIND("~",SUBSTITUTE(TRIM(A2)," ","~",2)),255)

Laatste woord uit tekstreeks verwijderen met een formule

Om het laatste woord uit tekstreeksen te verwijderen, kun je ook een formule gebruiken. De algemene syntaxis is:

• "text": De tekstreeks of celverwijzing waarvan je het laatste woord wilt verwijderen;

Gebruik de onderstaande formule in een lege cel en sleep de vulgreep naar beneden om de formule naar andere cellen te kopiëren, zie screenshot:

=LEFT(TRIM(A2),FIND("~",SUBSTITUTE(A2," ","~",LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))))-1)

Tips: Om de laatste N woorden uit een lijst met cellen te verwijderen, is de algemene syntaxis:

• "text": De tekstreeks of celverwijzing waarvan je de laatste n woorden wilt verwijderen;
• "N": Geeft het aantal woorden aan dat je vanaf het einde van de tekstreeks wilt verwijderen.

Stel dat je de laatste3 woorden uit een lijst met cellen wilt verwijderen, gebruik dan de onderstaande formule om het resultaat te krijgen, zie screenshot:

=LEFT(A2,FIND("~",SUBSTITUTE(A2," ","~",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-(3-1))))

4.2 Dubbele tekens of woorden binnen een cel verwijderen

Bij het verwijderen van dubbele waarden of rijen biedt Excel verschillende opties. Maar als je dubbele tekens of woorden binnen een bepaalde cel wilt verwijderen, is er mogelijk geen goede ingebouwde functie. In dat geval helpt dit gedeelte je om enkele User Defined Functions te maken om dit probleem op te lossen.

Dubbele tekens binnen een cel verwijderen met een User Defined Function

Als je meerdere keren hetzelfde teken in een cel hebt, kun je met de volgende User Defined Function de dubbele tekens verwijderen en alleen de eerste voorkomen behouden, zoals hieronder weergegeven.

1. Houd de toetsen "Alt + F11" ingedrukt om het venster "Microsoft Visual Basic for Applications" te openen.

2. Klik op "Invoegen" > "Module" en plak de volgende code in het modulevenster.

VBA-code: Dubbele tekens binnen een cel verwijderen

Function RemoveDupeschars(pWorkRng As Range) As String
'Updateby Extendoffice
Dim xValue As String
Dim xChar As String
Dim xOutValue As String
Set xDic = CreateObject("Scripting.Dictionary")
xValue = pWorkRng.Value
For i = 1 To VBA.Len(xValue)
    xChar = VBA.Mid(xValue, i, 1)
    If xDic.Exists(xChar) Then
    Else
        xDic(xChar) = ""
        xOutValue = xOutValue & xChar
    End If
Next
RemoveDupeschars = xOutValue
End Function

3. Sluit het codevenster, ga terug naar het werkblad en voer deze formule in: =RemoveDupeschars(A2)in een lege cel naast je gegevens. Sleep vervolgens de vulgreep naar de cellen waar je deze formule wilt toepassen, zie screenshot:

Opmerking:"A2" is de gegevenscel waarvan je dubbele tekens wilt verwijderen.

Tip: De functie is hoofdlettergevoelig, dus kleine en hoofdletters worden als verschillende tekens beschouwd.

Dubbele woorden binnen een cel verwijderen met een User Defined Function

Stel dat je dezelfde woorden of tekstreeksen in een cel hebt en alle dubbele woorden uit de cel wilt verwijderen, zoals hieronder weergegeven. Je kunt de volgende User Defined Function gebruiken om deze taak in Excel op te lossen.

1. Houd de toetsen "Alt + F11" ingedrukt om het venster "Microsoft Visual Basic for Applications" te openen.

2. Klik op "Invoegen" > "Module" en plak de volgende code in het modulevenster.

VBA-code: Dubbele woorden binnen een cel verwijderen

Function RemoveDupeswords(txt As String, Optional delim As String = " ") As String
'Updateby Extendoffice
    Dim x
    With CreateObject("Scripting.Dictionary")
        .CompareMode = vbTextCompare
        For Each x In Split(txt, delim)
            If Trim(x) <> "" And Not .exists(Trim(x)) Then .Add Trim(x), Nothing
        Next
        If .Count > 0 Then RemoveDupeswords = Join(.keys, delim)
    End With
End Function

3. Sluit het codevenster, ga terug naar het werkblad en voer deze formule in: =RemoveDupeswords(A2,", ")in een lege cel naast je gegevens. Sleep vervolgens de vulgreep naar de cellen waar je deze formule wilt toepassen, zie screenshot:

Opmerking: "A2" is de cel waarvan je dubbele woorden wilt verwijderen, en de komma en spatie (", ") zijn de scheidingstekens tussen de tekstreeksen. Je kunt deze aanpassen naar andere scheidingstekens indien gewenst.

Tip: Deze functie is niet hoofdlettergevoelig; kleine en hoofdletters worden als dezelfde tekens beschouwd.

4.3 Tekstreeks inkorten tot N woorden

Als je een lange tekstreeks in een cel hebt, wil je deze soms inkorten tot een bepaald aantal woorden, dus alleen de eerste n woorden behouden en de rest verwijderen. In dit gedeelte vind je enkele methoden om deze taak in Excel uit te voeren.

Tekstreeks inkorten tot N woorden met een formule

Om een tekstreeks tot N woorden in te korten, kun je een formule maken op basis van de functies LEFT, FIND en SUBSTITUTE. De algemene syntaxis is:

• "text": De tekstreeks of celverwijzing die je wilt inkorten;
• "N": Het aantal woorden dat je vanaf de linkerkant van de opgegeven tekstreeks wilt behouden.

Kopieer of voer de onderstaande formule in een lege cel in om deze taak uit te voeren:

=LEFT(A2,FIND("~",SUBSTITUTE(A2," ","~",B2))-1)

Sleep vervolgens de vulgreep naar beneden om deze formule naar andere cellen te kopiëren, zie screenshot:

Tekstreeks inkorten tot N woorden met een User Defined Function

Naast bovenstaande formule kun je ook een User Defined Function maken om deze taak uit te voeren. Doe dit als volgt:

1. Houd de toetsen "Alt + F11" ingedrukt om het venster "Microsoft Visual Basic for Applications" te openen.

2. Klik op "Invoegen" > "Module" en plak de volgende code in het modulevenster.

VBA-code: Tekstreeks inkorten tot N woorden

Function GetNWords(StrWords As String, Num_of_Words As Integer) As String
'Updateby Extendoffice
Dim xArr
Dim xRes As String
Dim xF As Integer
xStr = StrWords
If (Num_of_Words < 1) Then
    GetNWords = ""
    Exit Function
End If
xArr = Split(xStr, " ")
xRes = ""
On Error Resume Next
For xF = 0 To UBound(xArr)
    If Trim(xArr(xF)) <> "" Then
    Num_of_Words = Num_of_Words - 1
        If xRes = "" Then
            xRes = Trim(xArr(xF))
        Else
            xRes = xRes & " " & Trim(xArr(xF))
        End If
    End If
    If Num_of_Words = 0 Then Exit For
Next
If Num_of_Words = 0 Then
    GetNWords = xRes & "..."
Else
    GetNWords = xRes & "..."
End If
End Function

3. Sluit het codevenster, ga terug naar het werkblad en voer deze formule in: =GetNWords(A2,B2)in een lege cel. Sleep vervolgens de vulgreep naar beneden om deze formule naar andere cellen te kopiëren. Alleen het opgegeven aantal eerste woorden blijft over, zoals hieronder weergegeven: