Excel voegt tekst en nummer toe aan de opgegeven positie van de cel
In Excel is het toevoegen van teksten of getallen aan cellen een veel voorkomende taak. Zoals het toevoegen van spatie tussen namen, het toevoegen van een voor- of achtervoegsel aan cellen, het toevoegen van streepjes aan sociale nummers. Hier in deze zelfstudie bevat het bijna alle scenario's voor het toevoegen in Excel en biedt het de bijbehorende methoden voor u.
Note
In deze zelfstudie maak ik enkele voorbeelden om de methoden uit te leggen, u kunt de referenties wijzigen die u nodig hebt wanneer u onderstaande VBA-code of formules gebruikt, of u kunt de voorbeelden downloaden om methoden rechtstreeks uit te proberen.
Klik om het voorbeeldbestand te downloaden
Dit deel bevat verschillende methoden voor het toevoegen van tekens aan het begin van elke cel, zoals hieronder afgebeeld:
1.1 Toevoegen aan het begin van cellen met behulp van formule
Hier kunt u een van onderstaande drie formules kiezen:
Formule1 Samenvoegen operator "&"
Voeg meerdere teksten samen met een ampersand-teken "&".
"tekst"&cel
Formule2 CONCATENATE-functie
CONCATENATE-functie wordt gebruikt om teksten samen te voegen.
SAMENVOEGEN("tekst",cel)
Formule3 CONCAT-functie
Dit is een nieuwe functie die alleen in Excel 2019, Office 365 en Excel online verschijnt.
CONCAT("tekst",cel)
In de generieke formules: cel is de celverwijzing naar de tekst waaraan u een voorvoegsel wilt toevoegen, en tekst is de tekst die vroeger aan de cel werd toegevoegd.
Met behulp van het bovenstaande voorbeeld kunt u de formules in de formulebalk als volgt gebruiken:
"&"
=$E$3&B3 or ="Iphone"&B3
CONCATENATE-functie
= SAMENVOEGEN ($ E $ 3, B3) or =CONCATENATE("Iphone",B3)
CONCAT-functie
=CONCAT ($E$3,B3) or =CONCAT("Iphone",B3)
Media Enter toets om het resultaat te krijgen en sleep vervolgens de hendel voor automatisch aanvullen naar beneden om tekst toe te voegen aan elke cel van het bereik B3:B6.
| Aandacht: Tekst moet tussen dubbele aanhalingstekens staan, of formule retourneert een foutwaarde #NAAM?. Als u de celverwijzing naar een tekst gebruikt, vergeet dan niet om de absolute verwijzing te gebruiken, u kunt op de F4-toets drukken om de relatieve verwijzing in absolute verwijzing te veranderen. |
1.2 Toevoegen aan het begin van cellen door Flash Fill
Als u in Excel 2013 of latere versies werkt, is de krachtige nieuwe functie, Flash Fill, kan de cellen vullen op basis van de bovenstaande cellen die u automatisch hebt ingevoerd.
Kies een cel naast de eerste originele gegevens, typ handmatig de eerste gegevens met de prefix-tekst zoals hieronder weergegeven:
Ga vervolgens in de cel hieronder door met het typen van de tweede gegevens met prefixtekst. Tijdens het typen wordt een lijst in grijze kleur weergegeven, druk op Enter toets om Flash Fill de gegevens automatisch te laten vullen.
Als de voorbeeldlijst niet wordt gegenereerd, gaat u naar het tabblad Start en klikt u op Vulling > Flash Fill om het handmatig uit te voeren.
1.3 Toevoegen aan het begin van cellen met behulp van VBA-code
Als u bekend bent met VBA-code, introduceert u hier een VBA-code om tekst toe te voegen aan het begin van elke cel in een bereik.
1. Selecteer een celbereik waaraan u de prefixtekst wilt toevoegen en druk vervolgens op anders + F11 toetsen om het Microsoft Visual Basic voor toepassingen venster.
2. Klik vervolgens in het pop-upvenster op Invoegen > Module om een lege nieuwe 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| Aandacht: in het codescript, iphone is de tekst die u aan het begin van geselecteerde cellen wilt toevoegen, verander deze naar wens. |
4. druk op F5 toets of klik lopen 
in het venster om de VBA-code te activeren.
Nu hebben alle geselecteerde cellen aan het begin de tekst "Iphone" toegevoegd.
Behalve het toevoegen van tekst aan het begin van cellen, is het ook gebruikelijk om tekst aan het einde van cellen toe te voegen. Hier nemen we het toevoegen van een eenheid aan het einde van elke cel voor voorbeelden zoals hieronder getoond screenshot:
2.1 Toevoegen aan het einde van cellen met behulp van formule
Hier kunt u een van onderstaande drie formules kiezen:
Formule1 Samenvoegen operator "&"
Voeg meerdere teksten samen met een ampersand-teken "&".
Cel&"tekst"
Formule2 CONCATENATE-functie
CONCATENATE-functie wordt gebruikt om teksten samen te voegen.
SAMENVOEGEN(cel,"tekst")
Formule3 CONCAT-functie
Dit is een nieuwe functie die alleen in Excel 2019, Office 365 en Excel online verschijnt.
CONCAT(cel;"tekst")
In de generieke formules: cel is de celverwijzing naar de tekst waaraan u een achtervoegsel wilt toevoegen, en tekst is de tekst die vroeger aan de cel werd toegevoegd.
Met behulp van het bovenstaande voorbeeld kunt u de formules in de formulebalk als volgt gebruiken:
"&"
=B3&$E$3 or =B3&"Kg"
CONCATENATE-functie
= SAMENVOEGEN(B3, $E$3) or = SAMENVOEGEN (B3, "Kg")
CONCAT-functie
=CONCAT(B3, $E$3) or =CONCAT(B3; "Kg")
Media Enter toets om het resultaat te krijgen en sleep vervolgens de hendel voor automatisch aanvullen naar beneden om tekst toe te voegen aan het einde van elke cel van het bereik B3:B6.
| Aandacht: Tekst moet tussen dubbele aanhalingstekens staan, of formule retourneert een foutwaarde #NAAM?. Als u de celverwijzing naar een tekst gebruikt, vergeet dan niet om de absolute verwijzing te gebruiken, u kunt op de F4-toets drukken om de relatieve verwijzing in absolute verwijzing te veranderen. |
2.2 Toevoegen aan het einde van cellen door Flash Fill
Als u in Excel 2013 of latere versies werkt, is de krachtige nieuwe functie, Flash Fill, kan de cellen vullen op basis van de bovenstaande cellen die u automatisch hebt ingevoerd.
Kies een cel naast de eerste originele gegevens, typ handmatig de eerste gegevens met de achtervoegseltekst zoals hieronder weergegeven:
Activeer vervolgens de cel onder het eerste resultaat, klik op Data > Flash Fill, worden alle onderstaande cellen inclusief de actieve cel automatisch gevuld.
2.3 Toevoegen aan het einde van cellen met behulp van VBA-code
Hier is een VBA-code die tekst kan toevoegen aan het einde van elke cel in een bereik.
1. Selecteer een celbereik waaraan u de achtervoegseltekst wilt toevoegen en druk vervolgens op anders + F11 toetsen om het Microsoft Visual Basic voor toepassingen venster.
2. Klik vervolgens in het pop-upvenster op Invoegen > Module om een lege nieuwe 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| Aandacht: in het codescript, Kg is de tekst die u aan het einde van geselecteerde cellen wilt toevoegen, verander deze naar wens. |
4. druk op F5 toets of klik lopen in het venster om de VBA-code te activeren.
Nu is de tekst "Kg" toegevoegd aan het einde van elke cel.
Voor de meesten van jullie is het toevoegen van teksten aan het begin of einde van cellen eenvoudig, maar het toevoegen van teksten aan het midden van cellen is misschien wat moeilijk. Neem bijvoorbeeld, zoals hieronder afgebeeld, een scheidingsteken toe te voegen, zoals dubbele punt: aan het einde van het tweede teken van elke cel in bereik B3:B7.
3.1 Toevoegen na het n-de teken van string volgens formule
Hier zijn vier formules voor u om deze taak uit te voeren, kies er een om te gebruiken:
Formule1 Combineer functies LINKS en RECHTS
LEFT(cel, n) & "tekst" & RECHTS(cel, LEN(cel) -n)
Formule2 COMBINEER CONCATENATE (CONCAT), RIGHT en LEN-functies
SAMENVOEGEN(LINKS(cel, n), "tekst", RECHTS(cel, LEN(cel) -n))
Or
CONCAT(LINKS(cel, n), "tekst", RECHTS(cel, LEN(cel) -n))
Formule3 VERVANG functie
VERVANG(cel, n+1, 0, "tekst")
In de generieke formules: cel is de celverwijzing naar de tekst waaraan u tekst in het midden wilt toevoegen, en tekst is de tekst die werd toegevoegd aan de cel, n is het getal dat bepaalt na welk teken in de tekenreeks u wilt Voeg tekst toe.
Gebruik voor het oplossen van de hierboven genoemde taak de volgende formules:
Combineer de functies LINKS en RECHTS
=LINKS(B3, 2) & ":" & RECHTS(B3, LEN(B3) -2) or =LINKS(B3, 2) & $E$3 & RECHTS(B3, LEN(B3) -2)
COMBINEER CONCATENATE (CONCAT), RIGHT en LEN-functies
=CONCATENATE(LINKS(B3, 2), ":", RECHTS(B3, LEN(B3) -2)) or =CONCAT (LINKS(B3, 2), ":", RECHTS(B3, LEN(B3) -2))
VERVANG functie
=VERVANG(B3, 2+1, 0, ":") or =VERVANG(B3, 2+1, 0, $E$3)
Media Enter toets om het resultaat te krijgen en sleep vervolgens de hendel voor automatisch aanvullen naar beneden om tekst toe te voegen na het tweede teken van elke cel van het bereik B3:B7.
| Aandacht: Tekst moet tussen dubbele aanhalingstekens staan, of formule retourneert een foutwaarde #NAAM?. Als u de celverwijzing naar een tekst gebruikt, vergeet dan niet om de absolute verwijzing te gebruiken, u kunt op de F4-toets drukken om de relatieve verwijzing in absolute verwijzing te veranderen. |
3.2 Toevoegen na het n-de teken van string door Flash Fill
Als u zich in Excel 2013 of latere versies bevindt, is de Flash Fill, kan de cellen vullen op basis van de bovenstaande cellen die u automatisch hebt ingevoerd.
Kies een cel naast de eerste originele gegevens, typ handmatig de eerste gegevens met een dubbele punt na het tweede teken zoals hieronder getoond:
Activeer vervolgens de cel onder het eerste resultaat, druk op Ctrl + E toetsen om te activeren Flash Fill commando, zijn alle onderstaande cellen inclusief de actieve cel automatisch gevuld.
| Aandacht: Als u zich in een MAC-systeem bevindt, drukt u op de Command + E-toetsen. |
3.3 Toevoegen na het n-de teken van de tekenreeks met behulp van VBA-code
Een VBA-code kan dit werk ook doen.
1. druk op anders + F11 toetsen om het Microsoft Visual Basic voor toepassingen venster.
2. Klik vervolgens in het pop-upvenster op Invoegen > Module om een lege nieuwe module in te voegen.
3. Kopieer en plak onderstaande VBA-code in de nieuwe module.
VBA: toevoegen aan 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| Aandacht: in het codescript, 2 is het nummer dat de positie definieert waarnaar u tekst wilt toevoegen, : is de tekst waaraan u wilt toevoegen, 3 is de som van 2 en de lengte van de tekst die je toevoegt. Neem nog een voorbeeld, om "en" toe te voegen na het eerste teken van de tekenreeks "AB", het script moet Rng.Value = VBA.Left(Rng.Value, 1) & "en" & VBA.Mid(Rng.Value, 4, VBA.Len(Rng.Waarde) - 1) |
4. druk op F5 toets of klik lopen in het venster om de VBA-code te activeren. Er verschijnt een dialoogvenster voor het selecteren van cellen om tekst toe te voegen.
5. klikken OK. Nu is de tekst ":" toegevoegd na het tweede teken van elke cel in bereik B19:B23.
4. Voeg tekst toe met Kutools voor Excel (voeg tekens toe aan een specifieke positie als je nodig hebt)
Als je formules en VBA beu bent, kun je een krachtige en handige tool proberen, Kutools for Excel, die u kan helpen met zijn toe te voegen tekst kenmerk.
Deze functie Tekst toevoegen kan:
- Tekst toevoegen voor eerste teken
- Tekst toevoegen na laatste teken
- Tekst toevoegen op gespecificeerde posities
- Verlenging
Tekst toevoegen vóór hoofdletters
Tekst toevoegen vóór kleine letters
Tekst toevoegen vóór hoofdletters/kleine letters
Tekst toevoegen vóór numerieke tekens
Klik om nu een gratis proefperiode van 30 dagen te krijgen.
Selecteer de cellen waaraan u tekst wilt toevoegen en pas vervolgens de tekst Toevoegen toe door te klikken op Kutools > Tekst > toe te voegen tekst.
4.1 Tekst toevoegen voor het eerste teken of na het laatste teken
In het toe te voegen tekst typt u de tekst die u aan de cellen wilt toevoegen in de Tekst tekstvak, vink dan aan Voor het eerste karakter in de Positie pagina.
Klik Solliciteer or Ok om het toevoegen te voltooien.
Als u tekst wilt toevoegen na het laatste teken, typt u de tekst die u aan de cellen wilt toevoegen in het Tekst tekstvak, vink dan aan Na het laatste karakter in de Positie pagina.
Klik Solliciteer or Ok om het toevoegen te voltooien.
4.2 Tekst toevoegen op gespecificeerde positie(s)
Soms wilt u tekst toevoegen aan het midden van de cel of tekst toevoegen aan verschillende posities van de cel. Stel dat het scheidingsteken "-" wordt toegevoegd na het derde en zevende cijfer van de sociale nummers, zoals hieronder afgebeeld:
In het toe te voegen tekst dialoogvenster, typ de tekst in Tekst textbox en vink aan Specificeren optie, typ vervolgens de posities met een komma om ze te scheiden in het tekstvak en klik vervolgens op Ok or Solliciteer.
4.3 Uitbreiding van het hulpprogramma Tekst toevoegen
Naast het toevoegen van tekst aan de normale posities (begin, eind en gespecificeerde positie), is de toe te voegen tekst hulpprogramma ondersteunt ook het toevoegen van tekst
- Voor hoofdletters
- Voor kleine letters
- Voor hoofdletters/kleine letters
- Voor numerieke tekens
In het toe te voegen tekst dialoogvenster, typ de tekst in Tekst textbox en vink aan Alleen toevoegen aan optie en klik vervolgens op het vervolgkeuzemenu hieronder om de gewenste bewerking te kiezen.
Dit deel bevat formules voor het toevoegen van tekst voor of na de voor het eerst verschenen specifieke tekst in Excel.
5.1 Tekst toevoegen vóór de eerste specifieke tekst
U wilt bijvoorbeeld tekst "(sale)" toevoegen vóór de eerste tekst "prijs" in de cel, zoals hieronder getoonde schermafbeeldingen:
In Excel kunnen de functies VERVANGEN en VERVANGEN deze taak oplossen.
VERVANG functie
REPLACE(cel,FIND("find_text",cell),0,"add_text")
SUBSTITUTE-functie
VERVANG(cel,"find_text","replace_text",1)
Gebruik in dit geval de bovenstaande formules als volgt:
=VERVANG(B4,FIND("prijs",B4),0,"(verkoop)")
Or
=VERVANGING(B4,"prijs","(verkoop)prijs",1)
De formule-argumenten in dit geval zijn:
cel: B4,
Zoek_tekst: prijs,
Add_text: (verkoop),
Vervang_tekst:(verkoop)prijs.
Media Enter toets om het eerste resultaat van het toevoegen te krijgen en sleep vervolgens de handgreep voor automatisch aanvullen over cellen die deze formule nodig hebben.
5.2 Tekst toevoegen na de eerste specifieke tekst
U wilt bijvoorbeeld valutasymbool "$" toevoegen na de eerste dubbele punt ":" in de cel, zoals hieronder getoonde schermafbeeldingen:
In Excel kunnen de functies VERVANGEN en VERVANGEN deze taak oplossen.
VERVANG functie
REPLACE(cel,FIND("find_text",cell),find_text_length,"replace_text")
SUBSTITUTE-functie
VERVANG(cel,"find_text","replace_text")
Gebruik in dit geval de bovenstaande formules als volgt:
=VERVANG(B12,FIND(":",B12),1,":$")
Or
=VERVANG(B12,":",":$")
De formule-argumenten in dit geval zijn:
cel: B12,
Vind tekst: :,
Vervang_tekst::$.
Zoek_tekst_lengte:1
Media Enter toets om het eerste resultaat van het toevoegen te krijgen en sleep vervolgens de handgreep voor automatisch aanvullen over cellen die deze formule nodig hebben.
In dit deel van de zelfstudie introduceert het drie verschillende methoden (formule, Zoeken en vervangen, VBA) om u te helpen tekens voor of na elk woord van een cel toe te voegen.
| Tekens toevoegen voor elk woord |
 |
| Tekens toevoegen na elk woord |
 |
6.1 Teken(s) voor of na elk woord per formule toevoegen
De SUBSTITUTE-functie kan worden gebruikt om deze taak op te lossen, de algemene formules zijn als volgt:
Voor elk woord toevoegen
"teken"&VERVANG(tekenreeks; " "," teken")
Toevoegen na elk woord
VERVANG(string," ","karakter ")&"karakter"
Stel dat u "+" toevoegt voor elk woord in de lijst van B3:B6, gebruik dan de formule als volgt:
="+"&VERVANG(B3, " "," +")
Media Enter toets om het eerste resultaat te krijgen en sleep vervolgens de hendel voor automatisch vullen naar beneden om alle resultaten te krijgen.
Stel dat u ($) toevoegt na elk woord van lijst B11:B14, gebruik dan de onderstaande formule:
=VERVANGING(B11," ","($) ")&"($)"
Media Enter toets om het eerste resultaat te krijgen en sleep vervolgens de hendel voor automatisch vullen naar beneden om alle resultaten te krijgen.
6.2 Teken(s) voor of na elk woord toevoegen door Zoeken en vervangen
Als u vóór elk woord tekens wilt toevoegen, met uitzondering van het eerste, of tekens wilt toevoegen na elk woord, met uitzondering van het laatste, zoals hieronder wordt weergegeven, is de ingebouwde Excel-functie Zoeken en vervangen hulpprogramma kan deze taak aan.
Toevoegen voor elk woord behalve het eerste
Voeg toe na elk woord, behalve het laatste
1. Selecteer de cellen waaraan u tekens voor of na elk woord wilt toevoegen en druk vervolgens op Ctrl + H toetsen om het Zoeken en vervangen dialoogvenster.
2. Typ een spatie in de Zoeken naar tekstvak, typ vervolgens de nieuwe tekens met spatie in Vervangen door tekstvak en klik ten slotte op de Alles vervangen knop.
| Werking | Algemeen | Voorbeeld |
| Tekens toevoegen voor elk woord | Nieuwe tekens toevoegen na een spatie | Voeg (verkoop) toe voor elk woord |
| Werking | Algemeen | Voorbeeld |
| Tekens toevoegen na elk woord | Nieuwe karakters gevolgd door een spatie | Toevoegen - na elk woord |
Hier vindt u twee VBA-codes voor het toevoegen van tekens voor of na elk woord. Voer de code uit door de onderstaande stappen te volgen.
1. druk op anders + F11 toetsen om het Microsoft Visual Basic voor toepassingen venster.
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.
Voor elk woord toevoegen
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 SubToevoegen 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 Sub4. druk op F5 toets om de code uit te voeren (of u kunt klikken op lopen ), verschijnt er een dialoogvenster waarin u wordt gevraagd om doorlopende cellen te selecteren om te werken, klik op OK.
5. Typ in het tweede pop-outvenster de tekens die u wilt toevoegen en klik op OK.
Vervolgens worden de tekens aan de voorkant of het einde van elk woord toegevoegd.
Soms wilt u misschien tekst tussen alle tekens toevoegen. Hier in dit deel somt het twee scenario's op, één is om tekst toe te voegen tussen elk teken, een andere is om ruimte toe te voegen tussen elk nummer.
7.1 Voeg tekst toe tussen elk teken/cijfer met de door de gebruiker gedefinieerde functie
Om tekens toe te voegen tussen elk teken in een tekenreeks, kan de door de gebruiker gedefinieerde functie u helpen.
In dit geval voegen we een spatie toe tussen elk teken.
1. druk op anders + F11 toetsen om het Microsoft Visual Basic voor toepassingen venster.
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 tussen tekens toevoegen
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 Function4. Sla de code op en sluit het venster om terug te gaan naar het werkblad en typ vervolgens de onderstaande formule in een cel waarin u het optelresultaat wilt plaatsen.
=ToevoegenTekst(B3)
B3 is de cel die de tekenreeks bevat die u tussen tekens wilt toevoegen.
5. druk op Enter toets om het resultaat te krijgen, sleep de hendel voor automatisch vullen over cellen om andere resultaten te krijgen als u dat wilt.
|
Aandacht: 1） In de VBA-code kunt u de "" naar andere teksten als je nodig hebt, in de veronderstelling dat je het verandert in "-" toe te voegen - tussen tekens. 2） De code voegt de nieuwe tekst toe na elk teken. U kunt de laatst toegevoegde tekst verwijderen als u deze niet nodig hebt door de onderstaande formule te gebruiken. Ga voor meer informatie over de formule naar: Hoe de eerste of laatste N-tekens uit een cel of tekenreeks in Exc . te verwijderenel? LINKS(cel;LEN(cel)-tekst_lengte) |
7.2 Voeg spatie toe tussen elk nummer met formule
Als u spatie wilt toevoegen tussen cijfers van een tekenreeks die een numerieke waarde is, kunt u de TEKST-functie gebruiken.
| Aandacht: de nummers van het bereik waartussen u ruimte wilt toevoegen, moeten even lang zijn, anders kunnen sommige resultaten onjuist zijn. |
Stel dat hier een lijst is met 8-cijferige getallen in het bereik B10: B12, selecteer dan een cel die het resultaat zal plaatsen, typ onderstaande formule:
=TEKST(B10,"# # # # # # # #")
Media Enter toets en sleep vervolgens de handgreep voor automatisch aanvullen over andere cellen.
Als de lengte van getallen 5 is, wordt de formule gewijzigd als: =TEKST(B10,"# # # # #").
Als u karakter(s) tussen elk woord wilt toevoegen, in de veronderstelling dat u een spatie toevoegt tussen de eerste, middelste en laatste namen, zoals hieronder afgebeeld, kunt u een door de gebruiker gedefinieerde functie gebruiken.
1. druk op anders + F11 toetsen om het Microsoft Visual Basic voor toepassingen venster.
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 tussen woorden toevoegen
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 Function4. Sla de code op en sluit het venster om terug te gaan naar het werkblad en typ vervolgens de onderstaande formule in een cel waarin u het optelresultaat wilt plaatsen.
=Tekens toevoegen(B3)
B3 is de cel die de tekenreeks bevat die u tussen woorden wilt toevoegen.
5. druk op Enter toets om het resultaat te krijgen, sleep de hendel voor automatisch vullen over cellen om andere resultaten te krijgen als u dat wilt.
| Aandacht: in de VBA-code kunt u de . wijzigen "" naar andere teksten als je nodig hebt, in de veronderstelling dat je het verandert in "-" toe te voegen - tussen woorden. |
Om specifieke tekens tussen teksten en cijfers toe te voegen, zoals hieronder afgebeeld, biedt hier twee methoden.
9.1 Teken toevoegen tussen teksten en cijfers met formule
Als de teksten voor de cijfers staan, is de generieke formule
TRIM(REPLACE(string,MIN(FIND({1,2,3,4,5,6,7,8,9,0},string&"1234567890")),0,"-"))
Gebruik het bovenstaande geval als voorbeeld en gebruik de formule als volgt:
=TRIM(REPLACE(B4,MIN(FIND({1,2,3,4,5,6,7,8,9,0},B4&"1234567890")),0,"-"))
Media Enter toets om het eerste resultaat te krijgen en sleep vervolgens de handgreep voor automatisch vullen over andere cellen om deze formule te vullen.
Als de cijfers voor de teksten staan, is de generieke formule
LEFT(string,SUM(LEN(string)-LEN(SUBSTITUTE(string,{0,1,2,3,4,5,6,7,8,9},""))))&"."&RIGHT (string,LEN(B13)- SUM(LEN(string)-LEN(SUBSTITUTE(string, {0,1,2,3,4,5,6,7,8,9}, ""))))
Gebruik het bovenstaande geval als voorbeeld en gebruik de formule als volgt:
=LEFT(B13,SUM(LEN(B13)-LEN(SUBSTITUTE(B13,{0,1,2,3,4,5,6,7,8,9},""))))&"."&RIGHT(B13,LEN(B13)- SUM(LEN(B13)-LEN(SUBSTITUTE(B13, {0,1,2,3,4,5,6,7,8,9}, ""))))
Media Enter toets om het eerste resultaat te krijgen en sleep vervolgens de handgreep voor automatisch vullen over andere cellen om deze formule te vullen.
9.2 Teken toevoegen tussen teksten en cijfers met Flash Fill
Als u zich in Excel 2013 of latere versies bevindt, is de Flash Fill kan u ook helpen bij het toevoegen van tekens tussen teksten en cijfers.
Typ naast de oorspronkelijke gegevens de nieuwe gegevens waaraan de letter(s) tussen teksten en cijfers zijn toegevoegd. Druk vervolgens op de Enter-toets om naar de volgende cel van het eerste resultaat te gaan.
En druk op Ctrl + E toetsen om Flash Fill in te schakelen.
Verlenging
Deze zelfstudie bevat ook enkele scenario's over het toevoegen van tekst die we in ons dagelijks leven of werk kunnen tegenkomen.
Soms moet u scheidingstekens zoals streepjes toevoegen aan een reeks telefoonnummers of sociale nummers, zoals hieronder afgebeeld. Handmatig typen wordt geprobeerd als er honderden getallen moeten worden toegevoegd. In dit deel introduceert het drie trucjes om deze taak snel uit te voeren.
10.1 Voeg streepjes toe aan getallen volgens formule
Hier zijn twee formules waarmee snel streepjes aan getallen kunnen worden toegevoegd. Als u streepjes wilt toevoegen na het derde en zesde cijfer in cel B3, gebruikt u de formule als volgt:
VERVANG functie
=VERVANG(VERVANG(B3,4,0,"-"),8,0,"-")
TEXT-functie
=TEKST(B3,"???-???-??? ")
Media Enter toets om het eerste resultaat te krijgen en sleep vervolgens de handgreep voor automatisch vullen over andere cellen.
|
Aandacht: U kunt de argumenten van bovenstaande formules naar behoefte wijzigen. stel dat toevoegen "-" na het tweede, vierde en zesde cijfer kun je de formules gebruiken als: =VERVANG(VERVANG(VERVANG(B3,3,0,"-"),6,0,"-"),9,0,"-") =TEKST(B3,"??-??-??-??? ") |
10.2 Voeg streepjes toe aan getallen door Cellen opmaken
Om rechtstreeks streepjes toe te voegen aan de getallen in de originele cellen, kunt u de gebruiken Cellen opmaken kenmerk.
1. Selecteer de cijfers waaraan u streepjes wilt toevoegen en druk vervolgens op Ctrl + 1 toetsen om het Cellen opmaken dialoogvenster.
2. In de Cellen opmaken dialoogvenster, onder het Telefoon Nummer tabblad, kies Eigen van het Categorie pagina.
3. Dan rechts Type sectie, typ ###-###-### in het tekstvak, klik op OK.
Nu zijn de geselecteerde nummers met streepjes toegevoegd.
10.3 Voeg streepjes toe aan telefoonnummer door Tekst toevoegen van Kutools voor Excel
Als je Kutools for Excel geïnstalleerd in Excel, de toe te voegen tekst functie kan je ook een plezier doen.
1. Selecteer de cellen die de nummers bevatten die streepjes moeten toevoegen, klik op Kutools > Tekst > Voeg tekst toe.
2. In de toe te voegen tekst dialoogvenster, typ het streepje of een ander scheidingsteken in Tekst textbox, kies Specificeren optie, typ vervolgens de posities waar u streepjes achter wilt toevoegen en scheid ze door komma's in het onderstaande tekstvak.
3. klikken Ok of Toepassen. Vervolgens zijn de geselecteerde nummers met streepjes toegevoegd.
Gratis download Tekst toevoegen voor een proefperiode van 30 dagen.
Stel dat er een lijst met getallen in verschillende lengtes is, dan wil je voorloopnullen toevoegen om ze even lang te maken en er netjes uit te zien zoals hieronder afgebeeld. Hier zijn twee methoden die u kunt gebruiken om de klus te klaren.
11.1 Voorloopnullen toevoegen aan cellen met de functie Cellen opmaken
U kunt gebruik maken van de Cellen opmaken functie om een aangepast formaat in te stellen om de cellengte vast te leggen door voorloopnullen toe te voegen.
1. Selecteer de cellen waaraan u voorloopnullen wilt toevoegen en druk vervolgens op Ctrl + 1 toetsen om in te schakelen Cellen opmaken dialoogvenster.
2. In de Cellen opmaken dialoogvenster, onder het Telefoon Nummer tabblad, kies Eigen van het Categorie pagina.
3. Dan rechts Type sectie, typ 0000 in het tekstvak (om de nummerlengte vast te stellen op 4-dight, typ 0000, u kunt het wijzigen in een andere lengte, zoals 5-dight, typ 00000), klik OK.
Vervolgens zijn de getallen met voorloopnullen en in dezelfde lengte toegevoegd.
| Aandacht: als de lengte van de originele gegevens de ingestelde lengte overschrijdt, worden de originele gegevens weergegeven zonder voorloopnullen. |
11.2 Voorloopnullen toevoegen aan cellen met formule
Als u de oorspronkelijke gegevens niet wilt wijzigen, kunt u de formule gebruiken om voorloopnullen toe te voegen aan getallen op andere locaties.
Hier zijn drie functies die u kunnen helpen.
Formule1 TEXT-functie
TEKST(getal,"00...")
Formule2 RECHTS-functie
RECHTS("00..."&getal,lengte)
Formule3 BASE-functie
BASIS(getal,10,lengte)
Het aantal nullen in de formule is gelijk aan de lengte van het getal.
Hier stelt u 4-cijferig in als de lengte van de getallen, gebruik de onderstaande formules:
= TEXT (B10; "0000")
=RECHTS("0000"&B10,4)
=BASIS(B10,10,4)
Media Enter toets en sleep de hendel voor automatisch vullen naar beneden.
| Aandacht: als de lengte van de originele gegevens de ingestelde lengte overschrijdt, worden de originele gegevens weergegeven zonder voorloopnullen. |
Als u naloopnullen aan getallen wilt toevoegen om een vaste lengte te maken, aangenomen dat u naloopnullen toevoegt aan de getallen in cel B3:B7 om ze in 5-cijferige lengte te maken, zoals hieronder afgebeeld, kunt u een formule toepassen om de taak uit te voeren .
REPT-functie
nummer&REPT("0",lengte-LENGTE(getal))
In de formule vertegenwoordigt het argument "getal" het getal of een celverwijzing waaraan u nullen wilt toevoegen, en het argument "lengte" is de vaste lengte die u wilt.
Gebruik de formule als volgt:
=B3&HERHALEN("0",5-LENGTE(B3))
Media Enter toets en sleep de handgreep voor automatisch vullen omlaag naar cellen die deze formule nodig hebben.
Als u gegevens van een werkblad naar een notitieblok wilt exporteren of kopiëren, kunnen de teksten slordig worden geplaatst omdat de celwaarden verschillende lengtes hebben. Om ze er netjes uit te laten zien, zoals hieronder afgebeeld, moet u volgruimte toevoegen.
Voor het oplossen van deze klus kan de REPT-functie u een plezier doen.
LINKS(tekst & HERHALEN(" ",max_lengte),max_lengte)
In de formule is het argument "tekst" de tekst of de celverwijzing waaraan u een spatie wilt toevoegen, en het argument "max_length" is de vaste lengte die u van de teksten wilt maken, die langer moet zijn dan of gelijk moet zijn aan de maximale lengte van de gebruikte teksten.
Gebruik onderstaande formule om volgspatie toe te voegen aan de teksten in cel B3:C6:
=LINKS(B3 & HERHALEN(" ",10),10)
Media Enter toets en sleep vervolgens de handgreep voor automatisch vullen naar alle cellen die deze formule nodig hebben.
In dit geval, aangezien de langste van de gebruikte tekst 9 tekens is, gebruiken we hier 10.
Soms moet u aanhalingstekens of haakjes toevoegen rond de tekst of cijfers in cellen, zoals hieronder afgebeeld, behalve handmatig één voor één typen, hier zijn twee methoden in Excel om het snel op te lossen.
14.1 Aanhalingstekens/haakjes toevoegen door Cellen opmaken
Als u aanhalingstekens rond tekst in de originele gegevens wilt toevoegen, kan de functie Cellen opmaken in Excel u helpen.
1. Selecteer de cellen die u aanhalingstekens wilt toevoegen, druk op Ctrl + 1 toetsen om het Cellen opmaken dialoogvenster.
2. In de Cellen opmaken dialoog, onder Telefoon Nummer tabblad, kies Eigen uit de lijst van Categorie, ga dan naar het rechtergedeelte om te typen "''" @ "''" in de Type tekstvak. Klik OK.
De tekst in geselecteerde cellen is tussen aanhalingstekens geplaatst.
Als u haakjes rond de tekst wilt plaatsen, gebruikt u in het tekstvak Type van het dialoogvenster Cellen opmaken (@).
14.2 Aanhalingstekens/haakjes toevoegen met formules
Formules kunnen ook worden gebruikt om aanhalingstekens toe te voegen aan tekst in andere cellen.
Gebruik & connector
""""&tekst&""""
Or
char (34) & tekst & char (34)
Gebruik in dit geval de tekst in cel B11, gebruik de formules als volgt:
=""""&B11&""""
Or
=char(34)&B11&char(34)
Media Enter toets en sleep de hendel voor automatisch vullen naar beneden om cellen te vullen.
Als u haakjes rond tekst wilt toevoegen, gebruikt u de formule als "("&tekst&")".
14.3 Aanhalingstekens/haakjes toevoegen door VBA
Als je VBA-code nodig hebt om deze taak op te lossen, is hier een VBA.
1. druk op anders + F11 toetsen om het Microsoft Visual Basic voor toepassingen venster.
2. Klik vervolgens in het pop-upvenster op Invoegen > Module om een lege nieuwe 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| Aandacht: in het codescript, """" & Rng.Waarde & """" geeft aan dat "" rond de teksten moet worden toegevoegd, als u haakjes () rond tekst wilt toevoegen, wijzigt u dit in "(" & Rng.Waarde & ")". |
4. druk op F5 toets of klik lopen in het venster om de VBA-code te activeren. Er verschijnt een dialoogvenster voor het selecteren van cellen om aanhalingstekens toe te voegen.
5. klikken OK. Nu zijn de aanhalingstekens toegevoegd rond teksten in de geselecteerde cellen.
Om andere gebruikers te helpen gegevens beter te begrijpen, moet u soms tekst toevoegen aan de formuleresultaten, zoals hieronder wordt weergegeven. Om tekst in een formule op te nemen, kunt u de aanhalingstekens rond de tekst gebruiken en de &-connector toevoegen om de tekst en de formule samen te voegen.
Geval 1 tekst toevoegen voor formule
"Vandaag is "&TEKST(VANDAAG(),"dddd, mmmm dd.")
Case 2 tekst toevoegen na formule
TEXT(NOW(),"UU:MM:SS")&" is de huidige tijd."
Case 3 tekst toevoegen aan twee kanten van formule
"Vandaag verkocht "&data&" kg."
Meer Excel-tutorials: |
|
Combineer meerdere werkmappen/werkbladen in één Tekst-, getal- en datumcellen splitsen (gescheiden in meerdere kolommen) Combineer de inhoud van meerdere cellen zonder gegevens te verliezen in Excel Vergelijk twee kolommen voor overeenkomsten en verschillen in Excel |
De beste tools voor kantoorproductiviteit
Kutools voor Excel lost de meeste van uw problemen op en verhoogt uw productiviteit met 80%
- Super Formula-balk (bewerk eenvoudig meerdere regels tekst en formule); Lay-out lezen (gemakkelijk grote aantallen cellen lezen en bewerken); Plakken in gefilterd bereik...
- Voeg cellen / rijen / kolommen samen en het bewaren van gegevens; Gespleten cellen inhoud; Combineer dubbele rijen en som / gemiddelde... Voorkom dubbele cellen; Vergelijk Ranges...
- Selecteer Dupliceren of Uniek Rijen; Selecteer lege rijen (alle cellen zijn leeg); Super zoeken en fuzzy zoeken in veel werkboeken; Willekeurige selectie ...
- Exacte kopie Meerdere cellen zonder de formuleverwijzing te wijzigen; Maak automatisch verwijzingen naar meerdere bladen; Plaats kogels, Selectievakjes en meer ...
- Favoriete formules en snel invoegen, Bereiken, grafieken en afbeeldingen; Versleutel cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
- Extraheer tekst, Tekst toevoegen, Verwijderen op positie, Ruimte verwijderen; Paging-subtotalen maken en afdrukken; Converteren tussen celinhoud en opmerkingen...
- Super filter (bewaar en pas filterschema's toe op andere bladen); Geavanceerd sorteren per maand / week / dag, frequentie en meer; Speciaal filter door vet, cursief ...
- Combineer werkmappen en werkbladen; Tabellen samenvoegen op basis van sleutelkolommen; Gegevens splitsen in meerdere bladen; Batch Converteer xls, xlsx en PDF...
- Draaitabel groeperen op weeknummer, dag van de week en meer ... Toon ontgrendelde, vergrendelde cellen door verschillende kleuren; Markeer cellen met formule / naam...
- Schakel bewerken en lezen met tabbladen in Word, Excel, PowerPoint in, Publisher, Access, Visio en Project.
- 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!
