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.

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 pour chaque c In Selection If c.Value <> "" Then c.Value = "Iphone" & c.Value Next End Sub

4. presse F5 touche ou cliquez course bouton 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.

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 pour chaque c In Selection If c.Value <> "" Then c.Value = c.Value & "Kg" Next End Sub

4. presse F5 touche ou cliquez course bouton 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.

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.

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

4. presse F5 touche ou cliquez course bouton 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.

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 et 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 et 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.

À l'exception de la fonctionnalité Ajouter du texte, Kutools for Excel possède plus de 300 autres fonctionnalités utiles qui peuvent vous rendre service dans différents travaux Excel, téléchargez le téléchargement gratuit maintenant.

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 .

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) Si xRg n'est rien, alors quittez Sub xInStr = Application.InputBox("Tapez les caractères que vous souhaitez ajouter :", "Kutools for Excel", " ", , , , , , 2) Si StrPtr(xInStr) = 0, alors quittez la sous-application.ScreenUpdating = False pour chaque xCell dans xRg xArr = Split(xCell.Text, " ") xValue = "" Pour chaque xStr dans 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

Ajouter 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) Si xRg n'est rien, alors quittez Sub xInStr = Application.InputBox("Tapez les caractères que vous souhaitez ajouter :", "Kutools for Excel", " ", , , , , , 2) Si StrPtr(xInStr) = 0, alors quittez la sous-application.ScreenUpdating = False pour chaque xCell dans xRg xArr = Split(xCell.Text, " ") xValue = "" Pour chaque xStr dans 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. presse F5 pour exécuter le code (ou vous pouvez cliquer sur course bouton   ), 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

Fonction 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. 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.

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.

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

Fonction 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 Et xAsc <= 90 Then xOut = xOut & " " & VBA.Mid(pValue, i, 1) Sinon xOut = xOut & VBA.Mid(pValue, i, 1) End If Next AddCharacters = xOut End Function

4. 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.

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.

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 CUSTOMISER du Catégories .

3. Puis à droite Type 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 CUSTOMISER du Catégories .

3. Puis à droite Type 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.

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.

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 CUSTOMISER de la liste des Catégories, puis allez dans la section de droite pour taper "''" @ "''" into the Type 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) Pour chaque Rng In WorkRng Rng.Value = """" & Rng.Value & """" Next End Sub

4. presse F5 touche ou cliquez course bouton 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."

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ée...
• 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 gammes...
• 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 cellules...
• 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 PDF...
• 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 nom...

Lire la suite... Essai gratuit de 30 jours ... Achat... 

---

Office Tab - apporte une interface à onglets à Office et simplifie considérablement votre travail

• 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!

Lire la suite... Essai gratuit de 30 jours ... Achat...