In Excel tekst en getal toevoegen op een opgegeven positie in een cel

In Excel is het toevoegen van tekst of getallen aan cellen een veelvoorkomende taak. Denk aan het toevoegen van een spatie tussen namen, het toevoegen van een voorvoegsel of achtervoegsel aan cellen, of het toevoegen van streepjes aan sofinummers. In deze handleiding worden vrijwel alle scenario's voor toevoegen in Excel opgesomd en de bijbehorende methoden aangeboden.

Opmerking

In deze handleiding maak ik enkele voorbeelden om de methoden uit te leggen. Je kunt de verwijzingen aanpassen aan je eigen situatie wanneer je onderstaande VBA-code of formules gebruikt, of je kunt de voorbeelden downloaden om de methoden direct uit te proberen.

Klik om het voorbeeldbestand te downloaden

In dit deel worden verschillende methoden opgesomd om tekens toe te voegen aan het begin van elke cel, zoals te zien op onderstaande schermafbeelding:

1.1 Toevoegen aan het begin van cellen met behulp van een formule

Hier kun je een van de onderstaande drie formules kiezen:

Formule1: Concatenatie-operator "&"

Meerdere teksten samenvoegen met het ampersand-teken "&".

"tekst"&cel

Formule2: CONCATENATE-functie

De CONCATENATE-functie wordt gebruikt om teksten samen te voegen.

CONCATENATE("tekst",cel)

Formule3: CONCAT-functie

Dit is een nieuwe functie die alleen beschikbaar is in Excel2019, Office365 en Excel online.

CONCAT("tekst",cel)

In de algemene formules: cel is de celverwijzing naar de tekst waaraan je een voorvoegsel wilt toevoegen, en tekst is de tekst die je aan de cel wilt toevoegen.

Gebruikmakend van het bovenstaande voorbeeld, kun je de formules in de formulebalk als volgt gebruiken:

"&"

=$E$3&B3 of ="Iphone"&B3

CONCATENATE-functie

=CONCATENATE($E$3,B3) of =CONCATENATE("Iphone",B3)

CONCAT-functie

=CONCAT($E$3,B3) of =CONCAT("Iphone",B3)

Druk op Enter om het resultaat te krijgen, en sleep vervolgens de vulgreep naar beneden om tekst toe te voegen aan elke cel van bereik B3:B6.

1.2 Toevoegen aan het begin van cellen met Flash Fill

Als je Excel2013 of een latere versie gebruikt, kan de krachtige nieuwe functie Flash Fill de cellen automatisch invullen op basis van de cellen die je hierboven hebt ingevoerd.

Kies een cel naast de eerste originele gegevens en typ handmatig de eerste gegevens met het voorvoegsel zoals hieronder weergegeven:

Typ vervolgens in de cel eronder de tweede gegevens met het voorvoegsel. Tijdens het typen verschijnt er een grijze lijst. Druk op Enter om Flash Fill toe te staan de gegevens automatisch in te vullen.

Als de voorbeeldlijst niet wordt gegenereerd, ga dan naar het tabblad Start en klik op Plakken > Flash Fill om het handmatig uit te voeren.

1.3 Toevoegen aan het begin van cellen met VBA-code

Als je bekend bent met VBA-code, vind je hier een VBA-code om tekst toe te voegen aan het begin van elke cel in een bereik.

1. Selecteer een bereik met cellen waaraan je het voorvoegsel wilt toevoegen, druk vervolgens op Alt + F11 om het Microsoft Visual Basic for Applications-venster te openen.

2. Klik in het pop-upvenster op Invoegen > Module om een nieuwe lege module in te voegen.

3. Kopieer en plak onderstaande VBA-code in de nieuwe module.

VBA: Toevoegen aan het begin van cellen

Sub AppendToExistingOnLeft()
Dim c As Range
For Each c In Selection
If c.Value <> "" Then c.Value = "Iphone" & c.Value
Next
End Sub

4. Druk op F5 of klik op Uitvoeren knop in het venster om de VBA-code te activeren.

Nu is aan alle geselecteerde cellen de tekst "Iphone" aan het begin toegevoegd.

Naast het toevoegen van tekst aan het begin van cellen, is het toevoegen van tekst aan het einde van cellen ook gebruikelijk. Hier nemen we als voorbeeld het toevoegen van een eenheid aan het einde van elke cel, zoals te zien op onderstaande schermafbeelding:

2.1 Toevoegen aan het einde van cellen met behulp van een formule

Hier kun je een van de onderstaande drie formules kiezen:

Formule1: Concatenatie-operator "&"

Meerdere teksten samenvoegen met het ampersand-teken "&".

Cel&"tekst"

Formule2: CONCATENATE-functie

De CONCATENATE-functie wordt gebruikt om teksten samen te voegen.

CONCATENATE(cel,"tekst")

Formule3: CONCAT-functie

Dit is een nieuwe functie die alleen beschikbaar is in Excel2019, Office365 en Excel online.

CONCAT(cel,"tekst")

In de algemene formules: cel is de celverwijzing naar de tekst waaraan je een achtervoegsel wilt toevoegen, en tekst is de tekst die je aan de cel wilt toevoegen.

Gebruikmakend van het bovenstaande voorbeeld, kun je de formules in de formulebalk als volgt gebruiken:

"&"

=B3&$E$3 of =B3&"Kg"

CONCATENATE-functie

=CONCATENATE(B3, $E$3) of =CONCATENATE(B3, "Kg")

CONCAT-functie

=CONCAT(B3, $E$3) of =CONCAT(B3, "Kg")

Druk op Enter om het resultaat te krijgen, en sleep vervolgens de vulgreep naar beneden om tekst toe te voegen aan het einde van elke cel van bereik B3:B6.

2.2 Toevoegen aan het einde van cellen met Flash Fill

Als je Excel2013 of een latere versie gebruikt, kan de krachtige nieuwe functie Flash Fill de cellen automatisch invullen op basis van de cellen die je hierboven hebt ingevoerd.

Kies een cel naast de eerste originele gegevens en typ handmatig de eerste gegevens met het achtervoegsel zoals hieronder weergegeven:

Activeer vervolgens de cel onder het eerste resultaat en klik op Gegevens > Flash Fill, alle onderliggende cellen inclusief de actieve cel worden automatisch ingevuld.

2.3 Toevoegen aan het einde van cellen met VBA-code

Hier is een VBA-code die tekst aan het einde van elke cel in een bereik kan toevoegen.

1. Selecteer een bereik met cellen waaraan je het achtervoegsel wilt toevoegen, druk vervolgens op Alt + F11 om het Microsoft Visual Basic for Applications-venster te openen.

2. Klik in het pop-upvenster op Invoegen > Module om een nieuwe lege module in te voegen.

3. Kopieer en plak onderstaande VBA-code in de nieuwe module.

VBA: Toevoegen aan het einde van cellen

Sub AppendToExistingOnRight()
Dim c As Range
For Each c In Selection
If c.Value <> "" Then c.Value = c.Value & "Kg"
Next
End Sub

4. Druk op F5 of klik op Uitvoeren knop in het venster om de VBA-code te activeren.

Nu is de tekst "Kg" aan het einde van elke cel toegevoegd.

Voor de meesten is het toevoegen van tekst aan het begin of einde van cellen eenvoudig, maar tekst toevoegen in het midden van cellen kan wat lastiger zijn. Bijvoorbeeld, zoals op onderstaande schermafbeelding, een scheidingsteken zoals een dubbele punt : toevoegen na het tweede teken van elke cel in bereik B3:B7.

3.1 Toevoegen na het n-de teken van een tekenreeks met een formule

Hier zijn vier formules waarmee je deze taak kunt uitvoeren. Kies er één om te gebruiken:

Formule1: LEFT- en RIGHT-functies combineren

LEFT(cel, n) & "tekst" & RIGHT(cel, LEN(cel) -n)

Formule2: COMBINE CONCATENATE(CONCAT), RIGHT en LEN-functies

CONCATENATE(LEFT(cel, n), "tekst", RIGHT(cel, LEN(cel) -n))

Of

CONCAT(LEFT(cel, n), "tekst", RIGHT(cel, LEN(cel) -n))

Formule3: REPLACE-functie

REPLACE(cel, n+1,0, "tekst")

In de algemene formules: cel is de celverwijzing naar de tekst waaraan je tekst in het midden wilt toevoegen, tekst is de tekst die je wilt toevoegen, en n is het getal dat aangeeft na welk teken je wilt toevoegen.

Om de hierboven genoemde taak op te lossen, gebruik je de formules als volgt:

LEFT- en RIGHT-functies combineren

=LEFT(B3,2) & ":" & RIGHT(B3, LEN(B3) -2) of =LEFT(B3,2) & $E$3 & RIGHT(B3, LEN(B3) -2)

COMBINE CONCATENATE(CONCAT), RIGHT en LEN-functies

=CONCATENATE(LEFT(B3,2), ":", RIGHT(B3, LEN(B3) -2)) of =CONCAT (LEFT(B3,2), ":", RIGHT(B3, LEN(B3) -2))

REPLACE-functie

=REPLACE(B3,2+1,0, ":") of =REPLACE(B3,2+1,0, $E$3)

Druk op Enter om het resultaat te krijgen, en sleep vervolgens de vulgreep naar beneden om tekst toe te voegen na het tweede teken van elke cel in bereik B3:B7.

3.2 Toevoegen na het n-de teken van een tekenreeks met Flash Fill

Als je Excel2013 of een latere versie gebruikt, kan Flash Fill de cellen automatisch invullen op basis van de cellen die je hierboven hebt ingevoerd.

Kies een cel naast de eerste originele gegevens en typ handmatig de eerste gegevens met een dubbele punt na het tweede teken zoals hieronder weergegeven:

Activeer vervolgens de cel onder het eerste resultaat en druk op Ctrl + E om Flash Fill te activeren. Alle onderliggende cellen inclusief de actieve cel worden automatisch ingevuld.

3.3 Toevoegen na het n-de teken van een tekenreeks met VBA-code

Ook met VBA-code kun je deze taak uitvoeren.

1. Druk op Alt + F11 om het Microsoft Visual Basic for Applications-venster te openen.

2. Klik in het pop-upvenster op Invoegen > Module om een nieuwe lege module in te voegen.

3. Kopieer en plak onderstaande VBA-code in de nieuwe module.

VBA: Toevoegen in het midden van cellen

Sub AddToMidduleOfString()
    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 = VBA.Left(Rng.Value, 2 & ":" & VBA.Mid(Rng.Value, 3, VBA.Len(Rng.Value) - 1)
    Next
End Sub

4. Druk op F5 of klik op Uitvoeren knop in het venster om de VBA-code te activeren. Er verschijnt een dialoogvenster om cellen te selecteren waaraan je tekst wilt toevoegen.

5. Klik op OK. Nu is de tekst ":" toegevoegd na het tweede teken van elke cel in bereik B19:B23.

Als je klaar bent met formules en VBA, kun je een krachtig en handig hulpmiddel proberen, Kutools voor Excel, dat je kan helpen met de functie Tekst toevoegen functie.

Met deze Tekst toevoegen-functie kun je:

• Tekst toevoegen vóór het eerste teken
• Tekst toevoegen na het laatste teken
• Tekst toevoegen op opgegeven posities
• Uitbreiding
  Tekst toevoegen vóór hoofdletters
  Tekst toevoegen vóór kleine letters
  Tekst toevoegen vóór hoofdletters en kleine letters
  Tekst toevoegen vóór numerieke tekens

Selecteer de cellen waaraan je tekst wilt toevoegen en pas Tekst toevoegen toe door te klikken op Kutools > Tekst > Tekst toevoegen.

4.1 Tekst toevoegen vóór het eerste teken of na het laatste teken

In het Tekst toevoegen dialoogvenster, typ de tekst die je aan de cellen wilt toevoegen in het Tekst tekstvak, en vink vervolgens Voor het eerste karakter aan in het Positie gedeelte.

Klik op Toepassen of Ok om het toevoegen te voltooien.

Om tekst toe te voegen na het laatste teken, typ je de tekst die je wilt toevoegen in het Tekst tekstvak, en vink vervolgens Na het laatste karakter aan in het Positie gedeelte.

Klik op Toepassen of Ok om het toevoegen te voltooien.

4.2 Tekst toevoegen op opgegeven positie(s)

Soms wil je tekst toevoegen in het midden van een cel, of op meerdere posities in de cel. Stel dat je een scheidingsteken "-" wilt toevoegen na het derde en zevende cijfer van de sofinummers, zoals op onderstaande schermafbeelding:

In het Tekst toevoegen dialoogvenster, typ de tekst in het Tekst tekstvak, en vink Opgeven aan, typ vervolgens de posities gescheiden door komma's in het tekstvak en klik op Ok of Toepassen.

4.3 Uitbreiding van de Tekst toevoegen-functie

Naast het toevoegen van tekst op normale posities (begin, einde en opgegeven positie), ondersteunt de Tekst toevoegen-functie ook het toevoegen van tekst

• Voor hoofdletters
• Voor kleine letters
• Voor hoofdletters en kleine letters
• Voor numerieke tekens

In het Tekst toevoegen dialoogvenster, typ de tekst in het Tekst tekstvak, en vink Geavanceerd aan, klik vervolgens op het vervolgkeuzemenu hieronder om de gewenste bewerking te kiezen.

Naast de Tekst toevoegen-functie heeft Kutools voor Excel meer dan300 andere handige functies die je kunnen helpen bij verschillende Excel-taken. Download nu gratis.

In dit deel vind je formules om tekst toe te voegen vóór of na de eerste specifieke tekst die in Excel voorkomt.

5.1 Tekst toevoegen vóór de eerste specifieke tekst

Stel dat je de tekst "(sale)" vóór het eerste woord "price" in de cel wilt toevoegen, zoals op onderstaande schermafbeeldingen:

In Excel kunnen de functies REPLACE en SUBSTITUTE deze taak oplossen.

REPLACE-functie

REPLACE(cel,VIND.SPEC("zoek_tekst",cel),0,"toevoeg_tekst")

SUBSTITUTE-functie

SUBSTITUTE(cel,"zoek_tekst","vervang_tekst",1)

Gebruik in dit geval bovenstaande formules als volgt:

=REPLACE(B4,FIND("price",B4),0,"(sale)")

Of

=SUBSTITUTE(B4,"price","(sale)price",1)

De argumenten van de formule in dit geval zijn

Cel: B4,

Zoek_tekst: price,

Toevoeg_tekst: (sale),

Vervang_tekst: (sale)price.

Druk op Enter om het eerste resultaat te krijgen, en sleep vervolgens de vulgreep over de cellen die deze formule nodig hebben.

5.2 Tekst toevoegen na de eerste specifieke tekst

Stel dat je het valutasymbool "$" wilt toevoegen na de eerste dubbele punt ":" in de cel, zoals op onderstaande schermafbeeldingen:

In Excel kunnen de functies REPLACE en SUBSTITUTE deze taak oplossen.

REPLACE-functie

REPLACE(cel,VIND.SPEC("zoek_tekst",cel),zoek_tekst_lengte,"vervang_tekst")

SUBSTITUTE-functie

SUBSTITUTE(cel,"zoek_tekst","vervang_tekst")

Gebruik in dit geval bovenstaande formules als volgt:

=REPLACE(B12,FIND(":",B12),1,":$")

Of

=SUBSTITUTE(B12,":",":$")

De argumenten van de formule in dit geval zijn

Cel: B12,

Zoek_tekst: :,

Vervang_tekst: :$.

Zoek_tekst_lengte:1

Druk op Enter om het eerste resultaat te krijgen, en sleep vervolgens de vulgreep over de cellen die deze formule nodig hebben.

In dit deel van de handleiding worden drie verschillende methoden geïntroduceerd (formule, Zoeken en Vervangen, VBA) om tekens toe te voegen vóór of na elk woord in een cel.

Tekens toevoegen vóór elk woord

Tekens toevoegen na elk woord

6.1 Teken(s) toevoegen vóór of na elk woord met een formule

De SUBSTITUTE-functie kan worden gebruikt om deze taak op te lossen. De algemene formules zijn als volgt:

Toevoegen vóór elk woord

"teken"&SUBSTITUTE(tekst, " "," teken")

Toevoegen na elk woord

SUBSTITUTE(tekst," ","teken ")&"teken"

Stel dat je een "+" vóór elk woord in de lijst B3:B6 wilt toevoegen, gebruik dan de formule:

="+"&SUBSTITUTE(B3, " "," +")

Druk op Enter om het eerste resultaat te krijgen, en sleep vervolgens de vulgreep naar beneden om alle resultaten te krijgen.

Stel dat je ($) na elk woord in de lijst B11:B14 wilt toevoegen, gebruik dan de formule:

=SUBSTITUTE(B11," ","($) ")&"($)"

Druk op Enter om het eerste resultaat te krijgen, en sleep vervolgens de vulgreep naar beneden om alle resultaten te krijgen.

6.2 Teken(s) toevoegen vóór of na elk woord met Zoeken en Vervangen

Als je tekens wilt toevoegen vóór elk woord behalve het eerste, of na elk woord behalve het laatste, zoals op onderstaande schermafbeelding, kan de ingebouwde Excel-functie Zoeken en Vervangen deze taak uitvoeren.

Toevoegen vóór elk woord behalve het eerste

Toevoegen na elk woord behalve het laatste

1. Selecteer de cellen waaraan je tekens wilt toevoegen vóór of na elk woord, druk vervolgens op Ctrl + H om het dialoogvenster Zoeken en Vervangen te openen.

2. Typ een spatie in het tekstvak Zoeken naar, typ vervolgens de nieuwe tekens met een spatie in het tekstvak Vervangen door, en klik ten slotte op de knop Alles vervangen.

Hier vind je twee VBA-codes om tekens toe te voegen vóór of na elk woord. Voer de code uit volgens onderstaande stappen.

1. Druk op Alt + F11 om het Microsoft Visual Basic for Applications-venster te openen.

2. Klik in het pop-upvenster op Invoegen > Module om een nieuwe lege module in te voegen.

3. Kopieer en plak onderstaande VBA-code in de module.

Toevoegen vóór elk woord

Sub InsertCharBeforeWord()
'UpdateByExtendOffice
Dim ws As Worksheet
Dim xRg As Range
Dim xSRg As Range
Dim xCell As Range
Dim xInStr As String
Dim xArr As Variant
Dim xValue As String
    On Error Resume Next
   
    Set xSRg = Application.Selection
    Set xRg = Application.InputBox("Select cells(continuous):", "Kutools for Excel", xSRg.Address, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    xInStr = Application.InputBox("Type characters you want to add:", "Kutools for Excel", "", , , , , 2)
    If StrPtr(xInStr) = 0 Then Exit Sub
    Application.ScreenUpdating = False
    For Each xCell In xRg
        xArr = Split(xCell.Text, " ")
        xValue = ""
        For Each xStr In xArr
            If Trim(xStr) <> "" Then
                If xValue = "" Then
                    xValue = xInStr & Trim(xStr)
                Else
                    xValue = xValue & " " & xInStr & Trim(xStr)
                    End If
            End If
        Next
        xCell.Value = xValue
    Next
    Application.ScreenUpdating = True
End Sub

Toevoegen na elk woord

Sub InsertCharAfterWord()
'UpdateByExtendOffice
Dim ws As Worksheet
Dim xRg As Range
Dim xSRg As Range
Dim xCell As Range
Dim xInStr As String
Dim xArr As Variant
Dim xValue As String
    On Error Resume Next
 
    Set xSRg = Application.Selection
    Set xRg = Application.InputBox("Select cells(continuous):", "Kutools for Excel", xSRg.Address, , , , , 8)
    If xRg Is Nothing Then Exit Sub
   
    xInStr = Application.InputBox("Type characters you want to add:", "Kutools for Excel", "", , , , , 2)
    If StrPtr(xInStr) = 0 Then Exit Sub
   
    Application.ScreenUpdating = False
    For Each xCell In xRg
        xArr = Split(xCell.Text, " ")
        xValue = ""
        For Each xStr In xArr
            If Trim(xStr) <> "" Then
                If xValue = "" Then
                    xValue = Trim(xStr) & xInStr
                Else
                    xValue = xValue & " " & Trim(xStr) & xInStr
                End If
            End If
        Next
        xCell.Value = xValue
    Next
    Application.ScreenUpdating = True
End Sub

4. Druk op F5 om de code uit te voeren (of je kunt klikken op Uitvoeren knop ), er verschijnt een dialoogvenster waarin je wordt gevraagd om aaneengesloten cellen te selecteren, klik op OK.

5. In het tweede dialoogvenster dat verschijnt, typ je de tekens die je wilt toevoegen en klik je op OK.

Daarna worden de tekens aan het begin of einde van elk woord toegevoegd.

Soms wil je tekst toevoegen tussen elk teken. In dit deel worden twee scenario's besproken: tekst toevoegen tussen elk teken en spatie toevoegen tussen elk cijfer.

7.1 Tekst toevoegen tussen elk teken/cijfer met een door de gebruiker gedefinieerde functie

Om tekens toe te voegen tussen elk teken in een tekenreeks, kun je een door de gebruiker gedefinieerde functie gebruiken.

In dit geval voegen we een spatie toe tussen elk teken.

1. Druk op Alt + F11 om het Microsoft Visual Basic for Applications-venster te openen.

2. Klik in het pop-upvenster op Invoegen > Module om een nieuwe lege module in te voegen.

3. Kopieer en plak onderstaande VBA-code in de module.

Tekst toevoegen tussen tekens

Function AddText(Str As String) As String
    Dim i As Long
    For i = 1 To Len(Str)
        AddText = AddText & Mid(Str, i, 1) & " "
    Next i
    AddSpace = Trim(AddSpace)
End Function

4. Sla de code op en sluit het venster om terug te keren naar het werkblad. Typ vervolgens onderstaande formule in een cel waar je het resultaat wilt plaatsen.

=AddText(B3)

B3 is de cel die de tekenreeks bevat waartussen je spaties wilt toevoegen.

5. Druk op Enter om het resultaat te krijgen. Sleep de vulgreep over de cellen om andere resultaten te krijgen.

7.2 Spatie toevoegen tussen elk getal met een formule

Als je een spatie wilt toevoegen tussen cijfers van een tekenreeks die een numerieke waarde is, kun je de TEKST-functie gebruiken.

Stel dat je een lijst met8-cijferige getallen hebt in bereik B10:B12. Selecteer een cel waar je het resultaat wilt plaatsen en typ de volgende formule:

=TEXT(B10,"# # # # # # # #")

Druk op Enter en sleep vervolgens de vulgreep over andere cellen.

Als de lengte van de getallen5 is, wordt de formule: =TEXT(B10,"# # # # #")

Als je teken(s) wilt toevoegen tussen elk woord, bijvoorbeeld een spatie tussen voor-, midden- en achternaam zoals op onderstaande schermafbeelding, kun je een door de gebruiker gedefinieerde functie gebruiken.

1. Druk op Alt + F11 om het Microsoft Visual Basic for Applications-venster te openen.

2. Klik in het pop-upvenster op Invoegen > Module om een nieuwe lege module in te voegen.

3. Kopieer en plak onderstaande VBA-code in de module.

Tekens toevoegen tussen woorden

Function AddCharacters(pValue As String) As String
'UpdatebyExtendoffice20160908
Dim xOut As String
xOut = VBA.Left(pValue, 1)
For i = 2 To VBA.Len(pValue)
xAsc = VBA.Asc(VBA.Mid(pValue, i, 1))
If xAsc >= 65 And xAsc <= 90 Then
xOut = xOut & " " & VBA.Mid(pValue, i, 1)
Else
xOut = xOut & VBA.Mid(pValue, i, 1)
End If
Next
AddCharacters = xOut
End Function

4. Sla de code op en sluit het venster om terug te keren naar het werkblad. Typ vervolgens onderstaande formule in een cel waar je het resultaat wilt plaatsen.

=AddCharacters(B3)

B3 is de cel die de tekenreeks bevat waartussen je spaties wilt toevoegen.

5. Druk op Enter om het resultaat te krijgen. Sleep de vulgreep over de cellen om andere resultaten te krijgen.

Om specifieke teken(s) toe te voegen tussen tekst en getallen zoals op onderstaande schermafbeelding, vind je hier twee methoden.

9.1 Teken toevoegen tussen tekst en getallen met een formule

Als de tekst voor de getallen staat, is de algemene formule:

TRIM(REPLACE(tekst,MIN(FIND({1,2,3,4,5,6,7,8,9,0},tekst&"1234567890")),0,"-"))

Gebruik het bovenstaande geval als voorbeeld, gebruik de formule:

=TRIM(REPLACE(B4,MIN(FIND({1,2,3,4,5,6,7,8,9,0},B4&"1234567890")),0,"-"))

Druk op Enter om het eerste resultaat te krijgen, en sleep vervolgens de vulgreep over andere cellen om deze formule toe te passen.

Als de getallen voor de tekst staan, is de algemene formule:

LEFT(tekst,SOM(LEN(tekst)-LEN(SUBSTITUTE(tekst,{0,1,2,3,4,5,6,7,8,9},""))))&"."&RIGHT(tekst,LEN(B13)- SOM(LEN(tekst)-LEN(SUBSTITUTE(tekst, {0,1,2,3,4,5,6,7,8,9}, ""))))

Gebruik het bovenstaande geval als voorbeeld, gebruik de formule:

=LEFT(B13,SUM(LEN(B13)-LEN(SUBSTITUTE(B13,{0,1,2,3,4,5,6,7,8,9},""))))&"."&RIGHT(B13,LEN(B13)- SOM(LEN(B13)-LEN(SUBSTITUTE(B13, {0,1,2,3,4,5,6,7,8,9}, ""))))

Druk op Enter om het eerste resultaat te krijgen, en sleep vervolgens de vulgreep over andere cellen om deze formule toe te passen.

9.2 Teken toevoegen tussen tekst en getallen met Flash Fill

Als je Excel2013 of een latere versie gebruikt, kan Flash Fill je ook helpen om teken(s) toe te voegen tussen tekst en getallen.

Typ naast de originele gegevens de nieuwe gegevens waarin het teken/de tekens tussen tekst en getallen zijn toegevoegd. Druk vervolgens op Enter om naar de volgende cel van het eerste resultaat te gaan.

En druk op Ctrl + E om Flash Fill te activeren.

Uitbreiding

Deze handleiding bevat ook enkele scenario's voor het toevoegen van tekst die we in het dagelijks leven of op het werk kunnen tegenkomen.

Soms moet je scheidingstekens zoals streepjes toevoegen aan een reeks telefoonnummers of sofinummers, zoals op onderstaande schermafbeelding. Handmatig typen is tijdrovend als je honderden nummers moet bewerken. In dit deel worden drie handige methoden besproken om deze taak snel uit te voeren.

10.1 Streepjes toevoegen aan nummers met een formule

Hier zijn twee formules waarmee je snel streepjes aan nummers kunt toevoegen. Om streepjes toe te voegen na het derde en zesde cijfer in cel B3, gebruik je de volgende formules:

REPLACE-functie

=REPLACE(REPLACE(B3,4,0,"-"),8,0,"-")

TEKST-functie

=TEXT(B3,"???-???-??? ")

Druk op Enter om het eerste resultaat te krijgen, en sleep vervolgens de vulgreep over andere cellen.

10.2 Streepjes toevoegen aan nummers met Celopmaak instellen

Om direct streepjes toe te voegen aan de nummers in de originele cellen, kun je de functie Celopmaak instellen gebruiken.

1. Selecteer de nummers waaraan je streepjes wilt toevoegen, druk vervolgens op Ctrl +1 om het dialoogvenster Celopmaak instellen te openen.

2. In het dialoogvenster Celopmaak instellen, kies onder het tabblad Nummer de optie Aangepast uit het gedeelte Categorie.

3. Typ vervolgens aan de rechterkant Type in het gedeelte, typ ###-###-### in het tekstvak, klik op OK.

Nu zijn aan de geselecteerde nummers streepjes toegevoegd.

10.3 Streepjes toevoegen aan telefoonnummer met Tekst toevoegen van Kutools voor Excel

Als je Kutools voor Excel hebt geïnstalleerd, kan de functie Tekst toevoegen dit ook voor je doen.

1. Selecteer de cellen met de nummers waaraan je streepjes wilt toevoegen en klik op Kutools > Tekst > Tekst toevoegen.

2. In het Tekst toevoegen dialoogvenster, typ het streepje of een ander scheidingsteken in het Tekst tekstvak, kies Opgeven en typ vervolgens de posities waar je streepjes wilt toevoegen, gescheiden door komma's, in het onderstaande tekstvak.

3. Klik op Ok of Toepassen. Nu zijn aan de geselecteerde nummers streepjes toegevoegd.

Gratis download Tekst toevoegen voor30 dagen proefperiode.

Stel dat je een lijst met getallen hebt van verschillende lengtes, en je wilt voorloopnullen toevoegen zodat ze allemaal dezelfde lengte hebben en er netjes uitzien, zoals op onderstaande schermafbeelding. Hier zijn twee methoden om deze taak uit te voeren.

11.1 Voorloopnullen toevoegen aan cellen met Celopmaak instellen

Je kunt de functie Celopmaak instellen gebruiken om een aangepast formaat in te stellen en zo de celdikte te corrigeren door voorloopnullen toe te voegen.

1. Selecteer de cellen waaraan je voorloopnullen wilt toevoegen, druk vervolgens op Ctrl +1 om het dialoogvenster Celopmaak instellen te openen.

2. In het dialoogvenster Celopmaak instellen, kies onder het tabblad Nummer de optie Aangepast uit het gedeelte Categorie.

3. Typ vervolgens aan de rechterkant Type in het gedeelte, typ 0000 in het tekstvak (om de getallen op4 cijfers te zetten, typ0000; je kunt dit aanpassen naar andere lengtes, bijvoorbeeld5 cijfers:00000), klik op OK.

Nu zijn aan de getallen voorloopnullen toegevoegd en zijn ze allemaal even lang.

11.2 Voorloopnullen toevoegen aan cellen met een formule

Als je de oorspronkelijke gegevens niet wilt wijzigen, kun je een formule gebruiken om voorloopnullen toe te voegen aan getallen op andere locaties.

Hier zijn drie functies die je kunnen helpen.

Formule1: TEKST-functie

TEXT(getal,"00…")

Formule2: RECHTS-functie

RIGHT("00…"&getal,lengte)

Formule3: BASE-functie

BASE(getal,10,lengte)

Het aantal nullen in de formule is gelijk aan de lengte van het getal.

Stel dat je4 cijfers als lengte instelt, gebruik dan de volgende formules:

=TEXT(B10,"0000")

=RIGHT("0000"&B10,4)

=BASE(B10,10,4)

Druk op Enter en sleep vervolgens de vulgreep naar beneden.

Als je nullen aan het einde van getallen wilt toevoegen om een vaste lengte te krijgen, bijvoorbeeld nullen toevoegen aan de getallen in cel B3:B7 zodat ze5 cijfers lang zijn zoals op onderstaande schermafbeelding, kun je een formule gebruiken.

REPT-functie

getal&REPT("0",lengte-LEN(getal))

In de formule staat het argument "getal" voor het getal of de celverwijzing waaraan je nullen wilt toevoegen, en het argument "lengte" is de gewenste vaste lengte.

Gebruik de formule:

=B3&REPT("0",5-LEN(B3))

Druk op Enter en sleep vervolgens de vulgreep naar beneden over de cellen die deze formule nodig hebben.

Als je gegevens wilt exporteren of kopiëren van een werkblad naar een kladblok, kan de tekst rommelig worden weergegeven omdat de celwaarden verschillende lengtes hebben. Om ze netjes uit te lijnen zoals op onderstaande schermafbeelding, moet je spaties aan het einde toevoegen.

Om deze taak op te lossen, kun je de REPT-functie gebruiken.

LEFT(tekst & REPT(" ",max_lengte),max_lengte)

In de formule staat het argument "tekst" voor de tekst of celverwijzing waaraan je spaties wilt toevoegen, en het argument "max_lengte" is de vaste lengte die je wilt bereiken. Deze moet langer zijn dan of gelijk aan de maximale lengte van de gebruikte teksten.

Om spaties toe te voegen aan het einde van de teksten in cel B3:C6, gebruik je de volgende formule:

=LEFT(B3 & REPT(" ",10),10)

Druk op Enter en sleep vervolgens de vulgreep over alle cellen die deze formule nodig hebben.

In dit geval, omdat de langste gebruikte tekst9 tekens is, gebruiken we hier10.

Soms moet je aanhalingstekens of haakjes toevoegen rond de tekst of getallen in cellen, zoals op onderstaande schermafbeelding. Behalve handmatig één voor één typen, zijn er twee methoden in Excel om dit snel op te lossen.

14.1 Aanhalingstekens/haakjes toevoegen via Celopmaak instellen

Als je aanhalingstekens wilt toevoegen rond tekst in de originele gegevens, kan de functie Celopmaak instellen in Excel je helpen.

1. Selecteer de cellen waaraan je aanhalingstekens wilt toevoegen, druk op Ctrl +1 om het dialoogvenster Celopmaak instellen te openen.

2. In het Celopmaak instellen dialoogvenster, onder Nummer tabblad, kies Aangepast uit de lijst met Categorie, ga dan naar het rechtergedeelte om te typen "''"@"''" in het Type tekstvak. Klik op OK.

De tekst in de geselecteerde cellen is nu omgeven door aanhalingstekens.

Als je haakjes rond de tekst wilt toevoegen, gebruik dan (@) in het tekstvak Type van het dialoogvenster Celopmaak instellen.

14.2 Aanhalingstekens/haakjes toevoegen via formules

Met formules kun je ook aanhalingstekens toevoegen aan tekst in andere cellen.

Gebruik de &-koppeling

""""&tekst&""""

Of

char(34)&tekst&char(34)

In dit geval, tekst in cel B11, gebruik de volgende formules:

=""""&B11&""""

Of

=char(34)&B11&char(34)

Druk op Enter en sleep vervolgens de vulgreep naar beneden om de cellen te vullen.

Als je haakjes rond tekst wilt toevoegen, gebruik dan de formule "("&tekst&")".

14.3 Aanhalingstekens/haakjes toevoegen via VBA

Als je VBA-code nodig hebt om deze taak uit te voeren, vind je hier een VBA.

1. Druk op Alt + F11 om het Microsoft Visual Basic for Applications -venster te openen.

2. Klik in het pop-upvenster op Invoegen > Module om een nieuwe lege module in te voegen.

3. Kopieer en plak onderstaande VBA-code in de nieuwe module.

VBA: Aanhalingstekens toevoegen rond teksten

Sub addquotationmarksorbrackets()
'UpdatebyExtendOffice
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 = """" & Rng.Value & """"
Next
End Sub

4. Druk op F5 of klik op Uitvoeren knop in het venster om de VBA-code te activeren. Er verschijnt een dialoogvenster om cellen te selecteren waaraan je aanhalingstekens wilt toevoegen.

5. Klik op OK. Nu zijn aanhalingstekens toegevoegd rond de teksten in de geselecteerde cellen.

Soms wil je, om andere gebruikers de gegevens beter te laten begrijpen, tekst toevoegen aan het resultaat van een formule, zoals op onderstaande schermafbeelding. Om tekst in een formule op te nemen, gebruik je aanhalingstekens rond de tekst en voeg je de &-koppeling toe om de tekst en de formule samen te voegen.

Voorbeeld1: tekst toevoegen vóór formule

"Vandaag is "&TEXT(TODAY(),"dddd, mmmm dd.")

Voorbeeld2: tekst toevoegen na formule

TEXT(NOW(),"HH:MM:SS")&" is de huidige tijd."

Voorbeeld3: tekst aan beide zijden van formule toevoegen

"Vandaag verkocht "&gegevens&" kg."

---

De Beste Office Productiviteitstools

Kutools voor Excel lost de meeste van uw problemen op en verhoogt uw productiviteit met 80%

• Super Formulebalk (bewerk eenvoudig meerdere regels tekst en formules); Leeslayout (lees en bewerk eenvoudig grote aantallen cellen); Plakken in gefilterd bereik...
• Cellen/Rijen/Kolommen samenvoegen en gegevens behouden; Celinhoud splitsen; Dubbele rijen combineren en optellen/gemiddelde berekenen... Voorkom dubbele cellen; Bereiken vergelijken...
• Selecteer dubbele of unieke rijen; Selecteer lege rijen (alle cellen zijn leeg); Super Zoeken en Fuzzy Zoeken in veel werkboeken; Willekeurige selectie...
• Exacte kopie van meerdere cellen zonder formuleverwijzing te wijzigen; Automatisch verwijzingen naar meerdere werkbladen maken; Opsommingstekens, selectievakjes en meer invoegen...
• Favoriete en snel formules, bereiken, grafieken en afbeeldingen invoegen; Cellen versleutelen met wachtwoord; Mailinglijst maken en e-mails versturen...
• Tekst extraheren, Tekst toevoegen, Verwijder tekens op een specifieke positie, Spaties verwijderen; Maak en print statistieken per pagina; Converteer tussen celinhoud en opmerkingen...
• Superfilter (filter schema's opslaan en toepassen op andere werkbladen); Geavanceerd sorteren op maand/week/dag, frequentie en meer; Speciaal filteren op vet, cursief...
• Werkboeken en werkbladen combineren; Tabellen samenvoegen op basis van sleutelkolommen; Gegevens splitsen in meerdere werkbladen; Batchconversie xls, xlsx en PDF...
• Draaitabelgroepering op weeknummer, dag van de week en meer... Toon ontgrendelde, vergrendelde cellen met verschillende kleuren; Markeer cellen die een formule/naam bevatten...

Lees meer... Volledige functies 30-daagse gratis proefversie... Aanschaffen... 

---

Office Tab - brengt een tabbladinterface naar Office en maakt uw werk veel gemakkelijker

• Schakel tabbladbewerking en -lezen in Word, Excel, PowerPoint, Publisher, Access, Visio en Project in.
• Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster, in plaats van in nieuwe vensters.
• Verhoogt uw productiviteit met 50% en vermindert honderden muisklikken voor u elke dag!

Lees meer... Volledige functies 30-daagse gratis proefversie... Aanschaffen...