Il est assez simple de construire une expression régulière (RE2) pour extraire les nombres négatifs de différentes chaînes de texte dans Google Sheets. Parmi les trois fonctions connexes, nous pouvons utiliser soit la fonction REGEXTRACT, soit la fonction REGEXREPLACE. Ici, la troisième fonction, c’est-à-dire REGEXMATCH, n’a pas sa place.
Je pense que vous avez peut-être les interrogations suivantes à poser. Permettez-moi de clarifier cela d’abord.
-
Pouvons-nous utiliser les nombres négatifs ou positifs extraits dans d’autres calculs ?
Nous le pouvons. Pour cela, nous devons utiliser les fonctions VALUE pour convertir les nombres négatifs extraits au format texte en format numérique. -
Et qu’en est-il des nombres (positifs ou négatifs) multiples dans une chaîne de texte ?
Nous pouvons extraire tous ces nombres dans plusieurs colonnes de la ligne correspondante. -
Fournissez-vous une formule matricielle pour obtenir des nombres négatifs ou positifs à partir d’une chaîne ?
Absolument ! Nous pouvons coder une formule matricielle en utilisant la fonction ArrayFormula et la plage ouverte/fermée avec REGEXREPLACE.
Extraire tous les nombres indépendamment de leur signe des chaînes de texte
J’ai différentes chaînes de texte d’exemple dans la plage A2:A16. Voyons d’abord comment utiliser une formule non matricielle pour atteindre notre objectif.
Tout d’abord, sélectionnez A2:A16 et allez dans Format > Nombre > Texte brut.
Dans la cellule B2, insérez la formule combo suivante et faites-la glisser vers le bas autant que vous le souhaitez pour extraire tous les nombres positifs ou négatifs.
=value(SI(na(REGEXEXTRACT(A2,"-?d*.?d+")),))
Depuis nos valeurs sont dans A2:A16, j’ai fait glisser cette formule jusqu’à B16.
Les trois fonctions utilisées dans la formule ci-dessus ont les objectifs suivants :
- REGEXEXTRACT – Extraire les nombres indépendamment de leur signe à partir du texte fourni.
- IFNA – Renvoyer une cellule vide si la formule précédente renvoie #N/A !
- VALUE – Convertir les nombres formatés en texte (résultat) en nombres.
Explication de la formule (REGEXEXTRACT)
Voici les explications pour l’expression régulière utilisée, qui est -?d*.?d+.
-? – Correspond au caractère tiret entre zéro et une fois.
d* – Correspond au chiffre 0-9 entre zéro et un nombre illimité.
Dans les deux premières parties, le point d’interrogation et l’astérisque sont les quantificateurs. Dans les deux suivantes, ils sont le point d’interrogation et le signe plus.
.? – Correspond au caractère point entre zéro et une fois.
d+ – Correspond au chiffre 0-9 entre une et un nombre illimité.
Nous pouvons convertir la formule ci-dessus en une formule matricielle.
Nous pouvons utiliser les formules ci-dessous pour extraire des nombres négatifs ou positifs à partir de différentes chaînes de texte dans Google Sheets.
Vous pouvez utiliser les formules suivantes dans une colonne vide, idéalement en B2 après avoir vidé B2:B16 ou B2:B.
Plage fermée :
=ArrayFormula(value(SI(na(REGEXEXTRACT(A2:A16,"-?d*.?d+")),)))
Plage ouverte :
=ArrayFormula(SI(A2:A="",,(value(SI(na(REGEXEXTRACT(A2:A,"-?d*.?d+")),)))))
Comment obtenir plusieurs nombres négatifs ou positifs à partir d’un seul texte
Dans les exemples ci-dessus, certaines valeurs de A2:A16 contiennent plusieurs nombres.
Par exemple, regardez la cellule A4, qui contient les nombres 250 et -500.
Les formules matricielles/non-matricielles ci-dessus ne renvoient que 250. Comment obtenir les deux nombres ?
Ici, nous utiliserons REGEXREPLACE à la place de REGEXEXTRACT.
Dans la cellule B2, copiez-collez la formule combo suivante et faites-la glisser vers le bas autant que vous le souhaitez.
=SPLIT(REGEXREPLACE(REGEXREPLACE(A2,"[^(-?d*.?d+)]","|"),"(|){1,}","| "),"|")
Si la formule qui extrait les nombres négatifs ou positifs renvoie #REF!, survolez l’erreur.
Videz la cellule/le tableau que la formule essaie d’écraser.
Explication de la formule (REGEXREPLACE)
Auparavant, nous avons utilisé l’expression régulière -?d*.?d+ pour extraire les nombres négatifs ou positifs dans Google Sheets.
Au lieu de cela, nous avons utilisé ici une classe de caractères négative en plaçant le ^ devant, afin de pouvoir « remplacer » tous les caractères sauf les nombres négatifs ou positifs.
Voici l’expression régulière pour cela : [^(-?d*.?d+)].
=REGEXREPLACE(A2, "[^(-?d*.?d+)]", "|")
Dans la formule, le pipe est le texte/délimiteur de « remplacement ».
Si vous utilisez la formule ci-dessus dans la cellule B4, bien sûr, A2 deviendra A4, la sortie sera ||||||||||250|||||||||||||-500.
Les caractères de pipe supplémentaires poseront problème dans SPLIT.
J’ai donc utilisé un deuxième REGEXREPLACE avec l’expression régulière (|){1,} pour les substituer.
=REGEXREPLACE(REGEXREPLACE(A4, "[^(-?d*.?d+)]", "|"),"(|){1,}","| ")
Sortie lorsque vous l’utilisez dans la cellule B4 : | 250| -500
Ici, le texte de remplacement est un pipe et un espace. Ainsi, dans SPLIT également, le délimiteur est un pipe avec un espace.
Formule matricielle :
=ArrayFormula(SIERREUR(SPLIT(REGEXREPLACE(REGEXREPLACE(A2:A, "[^(-?d*.?d+)]", "|"),"(|){1,}","| "),"|")))
Lorsque vous suivez les formules matricielles ou non matricielles REGEXEXTRACT ci-dessus, supprimez immédiatement le quantificateur point d’interrogation après le tiret pour extraire uniquement les nombres négatifs dans Google Sheets.
Exemple :
=value(SI(na(REGEXEXTRACT(A2,"-d*.?d+")),))
Mais cela peut ne pas fonctionner avec REGEXREPLACE. Nous utiliserons donc un test logique IF.
=ArrayFormula(SI(split(REGEXREPLACE(REGEXREPLACE(A2, "[^(-?d*.?d+)]", "|"),"(|){1,}","| ")<0, split(REGEXREPLACE(REGEXREPLACE(A2, "[^(-?d*.?d+)]", "|"),"(|){1,}","| "), "")))
Note : Cela s’applique également à leurs formules matricielles correspondantes.
C’est tout. Merci de votre attention. Profitez-en !
