Excel ajoute du texte et un nombre dans la position spécifiée de la cellule
Dans Excel, l'ajout de textes ou de nombres aux cellules est un travail très courant. Tels que l'ajout d'espace entre les noms, l'ajout de préfixe ou de suffixe aux cellules, l'ajout de tirets aux numéros sociaux. Ici, dans ce didacticiel, il répertorie presque tous les scénarios d'ajout dans Excel et vous fournit les méthodes correspondantes.
Notes
Dans ce didacticiel, je crée quelques exemples pour expliquer les méthodes, vous pouvez modifier les références dont vous avez besoin lorsque vous utilisez le code ou les formules VBA ci-dessous, ou vous pouvez télécharger les exemples pour essayer directement les méthodes.
Cliquez pour télécharger le fichier d'exemple
Cette partie répertorie différentes méthodes pour ajouter des caractères au début de chaque cellule, comme illustré ci-dessous :
1.1 Ajouter au début des cellules en utilisant la formule
Ici, vous pouvez choisir l'une des trois formules ci-dessous :
Opérateur de concaténation Formula1 "&"
Joignez plusieurs textes ensemble par un caractère esperluette "&".
"texte"&cellule
Fonction CONCATENER de Formula2
La fonction CONCATENATE est utilisée pour joindre des textes entre eux.
CONCATENER("texte",cellule)
Fonction CONCAT Formule3
Il s'agit d'une nouvelle fonction qui n'apparaît que dans Excel 2019, Office 365 et Excel en ligne.
CONCAT("texte",cellule)
Dans les formules génériques : la cellule est la référence de cellule au texte auquel vous souhaitez ajouter un préfixe, et le texte est le texte utilisé pour ajouter à la cellule.
En utilisant l'exemple ci-dessus, vous pouvez utiliser les formules dans la barre de formule comme celles-ci :
"&"
=$E$3&B3 or ="Iphone"&B3
Fonction CONCATENER
=CONCATENER($E$3,B3) or =CONCATENATE("Iphone",B3)
Fonction CONCAT
=CONCAT($E$3,B3) or =CONCAT("Iphone",B3)
Presse Entrer pour obtenir le résultat, puis faites glisser la poignée de remplissage automatique vers le bas pour ajouter du texte à chaque cellule de la plage B3:B6.
| Attention: le texte doit être placé entre guillemets doubles, ou la formule renvoie une valeur d'erreur #NAME?. Si vous utilisez la référence de cellule à un texte, n'oubliez pas d'utiliser la référence absolue, vous pouvez appuyer sur la touche F4 pour changer la référence relative en référence absolue. |
1.2 Ajouter au début des cellules par Flash Fill
Si vous utilisez Excel 2013 ou versions ultérieures, la nouvelle fonctionnalité puissante, Remplissage Flash, peut remplir les cellules en fonction des cellules ci-dessus que vous avez saisies automatiquement.
Choisissez une cellule à côté des premières données d'origine, saisissez manuellement les premières données avec le texte du préfixe comme indiqué ci-dessous :
Ensuite, dans la cellule ci-dessous, continuez à saisir la deuxième donnée avec le texte du préfixe. Pendant la saisie, une liste en gris s'affichera, appuyez sur Entrer pour permettre au Flash Fill de remplir les données automatiquement.
Si la liste d'aperçu ne se génère pas, accédez à l'onglet Accueil, puis cliquez sur Niveau de remplissage > Remplissage Flash pour l'exécuter manuellement.
1.3 Ajouter au début des cellules en utilisant le code VBA
Si vous connaissez le code VBA, voici un code VBA pour ajouter du texte au début de chaque cellule d'une plage.
1. Sélectionnez une plage de cellules à laquelle vous souhaitez ajouter le texte du préfixe, puis appuyez sur autre + F11 touches pour activer Microsoft Visual Basic pour applications fenêtre.
2. Ensuite, dans la fenêtre qui s'ouvre, cliquez sur insérer > Module pour insérer un nouveau module vierge.
3. Copiez et collez le code VBA ci-dessous dans le nouveau module.
VBA : ajouter au début des cellules
Sub AppendToExistingOnLeft()
Dim c As Range
For Each c In Selection
If c.Value <> "" Then c.Value = "Iphone" & c.Value
Next
End Sub| Attention: dans le script de code, iphone est le texte que vous souhaitez ajouter au début des cellules sélectionnées, modifiez-le selon vos besoins. |
4. presse F5 touche ou cliquez course bouton (dans la fenêtre de contrôle qui apparaît maintenant) 
dans la fenêtre pour activer le code VBA.
Maintenant, toutes les cellules sélectionnées ont été ajoutées au texte "Iphone" au début.
À l'exception de l'ajout de texte au début des cellules, l'ajout de texte à la fin des cellules est également courant. Ici, nous prenons l'ajout d'unité à la fin de chaque cellule pour des exemples, comme illustré ci-dessous :
2.1 Ajouter à la fin des cellules en utilisant la formule
Ici, vous pouvez choisir l'une des trois formules ci-dessous :
Opérateur de concaténation Formula1 "&"
Joignez plusieurs textes ensemble par un caractère esperluette "&".
Cellule&"texte"
Fonction CONCATENER de Formula2
La fonction CONCATENATE est utilisée pour joindre des textes entre eux.
CONCATENER(cellule,"texte")
Fonction CONCAT Formule3
Il s'agit d'une nouvelle fonction qui n'apparaît que dans Excel 2019, Office 365 et Excel en ligne.
CONCAT(cellule,"texte")
Dans les formules génériques : la cellule est la référence de la cellule au texte auquel vous souhaitez ajouter un suffixe et le texte est le texte utilisé pour l'ajout à la cellule.
En utilisant l'exemple ci-dessus, vous pouvez utiliser les formules dans la barre de formule comme celles-ci :
"&"
=B3&$E$3 or =B3&"Kg"
Fonction CONCATENER
=CONCATENER(B3, $E$3) or =CONCATENATE(B3, "Kg")
Fonction CONCAT
=CONCAT(B3, $E$3) or =CONCAT(B3, "Kg")
Presse Entrer pour obtenir le résultat, puis faites glisser la poignée de remplissage automatique vers le bas pour ajouter du texte à la fin de chaque cellule de la plage B3:B6.
| Attention: le texte doit être placé entre guillemets doubles, ou la formule renvoie une valeur d'erreur #NAME?. Si vous utilisez la référence de cellule à un texte, n'oubliez pas d'utiliser la référence absolue, vous pouvez appuyer sur la touche F4 pour changer la référence relative en référence absolue. |
2.2 Ajouter à la fin des cellules par Flash Fill
Si vous utilisez Excel 2013 ou versions ultérieures, la nouvelle fonctionnalité puissante, Remplissage Flash, peut remplir les cellules en fonction des cellules ci-dessus que vous avez saisies automatiquement.
Choisissez une cellule à côté des premières données d'origine, saisissez manuellement les premières données avec le texte de suffixe comme indiqué ci-dessous :
Activez ensuite la cellule sous le premier résultat, cliquez sur Données > Remplissage Flash, toutes les cellules ci-dessous, y compris la cellule active, seront remplies automatiquement.
2.3 Ajouter à la fin des cellules en utilisant le code VBA
Voici un code VBA pouvant ajouter du texte à la fin de chaque cellule d'une plage.
1. Sélectionnez une plage de cellules que vous souhaitez ajouter le texte de suffixe, puis appuyez sur autre + F11 touches pour activer Microsoft Visual Basic pour applications fenêtre.
2. Ensuite, dans la fenêtre qui s'ouvre, cliquez sur insérer > Module pour insérer un nouveau module vierge.
3. Copiez et collez le code VBA ci-dessous dans le nouveau module.
VBA : ajouter à la fin des cellules
Sub AppendToExistingOnRight()
Dim c As Range
For Each c In Selection
If c.Value <> "" Then c.Value = c.Value & "Kg"
Next
End Sub| Attention: dans le script de code, Kg est le texte que vous souhaitez ajouter à la fin des cellules sélectionnées, modifiez-le selon vos besoins. |
4. presse F5 touche ou cliquez course bouton (dans la fenêtre de contrôle qui apparaît maintenant) dans la fenêtre pour activer le code VBA.
Maintenant, le texte "Kg" a été ajouté à la fin de chaque cellule.
Pour la plupart d'entre vous, l'ajout de textes au début ou à la fin des cellules est facile, mais l'ajout de textes au milieu des cellules est peut-être un peu difficile. Prenons l'exemple, comme illustré ci-dessous, en ajoutant un délimiteur, tel que deux points : à la fin du deuxième caractère de chaque cellule de la plage B3:B7.
3.1 Ajouter après le nième caractère de la chaîne par formule
Voici quatre formules fournies pour vous permettre de gérer ce travail, choisissez-en une à utiliser :
Formule1 Combinez les fonctions GAUCHE et DROITE
GAUCHE(cellule, n) & "texte" & DROITE(cellule, LEN(cellule) -n)
Formula2 COMBINE les fonctions CONCATENATE (CONCAT), RIGHT et LEN
CONCATENER(GAUCHE(cellule, n), "texte", DROITE(cellule, LEN(cellule) -n))
Or
CONCAT(LEFT(cell, n), "texte", RIGHT(cell, LEN(cell) -n))
Fonction REMPLACER Formula3
REMPLACER(cellule, n+1, 0, "texte")
Dans les formules génériques : la cellule est la référence de la cellule au texte auquel vous souhaitez ajouter du texte au milieu, et le texte est le texte utilisé pour ajouter à la cellule, n est le nombre qui définit après quel caractère de la chaîne de texte vous souhaitez ajouter du texte.
Pour résoudre le travail mentionné ci-dessus, veuillez utiliser les formules suivantes :
Combinez les fonctions GAUCHE et DROITE
=GAUCHE(B3, 2) & ":" & DROITE(B3, LEN(B3) -2) or =GAUCHE(B3, 2) & $E$3 & DROITE(B3, LEN(B3) -2)
COMBINER les fonctions CONCATENATE(CONCAT), RIGHT et LEN
=CONCATENATE(GAUCHE(B3, 2), ":", DROIT(B3, LEN(B3) -2)) or =CONCAT (GAUCHE(B3, 2), ":", DROIT(B3, LEN(B3) -2))
Fonction REMPLACER
=REMPLACER(B3, 2+1, 0, ":") or =REMPLACER(B3, 2+1, 0, $E$3)
Presse Entrer pour obtenir le résultat, puis faites glisser la poignée de remplissage automatique vers le bas pour ajouter du texte après le deuxième caractère de chaque cellule de la plage B3:B7.
| Attention: le texte doit être placé entre guillemets doubles, ou la formule renvoie une valeur d'erreur #NAME?. Si vous utilisez la référence de cellule à un texte, n'oubliez pas d'utiliser la référence absolue, vous pouvez appuyer sur la touche F4 pour changer la référence relative en référence absolue. |
3.2 Ajouter après le nième caractère de la chaîne par Flash Fill
Si vous utilisez Excel 2013 ou des versions ultérieures, le Remplissage Flash, peut remplir les cellules en fonction des cellules ci-dessus que vous avez saisies automatiquement.
Choisissez une cellule à côté des premières données d'origine, saisissez manuellement les premières données avec deux points après le deuxième caractère comme indiqué ci-dessous :
Activez ensuite la cellule sous le premier résultat, appuyez sur Ctrl + E touches pour activer Remplissage Flash commande, toutes les cellules ci-dessous, y compris la cellule active, ont été remplies automatiquement.
| Attention: Si vous êtes dans le système MAC, appuyez sur les touches Commande + E. |
3.3 Ajouter après le nième caractère de la chaîne en utilisant le code VBA
Un code VBA peut également faire ce travail.
1. presse autre + F11 touches pour activer Microsoft Visual Basic pour applications fenêtre.
2. Ensuite, dans la fenêtre qui s'ouvre, cliquez sur insérer > Module pour insérer un nouveau module vierge.
3. Copiez et collez le code VBA ci-dessous dans le nouveau module.
VBA : ajouter au milieu des cellules
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| Attention: dans le script de code, 2 est le nombre définit la position après laquelle vous souhaitez ajouter du texte, : est le texte que vous souhaitez ajouter, 3 est la somme de 2 et de la longueur du texte que vous ajoutez. Prenons un autre exemple, pour ajouter "et" après le premier caractère de la chaîne "AB", le script doit être Rng.Value = VBA.Left(Rng.Value, 1) & "et" & VBA.Mid(Rng.Valeur, 4, VBA.Len(Rng.Valeur) - 1) |
4. presse F5 touche ou cliquez course bouton (dans la fenêtre de contrôle qui apparaît maintenant) dans la fenêtre pour activer le code VBA. Une boîte de dialogue apparaît pour sélectionner des cellules pour ajouter du texte.
5. Cliquez OK. Maintenant, le texte ":" a été ajouté après le deuxième caractère de chaque cellule de la plage B19:B23.
4. Ajoutez du texte avec Kutools for Excel (ajoutez des caractères à une position spécifique selon vos besoins)
Si vous en avez marre des formules et de VBA, vous pouvez essayer un outil puissant et pratique, Kutools pour Excel, qui peut vous aider dans sa Ajouter du texte fonction.
Cette fonctionnalité d'ajout de texte peut :
- Ajouter du texte avant le premier caractère
- Ajouter du texte après le dernier caractère
- Ajouter du texte aux positions spécifiées
- Extension
Ajouter du texte avant les majuscules
Ajouter du texte avant les lettres minuscules
Ajouter du texte avant les majuscules/minuscules
Ajouter du texte avant les caractères numériques
Cliquez pour obtenir un essai gratuit de 30 jours maintenant.
Sélectionnez les cellules auxquelles vous souhaitez ajouter du texte, puis appliquez l'option Ajouter du texte en cliquant sur Kutools > Texte > Ajouter du texte.
4.1 Ajouter du texte avant le premier caractère ou après le dernier caractère
Dans le Ajouter du texte boîte de dialogue, tapez le texte que vous souhaitez ajouter aux cellules dans la Texte zone de texte, puis cochez Avant le premier personnage dans le Position .
Cliquez Appliquer or Ok pour terminer l'ajout.
Pour ajouter du texte après le dernier caractère, tapez le texte que vous souhaitez ajouter aux cellules de la Texte zone de texte, puis cochez Après le dernier caractère dans le Position .
Cliquez Appliquer or Ok pour terminer l'ajout.
4.2 Ajouter du texte aux positions spécifiées
Parfois, vous souhaiterez peut-être ajouter du texte au milieu de la cellule ou ajouter du texte à plusieurs positions de la cellule. Supposons d'ajouter le séparateur "-" après les troisième et septième chiffres des numéros sociaux, comme illustré ci-dessous :
Dans le Ajouter du texte boîte de dialogue, tapez le texte dans Texte zone de texte et cochez Spécifier option, puis tapez les positions à l'aide d'une virgule pour séparer dans la zone de texte, puis cliquez sur Ok or Appliquer.
4.3 Extension de l'utilitaire Ajouter du texte
En plus d'ajouter du texte dans les positions normales (début, fin et position spécifiée), le Ajouter du texte l'utilitaire prend également en charge l'ajout de texte
- Avant les lettres majuscules
- Avant les lettres minuscules
- Avant les majuscules/minuscules
- Avant les caractères numériques
Dans le Ajouter du texte boîte de dialogue, tapez le texte dans Texte zone de texte et cochez Ajouter seulement à option, puis cliquez sur dans le menu déroulant ci-dessous pour choisir l'opération dont vous avez besoin.
Cette partie fournit des formules sur l'ajout de texte avant ou après le premier texte spécifique apparu dans Excel.
5.1 Ajouter du texte avant le premier texte spécifique
Par exemple, vous souhaitez ajouter le texte "(vente)" avant le premier texte "prix" dans la cellule comme illustré ci-dessous :
Dans Excel, les fonctions REMPLACER et SUBSTITUE peuvent résoudre ce problème.
Fonction REMPLACER
REMPLACER(cellule,TROUVE("texte_recherche",cellule),0,"ajout_texte")
Fonction SUBSTITUTE
REMPLACER(cellule,"find_text","replace_text",1)
Dans ce cas, veuillez utiliser les formules ci-dessus comme celles-ci :
=REMPLACER(B4,TROUVER("prix",B4),0,"(vente)")
Or
=REMPLACER(B4,"prix","prix (vente)",1)
Les arguments de la formule dans ce cas sont
Cellule : B4,
Find_text : prix,
Add_text : (vente),
Replace_text : (vente) prix.
Presse Entrer pour obtenir le premier résultat d'ajout, puis faites glisser la poignée de remplissage automatique sur les cellules qui ont besoin de cette formule.
5.2 Ajouter du texte après le premier texte spécifique
Par exemple, vous souhaitez ajouter le symbole monétaire "$" après le premier deux-points ":" dans la cellule comme illustré ci-dessous :
Dans Excel, les fonctions REMPLACER et SUBSTITUE peuvent résoudre ce problème.
Fonction REMPLACER
REMPLACER(cellule,RECHERCHER("trouver_texte",cellule),trouver_texte_longueur,"remplacer_texte")
Fonction SUBSTITUTE
SUBSTITUE(cellule,"find_text","replace_text")
Dans ce cas, veuillez utiliser les formules ci-dessus comme celles-ci :
=REMPLACER(B12,RECHERCHER(":",B12),1,":$")
Or
=REMPLACER(B12,":",":$")
Les arguments de la formule dans ce cas sont
Cellule : B12,
Find_text : :,
Replace_text ::$.
Find_text_length:1
Presse Entrer pour obtenir le premier résultat d'ajout, puis faites glisser la poignée de remplissage automatique sur les cellules qui ont besoin de cette formule.
Dans cette partie du didacticiel, il présente trois méthodes différentes (formule, Rechercher et remplacer, VBA) pour vous aider à ajouter des caractères avant ou après chaque mot de cellule.
| Ajouter des caractères avant chaque mot |
 |
| Ajouter des caractères après chaque mot |
 |
6.1 Ajouter des caractères avant ou après chaque mot par formule
La fonction REMPLACER peut être utilisée pour résoudre ce travail, les formules générales comme celles-ci :
Ajouter avant chaque mot
"caractère"&SUBSTITUTE(chaîne, " "," caractère")
Ajouter après chaque mot
REMPLACER(chaîne," ","caractère ")&"caractère"
Supposons que l'on ajoute "+" avant chaque mot dans la liste de B3:B6, veuillez utiliser la formule comme suit :
="+"&SUBSTITUT(B3, " "," +")
Presse Entrer pour obtenir le premier résultat, puis faites glisser la poignée de remplissage automatique vers le bas pour obtenir tous les résultats.
En supposant que l'on ajoute ($) après chaque mot de la liste B11:B14, veuillez utiliser la formule ci-dessous :
=REMPLACER(B11," ","($) ")&"($)"
Presse Entrer pour obtenir le premier résultat, puis faites glisser la poignée de remplissage automatique vers le bas pour obtenir tous les résultats.
6.2 Ajouter des caractères avant ou après chaque mot par Rechercher et Remplacer
Si vous souhaitez ajouter des caractères avant chaque mot à l'exclusion du premier, ou ajouter des caractères après chaque mot à l'exclusion du dernier comme illustré ci-dessous, la fonction Excel intégrée Rechercher et remplacer l'utilitaire peut gérer ce travail.
Ajouter avant chaque mot en excluant le premier
Ajouter après chaque mot à l'exception du dernier
1. Sélectionnez les cellules auxquelles vous souhaitez ajouter des caractères avant ou après chaque mot, puis appuyez sur Ctrl + H touches pour activer Rechercher et remplacer dialogue.
2. Tapez un espace dans le Trouvez ce que zone de texte, puis tapez les nouveaux caractères avec un espace dans Remplacez-le par zone de texte, et enfin cliquez sur le Remplacer tout .
| Opération | Généralités | Exemple |
| Ajouter des caractères avant chaque mot | Ajouter de nouveaux caractères après un espace | Ajouter (vente) avant chaque mot |
| Opération | Généralités | Exemple |
| Ajouter des caractères après chaque mot | Nouveaux caractères suivis d'un espace | Ajouter - après chaque mot |
Ici fournit deux codes VBA pour ajouter des caractères avant ou après chaque mot, veuillez exécuter le code en suivant les étapes ci-dessous.
1. presse autre + F11 touches pour activer Microsoft Visual Basic pour applications fenêtre.
2. Dans la fenêtre contextuelle, cliquez sur insérer > Module pour insérer un nouveau module vierge.
3. Copiez et collez le code VBA ci-dessous dans le module.
Ajouter avant chaque mot
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 SubAjouter après chaque mot
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. presse F5 pour exécuter le code (ou vous pouvez cliquer sur course bouton (dans la fenêtre de contrôle qui apparaît maintenant) ), une boîte de dialogue apparaît pour vous demander de sélectionner des cellules continues à utiliser, cliquez sur OK.
5. Dans la deuxième boîte de dialogue qui s'affiche, saisissez les caractères que vous souhaitez ajouter, cliquez sur OK.
Ensuite, les caractères seront ajoutés au début ou à la fin de chaque mot.
Parfois, vous voudrez peut-être ajouter du texte entre chaque caractère. Ici, dans cette partie, il répertorie deux scénarios, l'un consiste à ajouter du texte entre chaque caractère, un autre consiste à ajouter un espace entre chaque numéro.
7.1 Ajouter du texte entre chaque caractère/chiffre avec la fonction définie par l'utilisateur
Pour ajouter des caractères entre chaque caractère d'une chaîne, la fonction définie par l'utilisateur peut vous aider.
Dans ce cas, nous ajoutons un espace entre chaque caractère.
1. presse autre + F11 touches pour activer Microsoft Visual Basic pour applications fenêtre.
2. Dans la fenêtre contextuelle, cliquez sur insérer > Module pour insérer un nouveau module vierge.
3. Copiez et collez le code VBA ci-dessous dans le module.
Ajouter du texte entre les caractères
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. Enregistrez le code et fermez la fenêtre pour revenir à la feuille de calcul, puis tapez la formule ci-dessous dans une cellule dans laquelle vous souhaitez placer le résultat de l'ajout.
=AjouterTexte(B3)
B3 est la cellule qui contient la chaîne dans laquelle vous souhaitez ajouter un espace entre les caractères.
5. presse Entrer pour obtenir le résultat, faites glisser la poignée de remplissage automatique sur les cellules pour obtenir d'autres résultats selon vos besoins.
|
Attention: 1） Dans le code VBA, vous pouvez modifier le "" à d'autres textes selon vos besoins, en supposant que vous le changez en "-" ajouter - entre les caractères. 2） Le code ajoutera le nouveau texte après chaque caractère. Vous pouvez supprimer le dernier texte ajouté si vous n'en avez pas besoin en utilisant la formule ci-dessous. Pour plus de détails sur la formule, veuillez visiter: Comment supprimer le premier ou le dernier N caractères d'une cellule ou d'une chaîne dans Excel? GAUCHE(cellule,LEN(cellule)-longueur_texte) |
7.2 Ajouter un espace entre chaque nombre avec la formule
Si vous souhaitez ajouter un espace entre les chiffres d'une chaîne qui est une valeur numérique, vous pouvez utiliser la fonction TEXTE.
| Attention: les numéros de plage entre lesquels vous souhaitez ajouter un espace doivent être de la même longueur, sinon certains résultats peuvent être incorrects. |
Supposons que voici une liste de nombres à 8 chiffres dans la plage B10:B12, veuillez sélectionner une cellule qui placera le résultat, tapez la formule ci-dessous :
=TEXTE(B10,"# # # # # # # #")
Presse Entrer , puis faites glisser la poignée de remplissage automatique sur d'autres cellules.
Si la longueur des nombres est 5, la formule sera modifiée comme =TEXTE(B10,"# # # # #").
Si vous souhaitez ajouter un ou plusieurs caractères entre chaque mot, en supposant que l'espace entre le premier, le deuxième et le dernier nom, comme illustré ci-dessous, vous pouvez utiliser une fonction définie par l'utilisateur.
1. presse autre + F11 touches pour activer Microsoft Visual Basic pour applications fenêtre.
2. Dans la fenêtre contextuelle, cliquez sur insérer > Module pour insérer un nouveau module vierge.
3. Copiez et collez le code VBA ci-dessous dans le module.
Ajouter des caractères entre les mots
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. Enregistrez le code et fermez la fenêtre pour revenir à la feuille de calcul, puis tapez la formule ci-dessous dans une cellule dans laquelle vous souhaitez placer le résultat de l'ajout.
=Ajouter des personnages(B3)
B3 est la cellule contenant la chaîne que vous souhaitez ajouter de l'espace entre les mots.
5. presse Entrer pour obtenir le résultat, faites glisser la poignée de remplissage automatique sur les cellules pour obtenir d'autres résultats selon vos besoins.
| Attention: dans le code VBA, vous pouvez modifier le "" à d'autres textes selon vos besoins, en supposant que vous le changez en "-" ajouter - entre les mots. |
Pour ajouter des caractères spécifiques entre les textes et les nombres comme illustré ci-dessous, voici deux méthodes.
9.1 Ajouter un caractère entre les textes et les nombres avec une formule
Si les textes sont devant les nombres, la formule générique est
TRIM(REPLACE(chaîne,MIN(TROUVE({1,2,3,4,5,6,7,8,9,0},chaîne&"1234567890")),0,"-"))
En utilisant le cas ci-dessus comme exemple, veuillez utiliser la formule comme ceci :
=TRIM(REPLACE(B4,MIN(FIND({1,2,3,4,5,6,7,8,9,0},B4&"1234567890")),0,"-"))
Presse Entrer pour obtenir le premier résultat, puis faites glisser la poignée de remplissage automatique sur d'autres cellules pour remplir cette formule.
Si les nombres sont au début des textes, la formule générique est
GAUCHE(chaîne,SOMME(LEN(chaîne)-LEN(SUBSTITUT(chaîne,{0,1,2,3,4,5,6,7,8,9},""))))&"."&DROITE (chaîne,LEN(B13)- SOMME(LEN(chaîne)-LEN(SUBSTITUT(chaîne, {0,1,2,3,4,5,6,7,8,9}, ""))))
En utilisant le cas ci-dessus comme exemple, veuillez utiliser la formule comme suit :
=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}, ""))))
Presse Entrer pour obtenir le premier résultat, puis faites glisser la poignée de remplissage automatique sur d'autres cellules pour remplir cette formule.
9.2 Ajouter du caractère entre les textes et les nombres avec Flash Fill
Si vous utilisez Excel 2013 ou des versions ultérieures, le Remplissage Flash peut également vous aider à ajouter des caractères entre les textes et les nombres.
À côté des données d'origine, saisissez les nouvelles données auxquelles ont été ajoutés le(s) caractère(s) entre les textes et les nombres. Appuyez ensuite sur la touche Entrée pour passer à la cellule suivante du premier résultat.
Et appuyez sur Ctrl + E touches pour activer Flash Fill.
Extension
Ce didacticiel répertorie également certains scénarios d'ajout de texte que nous pouvons rencontrer dans notre vie quotidienne ou professionnelle.
Parfois, vous devez ajouter des séparateurs tels que des tirets à une série de numéros de téléphone ou de numéros sociaux, comme illustré ci-dessous. La saisie manuelle sera tentée si des centaines de chiffres doivent être ajoutés. Dans cette partie, il présente trois astuces pour gérer ce travail rapidement.
10.1 Ajouter des tirets aux nombres par formule
Voici deux formules permettant d'ajouter rapidement des tirets aux nombres. Pour ajouter des tirets après les troisième et sixième nombres dans la cellule B3, veuillez utiliser la formule comme suit :
Fonction REMPLACER
=REMPLACER(REMPLACER(B3,4,0,"-"),8,0,"-")
Fonction TEXT
=TEXTE(B3,"???-???-???")
Presse Entrer pour obtenir le premier résultat, puis faites glisser la poignée de remplissage automatique sur les autres cellules.
|
Attention: Vous pouvez modifier les arguments des formules ci-dessus selon vos besoins. Supposons ajouter "-" après les deuxième, quatrième et sixième chiffres, vous pouvez utiliser les formules comme : =REMPLACER(REMPLACER(REMPLACER(B3,3,0,"-"),6,0,"-"),9,0,"-") =TEXTE(B3,"??-??-??-???") |
10.2 Ajouter des tirets aux nombres par Format Cells
Pour ajouter directement des tirets aux nombres dans les cellules d'origine, vous pouvez utiliser le Format de cellule fonction.
1. Sélectionnez les numéros auxquels vous souhaitez ajouter des tirets, puis appuyez sur Ctrl + 1 touches pour activer Format de cellule dialogue.
2. dans le Format de cellule dialogue, sous le Numéro onglet, choisissez Applications du Catégories .
3. Puis à droite Catégorie rubrique, tapez ###-###-### dans la zone de texte, cliquez sur OK.
Maintenant, les numéros sélectionnés ont été ajoutés des tirets.
10.3 Ajouter des tirets au numéro de téléphone en ajoutant du texte de Kutools for Excel
Si vous avez Kutools pour Excel installé dans Excel, le Ajouter du texte fonctionnalité peut également vous rendre service.
1. Sélectionnez les cellules qui contiennent les nombres à ajouter des tirets, cliquez sur Kutools > Texte > Ajouter du texte.
2. dans le Ajouter du texte boîte de dialogue, tapez le tiret ou un autre séparateur dans Texte zone de texte, choisissez Spécifier , puis tapez les positions après lesquelles vous souhaitez ajouter des tirets et séparez-les par des virgules dans la zone de texte ci-dessous.
3. Cliquez Ok ou Postulez. Ensuite, les numéros sélectionnés ont été ajoutés des tirets.
Téléchargez gratuitement Ajouter du texte pour un essai de 30 jours.
Supposons qu'il existe une liste de nombres de différentes longueurs, vous souhaitez ajouter des zéros non significatifs pour les rendre de la même longueur et avoir l'air soigné comme illustré ci-dessous. Voici deux méthodes fournies pour vous permettre de gérer le travail.
11.1 Ajouter des zéros non significatifs aux cellules avec la fonction Formater les cellules
Vous pouvez utiliser le Format de cellule fonction pour définir un format personnalisé pour fixer la longueur de la cellule en ajoutant des zéros non significatifs.
1. Sélectionnez les cellules auxquelles vous souhaitez ajouter des zéros non significatifs, puis appuyez sur Ctrl + 1 clés pour activer Format de cellule dialogue.
2. dans le Format de cellule dialogue, sous le Numéro onglet, choisissez Applications du Catégories .
3. Puis à droite Catégorie rubrique, tapez 0000 dans la zone de texte (pour fixer la longueur du nombre à 4 chiffres, tapez 0000, vous pouvez le changer en une autre longueur, telle que 5 chiffres, tapez 00000), cliquez sur OK.
Ensuite, les nombres ont été ajoutés des zéros non significatifs et de la même longueur.
| Attention: si la longueur des données d'origine dépasse la longueur définie, il affichera les données d'origine sans zéros non significatifs. |
11.2 Ajouter des zéros non significatifs aux cellules avec la formule
Si vous ne souhaitez pas modifier les données d'origine, vous pouvez utiliser une formule pour ajouter des zéros non significatifs aux nombres situés à d'autres emplacements.
Voici trois fonctions qui peuvent vous aider.
Fonction TEXTE Formule1
TEXTE(nombre,"00…")
Fonction Formula2 DROITE
DROITE("00…"&nombre,longueur)
Fonction BASE Formule3
BASE(nombre,10,longueur)
Le nombre de zéros dans la formule est égal à la longueur du nombre.
Ici, vous définissez 4 chiffres comme longueur des nombres, veuillez utiliser les formules ci-dessous :
= TEXTE (B10, "0000")
=DROITE("0000"&B10,4)
=BASE(B10,10,4)
Presse Entrer et faites glisser la poignée de remplissage automatique vers le bas.
| Attention: si la longueur des données d'origine dépasse la longueur définie, il affichera les données d'origine sans zéros non significatifs. |
Si vous souhaitez ajouter des zéros à droite aux nombres pour créer une longueur fixe, en supposant que l'on ajoute des zéros à droite aux nombres dans la cellule B3:B7 pour les faire en longueur à 5 chiffres comme illustré ci-dessous, vous pouvez appliquer une formule pour gérer le travail .
Fonction REPT
nombre&REPT("0",longueur-LEN(nombre))
Dans la formule, l'argument "nombre" représente le nombre ou une référence de cellule que vous souhaitez ajouter des zéros à droite, et l'argument "longueur" est la longueur fixe que vous souhaitez.
Veuillez utiliser la formule comme ceci:
=B3&REPT("0",5-LEN(B3))
Presse Entrer et faites glisser la poignée de remplissage automatique vers les cellules qui ont besoin de cette formule.
Si vous souhaitez exporter ou copier des données d'une feuille de calcul vers un bloc-notes, les textes peuvent être placés de manière désordonnée car les valeurs des cellules sont de longueurs différentes.
Pour résoudre ce problème, la fonction REPT peut vous rendre service.
GAUCHE(texte & REPT(" ", longueur_max), longueur_max)
Dans la formule, l'argument "text" est le texte ou la référence de cellule que vous souhaitez ajouter un espace de fin, et l'argument "max_length" est la longueur fixe que vous souhaitez faire les textes, qui doit être plus long ou égal à la longueur maximale des textes utilisés.
Pour ajouter un espace de fin aux textes de la cellule B3:C6, veuillez utiliser la formule ci-dessous :
=GAUCHE(B3 & REPT(" ",10),10)
Presse Entrer , puis faites glisser la poignée de remplissage automatique sur toutes les cellules nécessaires à cette formule.
Dans ce cas, comme le texte le plus long utilisé est de 9 caractères, nous en utilisons 10 ici.
Parfois, vous devez ajouter des guillemets ou des crochets autour du texte ou des nombres dans les cellules comme illustré ci-dessous, à l'exception de la saisie manuelle un par un, voici deux méthodes dans Excel pour le résoudre rapidement.
14.1 Ajouter des guillemets/crochets autour par Format Cells
Si vous souhaitez ajouter des guillemets autour du texte dans les données d'origine, la fonctionnalité Format de cellule d'Excel peut vous aider.
1. Sélectionnez les cellules que vous souhaitez ajouter des guillemets, appuyez sur Ctrl + 1 touches pour activer Format de cellule dialogue.
2. dans le Format de cellule dialogue, sous Numéro onglet, choisissez Applications de la liste des Catégories, puis allez dans la section de droite pour taper "''" @ "''" into the Catégorie zone de texte. Cliquez sur OK.
Le texte dans les cellules sélectionnées a été ajouté avec des guillemets.
Si vous souhaitez ajouter des crochets autour du texte, dans la zone de texte Type de la boîte de dialogue Format de cellule, utilisez (@).
14.2 Ajouter des guillemets/crochets autour des formules
Les formules peuvent également être utilisées pour ajouter des guillemets au texte dans d'autres cellules.
Utilisation de & connecteur
""""&texte&""""
Or
char(34)&texte&char(34)
Dans ce cas, texte dans la cellule B11, veuillez utiliser les formules comme celles-ci :
=""""&B11&""""
Or
=car(34)&B11&car(34)
Presse Entrer et faites glisser la poignée de remplissage automatique vers le bas pour remplir les cellules.
Si vous souhaitez ajouter des crochets autour du texte, utilisez la formule comme "("&texte&")".
14.3 Ajouter des guillemets/crochets autour par VBA
Si vous avez besoin de code VBA pour résoudre ce travail, voici un VBA.
1. presse autre + F11 touches pour activer Microsoft Visual Basic pour applications fenêtre.
2. Ensuite, dans la fenêtre qui s'ouvre, cliquez sur insérer > Module pour insérer un nouveau module vierge.
3. Copiez et collez le code VBA ci-dessous dans le nouveau module.
VBA : ajouter des guillemets autour des textes
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| Attention: dans le script de code, """" & Rng.Valeur & """" indique d'ajouter "" autour des textes, si vous voulez ajouter des crochets () autour du texte, changez-le en "(" & Valeur Rng. & ")". |
4. presse F5 touche ou cliquez course bouton (dans la fenêtre de contrôle qui apparaît maintenant) dans la fenêtre pour activer le code VBA. Une boîte de dialogue apparaît pour sélectionner des cellules pour ajouter des guillemets.
5. Cliquez OK. Maintenant, les guillemets ont été ajoutés autour des textes dans les cellules sélectionnées.
Parfois, pour aider les autres utilisateurs à mieux comprendre les données, vous devrez peut-être ajouter du texte dans les résultats de la formule, comme illustré ci-dessous. Pour inclure du texte dans la formule, vous pouvez utiliser les guillemets entourant le texte et ajouter le connecteur & pour combiner le texte et la formule ensemble.
Cas 1 ajouter du texte avant la formule
"Aujourd'hui est "&TEXT(TODAY(),"dddd, mmmm dd.")
Cas 2 ajouter du texte après formule
TEXT(NOW(),"HH:MM:SS")&" est l'heure actuelle."
Cas 3 ajouter des textes des deux côtés de la formule
"Aujourd'hui vendu "&data&" kg."
Plus de didacticiels Excel : |
|
Combinez plusieurs classeurs/feuilles de travail en un seul Fractionner les cellules de texte, de nombre et de date (séparer en plusieurs colonnes) Combinez le contenu de plusieurs cellules sans perdre de données dans Excel Comparez deux colonnes pour les correspondances et les différences dans Excel |
Les meilleurs outils de productivité de bureau
Kutools for Excel résout la plupart de vos problèmes et augmente votre productivité de 80%
- Barre Super Formula (modifiez facilement plusieurs lignes de texte et de formule); Disposition de lecture (lire et modifier facilement un grand nombre de cellules); Coller dans la plage filtréeplus
- Fusionner les cellules / lignes / colonnes et conservation des données; Contenu des cellules divisées; Combiner les lignes en double et la somme / moyenne... Empêcher les cellules en double; Comparer les gammesplus
- Sélectionnez Dupliquer ou Unique Lignes; Sélectionnez les lignes vides (toutes les cellules sont vides); Super Find et Fuzzy Find dans de nombreux classeurs; Sélection aléatoire ...
- Copie exacte Plusieurs cellules sans changer la référence de formule; Créer automatiquement des références à plusieurs feuilles; Insérer des puces, Cases à cocher et plus encore ...
- Formules favorites et insérer rapidement, Plages, graphiques et images; Crypter les cellules avec mot de passe; Créer une liste de diffusion et envoyer des e-mails ...
- Extrait du texte, Ajouter du texte, Supprimer par position, Supprimer l'espace; Créer et imprimer des sous-totaux de pagination; Conversion entre le contenu et les commentaires des cellulesplus
- Super filtre (enregistrer et appliquer des schémas de filtrage à d'autres feuilles); Tri avancé par mois / semaine / jour, fréquence et plus; Filtre spécial par gras, italique ...
- Combiner des classeurs et des feuilles de travail; Fusionner les tableaux en fonction des colonnes clés; Diviser les données en plusieurs feuilles; Conversion par lots xls, xlsx et PDFplus
- Regroupement du tableau croisé dynamique par numéro de semaine, jour de la semaine et plus encore ... Afficher les cellules déverrouillées et verrouillées par différentes couleurs; Mettre en évidence les cellules qui ont une formule / un nomplus
- Activer l'édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
- Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
- Augmente votre productivité de 50% et réduit des centaines de clics de souris chaque jour!
