Substituer le N-ième match d’un délimiteur depuis la fin d’une chaîne dans Google Sheets

Vous vous demandez s’il est possible d’utiliser la fonction SUBSTITUTE pour remplacer le N-ième match d’un délimiteur depuis la fin d’une chaîne dans Google Sheets ? Je vous apporte la réponse (sous forme de formule). Cette formule fonctionnera également dans Excel, car elle ne nécessite pas l’utilisation d’expressions régulières.

Mais pour comprendre cela, vous devez d’abord bien comprendre tous les arguments (il y en a trois) dans la syntaxe de cette fonction.

Comme vous pouvez le voir ci-dessous, la syntaxe de la fonction SUBSTITUTE contient un argument facultatif appelé « occurrence_number », qui est placé entre crochets car il est optionnel.

SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])

En général, la plupart d’entre nous négligent cet argument facultatif de la fonction SUBSTITUTE, n’est-ce pas ?

Cet argument, c’est-à-dire « occurrence_number », nous permet en réalité de remplacer la n-ième occurrence depuis le début de la chaîne, et non depuis la fin. Voyez l’exemple suivant.

La cellule A2 contient la valeur « Janvier / Février / Mars / Avril / Mai / Juin / Juillet / Août / Septembre / Octobre / Novembre / Décembre », qui est évidemment le nom des 12 mois séparés par des barres obliques.

La formule SUBSTITUTE suivante remplacerait la cinquième occurrence de la barre oblique depuis le début de la chaîne par un astérisque.

=substitute(A2,"/","*",5)

Le résultat serait « Janvier / Février / Mars / Avril / Mai * Juin / Juillet / Août / Septembre / Octobre / Novembre / Décembre ».

Comme vous pouvez le voir dans le résultat, le cinquième délimiteur (barre oblique) a été remplacé par le caractère astérisque.

Et que se passe-t-il si vous souhaitez remplacer le cinquième match (ou la cinquième occurrence) de la barre oblique par un astérisque depuis la fin de la chaîne précédente ?

Substituer le N-ième match/occurrence depuis la fin d’une chaîne – Logique et formule

Dans cet article, je vous montre comment substituer le N-ième match depuis la fin d’une chaîne dans Google Sheets. Cette astuce (ou contournement) est très simple à suivre.

La logique ici est assez simple à comprendre. Je vais d’abord expliquer la logique, car il est important de la comprendre, puis nous passerons à la formule.

Partie Logique

Pour substituer la n-ième occurrence depuis la fin d’une chaîne dans Google Sheets, vous devez d’abord compter le nombre total d’occurrences de ce caractère spécifique, également appelé délimiteur.

Ensuite, soustrayez le nombre d’occurrences depuis la fin/la dernière position de la chaîne de ce total. À ce total, ajoutez un. Pour plus de clarté, voici la version générique de la formule :

N-ième_match_du_délimiteur_depuis_la_fin_de_la_chaîne = Nombre_de_délimiteur - n + 1

C’est la partie logique. Maintenant, passons à la mise en œuvre de la formule, en suivant la logique ci-dessus en quelques étapes.

Partie Formule

Supposons que le nombre total d’occurrences de la barre oblique soit de 11 (il y a 11 barres obliques dans la chaîne de la cellule A2). Bien sûr, vous obtiendrez la formule pour obtenir ce nombre un peu plus bas.

Ensuite, par exemple, si vous souhaitez remplacer la n-ième occurrence depuis la fin de cette chaîne dans Google Sheets, où « n » est égal à 3, utilisez 11-2 (11-3+1), c’est-à-dire 9, comme valeur pour l’argument « occurrence_number ».

Cela signifie que l’argument « occurrence_number » dans la formule SUBSTITUTE serait égal à 9.

Comment compter le nombre d’occurrences des barres obliques dans la chaîne de la cellule A2 ?

Il y a deux formules pour cela. Vous pouvez choisir l’une d’entre elles.

Formule n° 1 :

=len(regexreplace(A2,"[^/]",""))

Formule n° 2 :

=LEN(A2)- LEN(SUBSTITUTE(A2,"/",""))

Comme pour la valeur dans la cellule A2, les deux formules renvoient 11, qui est le nombre total d’occurrences de la barre oblique dans la chaîne.

Je vais poursuivre avec la formule n° 2. Cela signifie que j’utiliserai =LEN(A2)- LEN(SUBSTITUTE(A2,"/",""))-2 comme valeur pour l’argument « occurrence_number » dans SUBSTITUTE.

Voici la formule pour substituer le n-ième (ici le 3e) match depuis la fin de la chaîne dans la cellule A2 de Google Sheets.

=substitute(A2,"/","*",LEN(A2)- LEN(SUBSTITUTE(A2,"/",""))-2)

Compter le délimiteur depuis la fin d’une chaîne et le diviser

Vous avez appris comment substituer le n-ième match d’un caractère/délimiteur depuis la fin/dernière partie d’une chaîne. À quoi cela peut-il servir dans la vie réelle ?

Vous avez une liste dans une colonne qui contient des mots séparés par un ou plusieurs caractères spécifiques. Lorsque vous souhaitez extraire les derniers « n » mots, vous pouvez utiliser la méthode décrite ci-dessus.

Tout d’abord, nous allons remplacer la n-ième occurrence du délimiteur depuis la fin de la chaîne par un autre délimiteur, comme expliqué ci-dessus. Ensuite, nous allons diviser à partir de la n-ième position depuis la fin.

Par exemple, en enveloppant la formule ci-dessus avec SPLIT comme suit, nous allons diviser la chaîne de la cellule A2 en deux parties.

=split(substitute(A2,"/","*",LEN(A2)- LEN(SUBSTITUTE(A2,"/",""))-2),"*")

Si vous souhaitez n’obtenir qu’une seule des parties, vous pouvez utiliser la fonction INDEX.

Première partie :

=index(split(substitute(A2,"/","*",LEN(A2)- LEN(SUBSTITUTE(A2,"/",""))-2),"*"),0,1)

Deuxième partie :

=index(split(substitute(A2,"/","*",LEN(A2)- LEN(SUBSTITUTE(A2,"/",""))-2),"*"),0,2)

Conclusion

Si votre but est de substituer le n-ième match d’un délimiteur depuis la fin d’une chaîne dans Google Sheets afin d’extraire une partie de la chaîne, comme expliqué ci-dessus, c’est-à-dire compter un délimiteur depuis la fin de la chaîne, le remplacer, puis diviser et extraire la portion requise, il existe une autre formule simple.

Je partagerai cette formule avec vous dans mon prochain tutoriel.

Avant de conclure, une autre chose. Nous pouvons également utiliser la formule SUBSTITUTE dans une plage/matrice. Pour cela, utilisez la fonction ArrayFormula.

=ArrayFormula(substitute(A2:A4,"/","*",LEN(A2:A4)- LEN(SUBSTITUTE(A2:A4,"/",""))-2))

Voilà, c’est tout. Profitez-en !

Ressources :

• Google Sheets Regexreplace Function How to and Examples
• Replace Function in Google Sheets – Formula Examples
• Replace Multiple Comma Separated Values in Google Sheets
• Regex to Replace the Last Occurrence of a Character in Google Sheets
• Extract, Replace, Match Nth Occurrence of a String or Number in Google Sheets
• RegexReplace to Wrap Numbers in Brackets and Its Use in Query