=CNUM(A2) convertit un nombre stocké sous forme de texte en A2 en vrai nombre. Les nombres en texte ont l'air normaux, mais SOMME, MOYENNE et NB les ignorent, ce qui explique qu'un total puisse donner 0. Dans un Excel anglais, CNUM s'appelle VALUE ; le tableau affiche les formules sous cette forme anglaise, avec des virgules, mais vous pouvez aussi les taper comme dans un Excel français, avec des points-virgules : =CNUM(A2).
| A | B | C | |
|---|---|---|---|
| 1 | Imported | VALUE | |
| 2 | 120 | 120 | |
| 3 | 85 | 85 | |
| 4 | 240 | 240 | |
| 5 | 15 | 15 | |
| 6 | 0 | 460 |
=CNUM(A2)A6 totalise 0 parce que les quatre cellules de la colonne A sont du texte (tapé avec une apostrophe, comme le sont souvent les données importées d'un CSV ou d'une page web). La colonne C convertit chacune d'elles et C6 donne le vrai total, 460.
Reconnaître un nombre stocké sous forme de texte
Dans Excel, un nombre stocké sous forme de texte :
- se place à gauche de la cellule, alors que les nombres se placent à droite (sauf si l'alignement a été modifié) ;
- affiche un petit triangle vert dans le coin supérieur gauche de la cellule, et une icône d'avertissement quand vous la sélectionnez, avec le message "Nombre stocké sous forme de texte" ;
- est compté par NBVAL mais pas par NB.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Value | ISNUMBER | ISTEXT | Text numbers | |
| 2 | 120 | TRUE | FALSE | 2 | |
| 3 | 85 | FALSE | TRUE | ||
| 4 | 240 | TRUE | FALSE | ||
| 5 | 15 | FALSE | TRUE |
=ESTNUM(A2)E2 compte combien de cellules de la plage sont remplies sans être des nombres : ici les deux tapées avec une apostrophe. Sur une colonne propre de nombres, elle renvoie 0.
Quatre formules qui convertissent
| A | B | C | |
|---|---|---|---|
| 1 | Text | Result | How |
| 2 | 1250 | 1250 | VALUE |
| 3 | 1250 | double minus | |
| 4 | 1250 | multiply by 1 | |
| 5 | 1250 | add 0 |
=CNUM(A2)Tout calcul oblige Excel à lire le texte comme un nombre, et CNUM en est la version explicite. -- (deux signes moins : moins, puis encore moins) est le choix habituel à l'intérieur d'autres formules, parce qu'il est court : =SOMMEPROD(--A2:A5) additionne une colonne de nombres en texte sans colonne d'aide. CNUM lit aussi un texte avec un symbole monétaire, des séparateurs de milliers ou un signe pourcent, selon les réglages de votre Excel : dans un Excel anglais, VALUE("$1,250") vaut 1250 et VALUE("12%") vaut 0.12.
| A | B | |
|---|---|---|
| 1 | Quantity (text) | Quantity |
| 2 | 125 |
À vous : La quantité de A2 a été importée sous forme de texte. En B2, convertissez-la en nombre.
Du texte avec des unités ou d'autres séparateurs
CNUM renvoie #VALEUR! (#VALUE! en anglais ; le tableau affiche les noms d'erreur anglais) quand le texte contient quelque chose qu'elle ne sait pas lire comme un nombre. Deux cas fréquents :
| A | B | C | |
|---|---|---|---|
| 1 | Text | Fixed | Without the fix |
| 2 | 120 kg | 120 | #VALUE! |
| 3 | 1.234,5 | 1234.5 | #VALUE! |
=CNUM(SUBSTITUE(A2;" kg";""))- Une unité ou un mot : supprimez-le d'abord avec SUBSTITUE, comme en B2.
- Une virgule comme séparateur décimal et un point pour les milliers, comme
1.234,5venu d'un système allemand ou brésilien : VALEURNOMBRE (NUMBERVALUE en anglais, Excel 2013 et versions ultérieures) prend le séparateur décimal et le séparateur de groupes comme deuxième et troisième arguments. CNUM ne connaît que les séparateurs de votre propre Excel, donc C3 échoue.
Des espaces avant ou après les chiffres n'arrêtent pas CNUM, mais les espaces insécables des pages web, si ; SUBSTITUE(A2;CAR(160);"") les supprime d'abord. Pour les causes générales de cette erreur, voir #VALEUR!.
| A | B | |
|---|---|---|
| 1 | Amount | Total |
| 2 | 120 | |
| 3 | 45 | |
| 4 | 80 |
À vous : Les montants de A2:A4 sont des nombres stockés sous forme de texte. En B2, renvoyez leur total avec une seule formule.
Convertir sur place sans formule
Les formules placent les nombres dans une nouvelle colonne. Pour corriger les cellules elles-mêmes :
- Convertir en nombre. Sélectionnez les cellules (la première cellule sélectionnée doit porter le triangle vert), cliquez sur l'icône d'avertissement à côté de la sélection et choisissez Convertir en nombre. C'est la correction la plus rapide.
- Convertir. Sélectionnez la colonne, Données > Convertir, et cliquez tout de suite sur Terminer. Excel ressaisit chaque cellule et transforme les nombres en texte en nombres.
- Collage spécial, Multiplication. Tapez 1 dans une cellule vide et copiez-la. Sélectionnez les nombres en texte, Accueil > Coller > Collage spécial, choisissez Multiplication, cliquez sur OK.
Si les cellules sont au format Texte (Accueil > Format de nombre affiche "Texte"), passez-les d'abord en Standard ; sinon tout ce que vous y tapez est de nouveau stocké comme du texte.
Erreur fréquente : des recherches entre texte et nombres
Une valeur cherchée de 101 ne correspond pas au texte 101 : RECHERCHEV, RECHERCHEX et EQUIV renvoient #N/A, et =A2=101 vaut FAUX, alors que les deux cellules se ressemblent. Convertissez un des deux côtés pour qu'ils soient du même type. Quand c'est la colonne de recherche qui contient le texte, convertissez plutôt la valeur cherchée :
=VLOOKUP(TEXT(E2,"0"), A2:C6, 3, FALSE) E2 is a number, column A holds text numbers
=VLOOKUP(--E2, A2:C6, 3, FALSE) E2 holds a text number, column A holds numbers
Dans un Excel français : =RECHERCHEV(TEXTE(E2;"0");A2:C6;3;FAUX) et =RECHERCHEV(--E2;A2:C6;3;FAUX). La page ESTNUM et ESTTEXTE montre comment vérifier chaque côté.
Questions fréquentes
Comment convertir du texte en nombre dans Excel ?
Avec une formule, =CNUM(A2) ou =--A2. Sans formule, sélectionnez les cellules, cliquez sur l'icône d'avertissement qui apparaît à côté et choisissez Convertir en nombre.
Pourquoi SOMME renvoie-t-elle 0 dans Excel ?
Les nombres sont stockés sous forme de texte, et SOMME ignore le texte. Ils sont en général alignés à gauche et portent un petit triangle vert. Convertissez-les avec =CNUM(A2), ou additionnez-les directement avec =SOMMEPROD(--A2:A10).
Pourquoi Convertir en nombre ne fonctionne-t-il pas ou n'apparaît-il pas ?
Excel ne le propose que pour un texte qu'il sait lire comme un nombre. Une espace insécable venue d'une page web, une unité comme kg ou un séparateur décimal que votre Excel n'utilise pas fait disparaître le triangle vert. Nettoyez d'abord le texte : =CNUM(SUBSTITUE(A2;CAR(160);"")) ou =VALEURNOMBRE(A2;",";".").
Comment convertir des nombres qui utilisent un autre séparateur décimal ?
Utilisez VALEURNOMBRE en précisant les séparateurs : =VALEURNOMBRE(A2;",";".") transforme 1.234,5 en 1234,5. CNUM ne comprend que les séparateurs des réglages de votre propre Excel.
