Excel ajoute Diviser par texte et nombre à la position spécifiée dans la cellule

Dans Excel, Ajouter du texte ou des chiffres dans des cellules est une tâche très courante, comme ajouter un espace entre des noms, un préfixe ou un suffixe aux cellules, ou encore des tirets aux numéros de sécurité sociale. Ce tutoriel présente presque tous les scénarios d’ajout dans Excel et fournit les méthodes correspondantes.

Remarque

Dans ce tutoriel, j’ai préparé des exemples pour illustrer les méthodes. Vous pouvez adapter les références selon vos besoins lorsque vous utilisez le code VBA ou les formules ci-dessous, ou télécharger directement les exemples pour tester ces méthodes.

Cliquez pour télécharger le fichier exemple

Cette section présente différentes méthodes permettant d’ajouter des caractères au début de chaque cellule, comme illustré dans la capture d’écran ci-dessous :

1,1 Ajouter au début des cellules à l’aide d’une formule

Vous pouvez choisir l’une des trois formules suivantes :

Formule 1 : opérateur de concaténation « & »

Joignez plusieurs textes à l’aide du caractère esperluette « & ».

«texte»&cellule

Formule 2 : fonction CONCATENER

La fonction CONCATENER permet de fusionner plusieurs textes en un seul.

CONCATENER(«texte»;cellule)

Formule 3 : fonction CONCAT

Il s’agit d’une fonctionnalité exclusive disponible uniquement dans Excel 2019, Office 365 et Excel en ligne.

CONCAT(«texte»;cellule)

Dans les formules génériques : « cellule » désigne la référence de la cellule contenant le texte auquel vous souhaitez ajouter un préfixe, et « texte » correspond au préfixe à ajouter à cette cellule.

En reprenant l’exemple ci-dessus, vous pouvez utiliser les formules suivantes dans Barre de formule :

« & »

=$E$3&B3ou =«Iphone»&B3

Fonction CONCATENER

=CONCATENER($E$3;B3)ou =CONCATENATE(«Iphone»,B3)

Fonction CONCAT

=CONCAT($E$3;B3)ou =CONCAT(«Iphone»,B3)

Appuyez sur la touche Entrée pour obtenir le résultat, puis faites glisser la poignée de recopie vers le bas jusqu’à « Ajouter du texte » afin d’appliquer la formule à chaque cellule de la plage B3:B6.

1,2 Ajouter au début des cellules à l’aide de la Remplissage instantané

Si vous utilisez Excel 2013 ou une version ultérieure, la puissante fonctionnalité Remplissage instantané remplit automatiquement les cellules en se basant sur celles situées au-dessus.

Sélectionnez une cellule adjacente à la première donnée d’origine et saisissez manuellement la première donnée avec le texte de préfixe, comme illustré ci-dessous :

Ensuite, dans la cellule située en dessous, saisissez la deuxième donnée en incluant le texte de préfixe. Pendant la saisie, une liste en gris apparaît ; appuyez sur la touche Entrée pour permettre au Remplissage instantané de compléter automatiquement les données.

Si la liste d’aperçu ne s’affiche pas, rendez-vous dans l’onglet Accueil, puis cliquez sur Remplissage > Remplissage instantané pour l’exécuter manuellement.

1,3 Ajouter au début des cellules à l’aide d’un code VBA

Si vous maîtrisez le code VBA, voici une macro illustrant comment 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 de préfixe, puis appuyez sur les touches Alt + F11 pour ouvrir la fenêtre Microsoft Visual Basic pour Applications.

2. Dans la fenêtre qui s’ouvre, cliquez sur Insertion > 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

4. Appuyez sur la touche F5 ou cliquez sur le bouton Exécuter dans la fenêtre pour exécuter le code VBA.

Toutes les cellules sélectionnées affichent désormais « iPhone » en début de texte.

Outre l’ajout de texte au début des cellules, il est également courant d’ajouter du texte à la fin des cellules. Prenons ici l’exemple de l’ajout d’une unité à la fin de chaque cellule, tel qu’illustré dans la capture d’écran ci-dessous :

2,1 Ajouter à la fin des cellules à l’aide d’une formule

Vous pouvez choisir l’une des trois formules suivantes :

Formule 1 : opérateur de concaténation « & »

Joignez plusieurs textes à l’aide du caractère esperluette « & ».

Cellule&«texte»

Formule 2 : fonction CONCATENER

La fonction CONCATENER permet de fusionner plusieurs textes en un seul.

CONCATENER(cellule,«texte»)

Formule 3 : fonction CONCAT

Cette fonctionnalité inédite est disponible exclusivement dans Excel 2019, Office 365 et Excel en ligne.

CONCAT(cellule,«texte»)

Dans ces formules génériques, « cellule » désigne la référence de la cellule contenant le texte auquel vous souhaitez ajouter un suffixe, et « texte » correspond au suffixe à ajouter.

En reprenant l’exemple ci-dessus, vous pouvez utiliser les formules suivantes dans Barre de formule :

« & »

=B3&$E$3ou =B3&«Kg»

Fonction CONCATENER

=CONCATENER(B3; $E$3)ou =CONCATENATE(B3, «Kg»)

Fonction CONCAT

=CONCAT(B3; $E$3)ou =CONCAT(B3, «Kg»)

Appuyez sur la touche Entrée pour obtenir le résultat, puis faites glisser la poignée de recopie vers le bas jusqu’à ajouter du texte à la fin de chaque cellule de la plage B3:B6.

2,2 Ajouter à la fin des cellules à l’aide de la Remplissage instantané

Si vous utilisez Excel 2013 ou une version ultérieure, la puissante fonctionnalité Remplissage instantané remplit automatiquement les cellules en se basant sur celles situées au-dessus que vous avez saisies.

Sélectionnez une cellule adjacente à la première donnée d’origine et saisissez manuellement cette première donnée avec le texte de suffixe, comme illustré ci-dessous :

Ensuite, activez la cellule sous le premier résultat, puis cliquez sur Données > Remplissage instantané : toutes les cellules situées en dessous, y compris la cellule active, seront remplies automatiquement.

2,3 Ajouter à la fin des cellules à l’aide d’un code VBA

Voici un code VBA permettant d’ajouter du texte à la fin de chaque cellule d’une plage.

1. Sélectionnez une plage de cellules à laquelle vous souhaitez ajouter le suffixe, puis appuyez sur les touches Alt+F11 pour ouvrir la fenêtre Microsoft Visual Basic pour Applications.

2. Dans la fenêtre qui s’ouvre, cliquez sur Insertion>Modulepour 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

4. Appuyez sur F5 ou cliquez sur Exécuter dans la fenêtre pour exécuter le code VBA.

Le texte « kg » a désormais été ajouté à la fin de chaque cellule.

Pour la plupart d’entre vous, ajouter du texte au début ou à la fin des cellules est une opération simple, mais insérer du texte au milieu des cellules peut s’avérer plus délicat. Prenons un exemple : comme le montre la capture d’écran ci-dessous, il s’agit d’ajouter un séparateur — par exemple, les deux-points « : » — après le deuxième caractère de chaque cellule dans la plage B3:B7.

3,1 Ajouter après le n-ième caractère d’une chaîne à l’aide d’une formule

Voici quatre formules proposées pour effectuer cette tâche ; choisissez celle qui vous convient :

Formule 1 : combinaison des fonctions GAUCHE et DROITE

GAUCHE(cellule; n) & «texte» & DROITE(cellule; NBCAR(cellule) - n)

Formule 2 : combinaison des fonctions CONCATENER (ou CONCAT), DROITE et NBCAR

CONCATENER(GAUCHE(cellule; n); «texte»; DROITE(cellule; NBCAR(cellule) - n))

Ou

CONCAT(GAUCHE(cellule; n); «texte»; DROITE(cellule; NBCAR(cellule) - n))

Formule 3 : fonction REMPLACER

REMPLACER(cellule; n+1; 0; «texte»)

Dans ces formules génériques : « cellule » correspond à la référence de la cellule contenant le texte auquel vous souhaitez Ajouter du texte au milieu, « texte » correspond au texte à insérer, et « n » représente le numéro du caractère après lequel vous souhaitez Ajouter du texte.

Pour résoudre la tâche mentionnée ci-dessus, veuillez utiliser les formules suivantes :

Combinaison des fonctions GAUCHE et DROITE

=GAUCHE(B3; 2) & ":" & DROITE(B3; NBCAR(B3) - 2)ou =GAUCHE(B3; 2) & $E$3 & DROITE(B3; NBCAR(B3) - 2)

Combinaison des fonctions CONCATENER (ou CONCAT), DROITE et NBCAR

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

Fonction REMPLACER

=REPLACE(B3, 2+1, 0, ":")ou =REMPLACER(B3; 2+1; 0; $E$3)

Appuyez sur la touche Entrée pour obtenir le résultat, puis faites glisser la poignée de recopie vers le bas jusqu’à ce que vous ayez ajouté du texte après le deuxième caractère de chaque cellule de la plage B3:B7.

3,2 Ajouter après le n-ième caractère d’une chaîne à l’aide de la Remplissage instantané

Si vous utilisez Excel 2013 ou une version ultérieure, la fonctionnalité Remplissage instantané remplit automatiquement les cellules en se basant sur celles saisies au-dessus.

Sélectionnez une cellule adjacente à la première donnée d’origine et saisissez manuellement cette première donnée avec un deux-points après le deuxième caractère, comme illustré ci-dessous :

Ensuite, activez la cellule sous le premier résultat, puis appuyez sur les touches Ctrl+E pour lancer la commande Remplissage instantané. Toutes les cellules situées en dessous, y compris la cellule active, sont alors remplies automatiquement.

3,3 Ajouter après le n-ième caractère d’une chaîne à l’aide d’un code VBA

Un code VBA peut également réaliser cette tâche.

1. Appuyez sur les touches Alt + F11 pour ouvrir la fenêtre Microsoft Visual Basic pour Applications.

2. Dans la fenêtre qui s’ouvre, cliquez sur Insertion>Modulepour 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

4. Appuyez sur la touche F5 ou cliquez sur le bouton Exécuter dans la fenêtre pour activer le code VBA. Une boîte de dialogue s’ouvre alors, vous permettant de sélectionner les cellules auxquelles ajouter du texte.

5. Cliquez sur OK. Le texte « : » a désormais été ajouté après le deuxième caractère de chaque cellule dans la plage B19:B23.

Vous en avez assez des formules et du VBA ? Découvrez Kutools pour Excel, un outil puissant et pratique qui vous simplifie la tâche grâce à sa fonctionnalité Ajouter du texte.

Cette fonctionnalité Ajouter du texte permet de :

• 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 lettres majuscules
  Ajouter du texte Avant les lettres minuscules
  Ajouter du texte Avant les lettres majuscules et minuscules
  Ajouter du texte Avant les chiffres

Sélectionnez les cellules auxquelles vous souhaitez ajouter du texte, puis appliquez la fonction « 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 la boîte de dialogue Ajouter du texte, saisissez le texte que vous souhaitez ajouter aux cellules dans la zone de texte Texte, puis cochez l’option Avant le premier caractère dans la section Position.

Cliquez sur Appliquer ou OK pour terminer l’ajout.

Pour ajouter du texte après le dernier caractère, saisissez le texte que vous souhaitez insérer dans les cellules dans la zone de texte Texte, puis cochez l’option Après le dernier caractère dans la section Position.

Cliquez sur Appliquer ou OK pour terminer l’ajout.

4,2 Ajouter du texte à la ou aux position(s) spécifiée(s)

Il arrive parfois que vous souhaitiez ajouter du texte au milieu d’une cellule ou à plusieurs positions précises. Par exemple, imaginez que vous vouliez insérer le séparateur « - » après le troisième et le septième chiffre des numéros de sécurité sociale, comme illustré dans la capture d’écran ci-dessous :

Dans la boîte de dialogue Ajouter du texte, saisissez votre texte dans la zone Texte, cochez l’option Spécifier, indiquez les positions en les séparant par des virgules dans la zone de texte, puis cliquez sur OK ou sur Appliquer.

Extension 4,3 de l’utilitaire Ajouter du texte

Outre l’insertion Ajouter du texte aux positions habituelles (début, fin et position spécifiée), l’utilitaire Ajouter du textepermet également de Ajouter du texte

• Avant les lettres majuscules
• Avant les lettres minuscules
• Avant les lettres majuscules et minuscules
• Avant les chiffres

Dans la boîte de dialogue Ajouter du texte, saisissez le texte dans la zone Texte, cochez l’option Ajouter uniquement à, puis cliquez sur le menu déroulant ci-dessous pour choisir l’opération souhaitée.

En plus de la fonctionnalité Ajouter du texte, Kutools pour Excel propose d’autres fonctions utiles 300+ qui peuvent vous faciliter la tâche dans diverses opérations Excel. Téléchargez gratuitement dès maintenant.

Cette section présente des formules permettant d’ajouter du texte avant ou après la première occurrence d’un texte spécifique dans Excel.

5,1 Ajouter du texte avant le premier texte spécifique

Par exemple, vous souhaitez Ajouter du texte « (vente) » avant la première occurrence du mot « prix » dans la cellule, comme illustré dans les captures d’écran ci-dessous :

Dans Excel, les fonctions REMPLACER et SUBSTITUER permettent de résoudre cette tâche.

Fonction REMPLACER

REMPLACER(cellule;TROUVE(«texte_recherché»;cellule);0;«texte_à_insérer»)

Fonction SUBSTITUER

SUBSTITUER(cellule;«texte_recherché»;«texte_de_remplacement»;1)

Dans ce cas, veuillez utiliser les formules ci-dessus de la manière suivante :

=REPLACE(B4,FIND(«price»,B4),0,«(sale)»)

Ou

=SUBSTITUTE(B4,«price»,«(sale)price»,1)

Les arguments de la formule dans ce cas sont les suivants :

Cellule : B4,

Texte_recherché : prix,

Texte_à_insérer : (vente),

Texte de remplacement : (vente) prix.

Appuyez sur la touche Entrée pour obtenir le premier résultat, puis faites glisser la poignée de recopie vers les autres cellules nécessitant 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é dans les captures d’écran ci-dessous :

Dans Excel, les fonctions REMPLACER et SUBSTITUER vous permettent de réaliser cette tâche en toute simplicité.

Fonction REMPLACER

REMPLACER(cellule;TROUVE(«texte_recherché»;cellule);longueur_texte_recherché;«texte_de_remplacement»)

Fonction SUBSTITUER

SUBSTITUER(cellule;«texte_recherché»;«texte_de_remplacement»)

Dans ce cas, veuillez utiliser les formules ci-dessus de la manière suivante :

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

Ou

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

Les arguments de la formule dans ce cas sont les suivants :

Cellule : B12,

Texte_recherché : :,

Texte_de_remplacement : $:

Longueur_texte_recherché : 1

Appuyez sur la touche Entrée pour obtenir le premier résultat, puis faites glisser la poignée de recopie vers les autres cellules nécessitant cette formule.

Cette partie du tutoriel vous présente trois méthodes distinctes — formule, Rechercher et remplacer, et VBA — pour insérer facilement des caractères avant ou après chaque mot d’une cellule.

Ajouter des caractères avant chaque mot

Ajouter des caractères après chaque mot

6,1 Ajouter un ou plusieurs caractères avant ou après chaque mot à l’aide d’une formule

La fonction SUBSTITUER permet d’effectuer cette opération. Voici les formules générales :

Insérer avant chaque mot

«caractère»&SUBSTITUER(chaîne; « »;« caractère»)

Insérer après chaque mot

SUBSTITUER(chaîne;« »;«caractère »)&«caractère»

Supposons que vous souhaitiez ajouter un « + » devant chaque mot de la plage B3:B6. Utilisez la formule suivante :

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

Appuyez sur la touche Entrée pour obtenir le premier résultat, puis faites glisser la poignée de recopie vers le bas afin d’appliquer la formule aux autres cellules.

Supposons que vous souhaitiez ajouter « ($) » après chaque mot de la liste B11:B14. Utilisez la formule ci-dessous :

=SUBSTITUTE(B11,« »,«($) »)&«($)»

Appuyez sur la touche Entrée pour obtenir le premier résultat, puis faites glisser la poignée de recopie vers le bas afin d’appliquer la formule aux autres cellules.

6,2 Insérer un ou plusieurs caractères avant ou après chaque mot à l’aide de Rechercher et remplacer

Si vous souhaitez insérer des caractères avant chaque mot, sauf le premier, ou après chaque mot, sauf le dernier — comme illustré dans la capture d’écran ci-dessous — l’utilitaire intégré d’Excel Rechercher et remplacer peut parfaitement accomplir cette tâche.

Insérer avant chaque mot à l’exception du premier

Insérer après chaque mot à l’exception du Dernière cellule

1. Sélectionnez les cellules auxquelles vous souhaitez ajouter des caractères avant ou après chaque mot, puis appuyez sur les touches Ctrl+H pour ouvrir la boîte de dialogue Rechercher et remplacer.

2. Saisissez un espace dans la zone Rechercher, puis tapez les nouveaux caractères, précédés ou suivis d’un espace, dans la zone Remplacer par, et cliquez enfin sur le bouton Remplacer tout.

Voici deux macros VBA qui insèrent des caractères avant ou après chaque mot. Pour exécuter ce code, suivez la procédure ci-dessous.

1. Appuyez sur les touches Alt + F11 pour ouvrir la fenêtre Microsoft Visual Basic pour Applications.

2. Dans la fenêtre qui s’ouvre, cliquez sur Insertion > Module pour insérer un nouveau module vide.

3. Copiez et collez le code VBA ci-dessous dans le module.

Insérer 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 Sub

Insérer 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 Sub

4. Appuyez sur la touche F5pour exécuter le code (ou cliquez sur le bouton)Exécuter). Une boîte de dialogue s’ouvre alors pour vous demander de sélectionner une plage de cellules contiguës. Cliquez sur OK.

5. Dans la deuxième boîte de dialogue qui s’affiche, saisissez les caractères à insérer, puis cliquez sur OK.

Les caractères seront alors ajoutés au début ou à la fin de chaque mot.

Il arrive parfois que vous souhaitiez ajouter du texte entre tous les caractères. Cette section présente deux scénarios : le premier consiste à ajouter du texte entre chaque caractère, et le second à insérer un espace entre chaque chiffre.

7,1 Ajouter du texte entre chaque caractère/chiffre à l’aide d’une fonction personnalisée

Pour insérer des caractères entre chaque caractère d’une chaîne, une fonction personnalisée (User-Defined Function) vous sera particulièrement utile.

Dans cet exemple, nous insérons un espace entre chaque caractère.

1. Appuyez sur Alt + F11 pour ouvrir la fenêtre Microsoft Visual Basic pour Applications.

2. Dans la fenêtre qui s’ouvre, cliquez sur Insertion > 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 Function

4. Enregistrez le code, fermez cette fenêtre pour revenir à la feuille de calcul, puis saisissez la formule ci-dessous dans la cellule où vous souhaitez afficher le résultat.

=AddText(B3)

B3 est la cellule contenant la chaîne à laquelle vous souhaitez insérer des espaces entre chaque caractère.

5. Appuyez sur la touche Entrée pour obtenir le résultat, puis faites glisser la poignée de recopie vers les autres cellules selon vos besoins.

7,2 Insérer un espace entre chaque chiffre à l’aide d’une formule

Pour insérer un espace entre les chiffres d’une valeur numérique, utilisez la fonction TEXTE.

Supposons qu’une liste de nombres à 8 chiffres se trouve dans la plage B10:B12. Dans une cellule destinée à afficher le résultat, saisissez la formule suivante :

=TEXT(B10,«# # # # # # # #»)

Appuyez sur la touche Entrée, puis faites glisser la poignée de recopie vers les autres cellules.

Si la longueur des nombres est de 5, la formule devient =TEXT(B10,«# # # # #»).

Si vous souhaitez ajouter un ou plusieurs caractères entre chaque mot, par exemple insérer un espace entre le prénom, le deuxième prénom et Nom de famille, comme illustré dans la capture d’écran ci-dessous, vous pouvez utiliser une fonction définie par l’utilisateur.

1. Appuyez sur Alt+F11pour ouvrir la fenêtre Microsoft Visual Basic pour Applications.

2. 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 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 Function

4. Enregistrez le code, fermez cette fenêtre pour revenir à la feuille de calcul, puis saisissez la formule suivante dans la cellule où vous souhaitez afficher le résultat.

=AddCharacters(B3)

B3 est la cellule contenant la chaîne à laquelle vous souhaitez ajouter des espaces entre les mots.

5. Appuyez sur la touche Entrée pour obtenir le résultat, puis faites glisser la poignée de recopie vers les autres cellules selon vos besoins.

Pour insérer un ou plusieurs caractères spécifiques entre le texte et le nombre lors d’un fractionnement, comme le montre la capture d’écran ci-dessous, voici deux méthodes.

9,1 Ajouter un caractère entre Diviser par texte et nombre à l’aide d’une formule

Si les textes précèdent les chiffres, la formule générique est la suivante :

SUPPRESPACE(REMPLACER(chaîne;MIN(TROUVE({1,2,3,4,5,6,7,8,9,0};chaîne&"1234567890"));0;"-"))

En reprenant l’exemple ci-dessus, utilisez la formule suivante :

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

Appuyez sur la touche Entrée pour obtenir le premier résultat, puis faites glisser la poignée de recopie vers les autres cellules afin d’appliquer cette formule.

Si les chiffres précèdent les textes, la formule générique est la suivante :

GAUCHE(chaîne;SOMME(NBCAR(chaîne)-NBCAR(SUBSTITUE(chaîne;{0,1,2,3,4,5,6,7,8,9};«»))))&"."&DROITE(chaîne;NBCAR(B13)- SOMME(NBCAR(chaîne)-NBCAR(SUBSTITUE(chaîne; {0,1,2,3,4,5,6,7,8,9}; «»))))

En reprenant l’exemple ci-dessus, utilisez la formule suivante :

=LEFT(B13,SUM(LEN(B13)-LEN(SUBSTITUTE(B13,{0,1,2,3,4,5,6,7,8,9},«»))))&"."&DROITE(B13;NBCAR(B13)- SOMME(NBCAR(B13)-NBCAR(SUBSTITUE(B13; {0;1;2;3;4;5;6;7;8;9}; «»))))

Appuyez sur la touche Entrée pour obtenir le premier résultat, puis faites glisser la poignée de recopie vers les autres cellules afin d’appliquer cette formule.

9,2 Ajouter un caractère entre Diviser par texte et nombre à l’aide de la Remplissage instantané

Si vous utilisez Excel 2013 ou une version ultérieure, la fonctionnalité Remplissage instantané peut également vous aider à insérer un ou plusieurs caractères entre le texte et le nombre lors de la division.

À côté des données d’origine, saisissez les nouvelles données avec le ou les caractères déjà insérés entre Diviser par texte et nombre. Appuyez ensuite sur la touche Entrée pour passer à la cellule suivant le premier résultat.

Appuyez ensuite sur les touches Ctrl + E pour activer le Remplissage instantané.

Extension

Ce tutoriel présente également quelques scénarios courants liés à l’ajout de texte que vous pourriez rencontrer au quotidien ou dans votre travail.

Il arrive parfois qu’il faille ajouter des séparateurs, tels que des tirets, à une série de Numéro de téléphone ou de numéros de sécurité sociale, comme illustré dans la capture d’écran ci-dessous. Saisir manuellement ces tirets devient fastidieux lorsqu’il y a des centaines de numéros à traiter. Cette section présente trois astuces efficaces pour réaliser cette tâche rapidement.

10,1 Ajouter des tirets aux nombres à l’aide d’une formule

Voici deux formules pour ajouter rapidement des tirets aux nombres. Pour insérer des tirets après le troisième et le sixième chiffre de la cellule B3, utilisez l’une des formules suivantes :

Fonction REMPLACER

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

Fonction TEXTE

=TEXT(B3;«???-???-??? »)

Appuyez sur la touche Entrée pour obtenir le premier résultat, puis faites glisser la poignée de recopie vers les autres cellules.

10,2 Ajouter des tirets aux nombres à l’aide de Définir le format des cellules

Pour ajouter directement des tirets aux nombres dans les cellules d’origine, utilisez la fonctionnalité Définir le format des cellules.

1. Sélectionnez les nombres auxquels vous souhaitez ajouter des tirets, puis appuyez sur les touches Ctrl+1 pour ouvrir la boîte de dialogue Définir le format des cellules.

2. Dans la boîte de dialogue Définir le format des cellules, sous l’onglet Nombre, sélectionnez Personnalisé dans la section Catégorie.

3. Puis, dans la section Type, saisissez ###-###-### dans la zone de texte, puis cliquez sur OK.

Les nombres sélectionnés ont désormais des tirets.

10,3 Ajouter des tirets à Numéro de téléphone à l’aide de la fonction Ajouter du texte de Kutools pour Excel

Si vous avez installé Kutools pour Excel dans Excel, la fonctionnalité Ajouter du texte peut également vous être utile.

1. Sélectionnez les cellules contenant les nombres auxquels vous souhaitez ajouter des tirets, puis cliquez sur Kutools>Texte>Ajouter du texte.

2. Dans la boîte de dialogue Ajouter du texte, saisissez le tiret ou tout autre séparateur dans la zone de texte Texte, cochez l’option Spécifier, puis indiquez, dans la zone de texte située en dessous, les positions après lesquelles vous souhaitez insérer des tirets, en les séparant par des virgules.

3. Cliquez sur OK ou sur Appliquer. Les nombres sélectionnés seront alors affichés avec des tirets.

Téléchargez gratuitement Ajouter du texte pour un essai de 30 jours.

Supposons que vous disposiez d’une liste de nombres de longueurs différentes et que vous souhaitiez les aligner en ajoutant des zéros au début, afin qu’ils aient tous la même longueur, comme illustré dans la capture d’écran ci-dessous. Voici deux méthodes pour réaliser cette opération.

11,1 Ajouter des zéros en début de cellule à l’aide de la fonctionnalité Définir le format des cellules

Vous pouvez utiliser la fonctionnalité Définir le format des cellules pour créer un format personnalisé et fixer la longueur des cellules en ajoutant des zéros au début des nombres.

1. Sélectionnez les cellules auxquelles vous souhaitez ajouter des zéros au début, puis appuyez sur les touches Ctrl+1 pour ouvrir la boîte de dialogue Définir le format des cellules.

2. Dans la boîte de dialogue Définir le format des cellules, sous l’onglet Nombre, sélectionnez Personnalisédans la section Catégorie.

3. Puis, dans la section Type, saisissez 0000 dans la zone de texte (pour fixer la longueur des nombres à 4 chiffres, entrez 0000 ; vous pouvez adapter cette valeur à une autre longueur, par exemple 5 chiffres en saisissant 00000), puis cliquez sur OK.

Les nombres ont désormais des zéros en début et présentent tous la même longueur.

11,2 Ajouter des zéros en début à Formule

Si vous préférez ne pas modifier les données d’origine, utilisez une formule pour ajouter des zéros au début du nombre à un autre emplacement.

Voici trois fonctions conçues pour vous aider.

Formule 1 : fonction TEXTE

TEXTE(nombre;«00…»)

Formule 2 : fonction DROITE

DROITE(«00…»&nombrelongueur)

Formule 3 : fonction BASE

BASE(nombre;10;longueur)

Le nombre de zéros dans la formule correspond exactement à la longueur souhaitée du nombre.

Ici, vous définissez une longueur de 4 chiffres pour les nombres ; utilisez donc les formules suivantes :

=TEXT(B10,"0000")

=RIGHT("0000"&B10,4)

=BASE(B10;10;4)

Appuyez sur la touche Entrée et faites glisser la poignée de recopie vers le bas.

Si vous souhaitez ajouter des zéros à droite des nombres de la plage B3:B7 afin d’obtenir systématiquement des nombres à 5 chiffres, comme illustré ci-dessous, une formule simple vous permet d’accomplir cette tâche en un clin d’œil.

Fonction REPT

nombre&REPT("0",longueur-NBCAR(nombre))

Dans cette formule, l’argument « nombre » représente le nombre ou la référence de cellule auquel vous souhaitez ajouter des zéros à droite, et l’argument « longueur » correspond à la longueur fixe souhaitée.

Veuillez utiliser la formule suivante :

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

Appuyez sur la touche Entrée, puis faites glisser la poignée de recopie vers le bas jusqu’aux cellules nécessitant cette formule.

Si vous souhaitez exporter ou copier des données d’une feuille de calcul vers un bloc-notes, le texte risque d’apparaître désordonné car les valeurs des cellules ont des longueurs différentes. Pour obtenir un affichage soigné comme sur la capture d’écran ci-dessous, vous devez ajouter des espaces à droite.

Pour résoudre ce problème, la fonction REPT peut vous être d’une grande utilité.

GAUCHE(texte & REPT(« »,longueur_max),longueur_max)

Dans cette formule, l’argument « texte » correspond au texte ou à la référence de cellule auquel vous souhaitez ajouter des espaces à droite, et l’argument « longueur_max » correspond à la longueur fixe que vous souhaitez imposer aux textes, qui doit être supérieure ou égale à la longueur maximale des textes utilisés.

Pour ajouter des espaces à droite aux textes situés dans la plage B3:C6, veuillez utiliser la formule suivante :

=LEFT(B3 & REPT(« »,10),10)

Appuyez sur la touche Entrée, puis faites glisser la poignée de recopie vers toutes les cellules nécessitant cette formule.

Dans ce cas, comme le texte le plus long comporte 9 caractères, nous utilisons ici 10.

Il vous arrive parfois d’avoir besoin d’ajouter des guillemets ou des parenthèses autour du texte ou des nombres contenus dans des cellules, comme illustré ci-dessous. Outre la saisie manuelle, cellule par cellule, voici deux méthodes rapides dans Excel pour résoudre ce problème efficacement.

14,1 Ajouter des guillemets/parenthèses à l’aide de Définir le format des cellules

Si vous souhaitez ajouter des guillemets autour du texte dans vos données d’origine, la fonctionnalité « Définir le format des cellules » d’Excel est faite pour vous.

1. Sélectionnez les cellules auxquelles vous souhaitez ajouter des guillemets, puis appuyez sur Ctrl + 1 pour ouvrir la boîte de dialogue Définir le format des cellules.

2. Dans la boîte de dialogue Définir le format des cellules, sous l’onglet Nombre, choisissez Personnalisé dans la liste Catégorie, puis saisissez «''»@«''» dans la zone de texte Type située à droite. Cliquez sur OK.

Le texte des cellules sélectionnées est désormais entouré de guillemets.

Pour ajouter des parenthèses autour du texte, utilisez (@) dans la zone de texte Type de la boîte de dialogue Définir le format des cellules.

14,2 Ajouter des guillemets/parenthèses à l’aide de formules

Des formules peuvent également être utilisées pour ajouter des guillemets autour du texte contenu dans d’autres cellules.

Utilisation de l’opérateur &

«»«»&texte&«»«»

Ou

CAR(34)&texte&CAR(34)

Dans ce cas, le texte se trouvant dans la cellule B11, veuillez utiliser les formules suivantes :

=«»""&B11&""""

Ou

=CAR(34)&B11&CAR(34)

Appuyez sur la touche Entrée, puis faites glisser la poignée de recopie vers le bas pour remplir les cellules.

Pour ajouter des parenthèses autour de votre texte, utilisez simplement la formule suivante : «(»&texte&«)».

14,3 Ajouter des guillemets/parenthèses à l’aide de VBA

Si vous avez besoin d’un code VBA pour résoudre ce problème, en voici un.

1. Appuyez sur les touches Alt+F11pour ouvrir la fenêtre Microsoft Visual Basic pour Applications.

2. Dans la fenêtre qui s’ouvre, cliquez sur Insertion > 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

4. Appuyez sur la touche F5 ou cliquez sur le bouton Exécuter dans la fenêtre pour exécuter le code VBA. Une boîte de dialogue s’ouvre alors, vous permettant de sélectionner les cellules auxquelles ajouter des guillemets.

5. Cliquez sur OK. Des guillemets ont désormais été ajoutés autour des textes dans les cellules sélectionnées.

Parfois, afin d’aider d’autres utilisateurs à mieux comprendre les données, vous devrez Ajouter du texte dans les résultats de formule, comme illustré ci-dessous. Pour inclure du texte dans une formule, vous pouvez encadrer le texte entre guillemets et utiliser l’opérateur & pour combiner le texte et la formule.

Cas 1 Ajouter du texte avant la formule

«Aujourd’hui, nous sommes le »&TEXTE(AUJOURDHUI(),«dddd dd mmmm.»)

Cas 2 Ajouter du texte aprèsla formule

TEXTE(MAINTENANT(),«HH:MM:SS»)&« est l’heure actuelle.»

Cas 3 Ajouter du texte des deux côtés de la formule

«Aujourd’hui, nous avons vendu »&données&« kg.»

---

Les meilleurs outils de productivité bureautique

Kutools pour Excel résout la majorité de vos problèmes et augmente votre productivité de 80 %

• Super Barre de formule (modifiez facilement plusieurs lignes de texte et de formules) ; Mode Lecture (lisez et modifiez aisément un grand nombre de cellules) ; Coller dans Plage de filtrage...
• Fusionner des cellules, lignes ou colonnes tout en conservant les données ; diviser le contenu des cellules ; combiner les lignes en double avec somme ou moyenne… empêcher les doublons dans les cellules ; comparer des plages…
• Sélectionner les doublons ou les éléments uniques dans les lignes ; Sélectionner les lignes vides (toutes les cellules sont vides) ; Super Recherche et recherche floue dans de nombreux classeurs ; sélection aléatoire…
• Copiez plusieurs cellules sans modifier les références de formule ; créez automatiquement des références vers plusieurs feuilles ; insérez des puces, des cases à cocher et bien plus encore…
• Favoris et insertion rapide de formules, plages, graphiques et images ; crypter les cellules avec mot de passe ; créer une liste de diffusion et envoyer des e-mails…
• Extraire du texte, ajouter du texte, supprimer les caractères à une position donnée, supprimer les espaces ; créer et imprimer les statistiques de la page de données ; convertir le contenu des cellules en commentaires et inversement...
• Super Filtre (enregistrez et appliquez des schémas de filtre à d’autres feuilles) ; Tri avancé par mois, semaine, jour, fréquence, etc. ; Filtre spécial par gras, italique…
• Combinez plusieurs classeurs et feuilles de calcul en un seul ; fusionnez des tableaux à partir d’une colonne clé ; répartissez vos données dans plusieurs feuilles ; convertissez en lot vos fichiers xls, xlsx et PDF…
• Regroupement dans un tableau croisé dynamique par numéro de semaine, jour de la semaine et bien plus encore…Afficher les cellules non verrouillées et verrouiller la sélection avec des couleurs distinctes ;Mettre en surbrillance les cellules contenant une formule ou un nom défini…

En savoir plus...Essai gratuit de 30 jours avec toutes les fonctionnalités…Acheter... 

---

Office Tab – apporte une interface à onglets à Office et rend votre travail bien plus facile

• Activez 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 vous évite des centaines de clics de souris chaque jour !

En savoir plus...Essai gratuit de 30 jours avec toutes les fonctionnalités...Acheter...