paramètres régionaux et format de la fonction TEXTE

[haleakala]

Bonjour,

J'ai une question bête: au boulot, plusieurs collègues utilisent des versions anglaises d'Excel tandis que j'utilise une version française, donc avec des paramètres régionaux différents (par exemple: date, séparateur de décimales, ...)
On a un gros fichier avec plein de fonctions, qui passent bien les barrières linguistiques, cependant l'une ne passe pas du tout. Il s'agit de la partie format de la fonction texte.
Exemples:

="texte 1 " & TEXTE(K5;"0.0")

="texte 2 " & TEXTE(K6;"dd-mm-yyyy")

Comme vous pouvez le voir, dans le premier cas, le problème vient du point, car le format français reconnaît uniquement la virgule dans ce cas de figure et le second vient du format de date.

Ma question est donc: y a-t-il un moyen de réécrire la fonction de telle sorte qu'elle s'adapte aux deux environnements, et ceux de façon *simple*???

Merci par avance pour vos réponses avisées!

Haleakala

 

[Tibo]

Re : paramètres régionaux et format de la fonction TEXTE

Bonsoir,

La fonction TEXTE, comme son nom l'indique, retourne une valeur numérique en texte avec un certain format.

Je viens de faire l'essai ici :

=TEXTE(A1;"0.00")

et

=TEXTE(A1;"0,00")

ça me met ou le point ou la virgule. Donc je ne comprends pas trop ton problème.


Pour le problème de date, ça me parait relever plutôt de la version d'Excel. Une version anglaise demande effectivement dd-mm-yyyy tandis qu'une version française demande jj-mm-aaaa

Pour ce 2ème point, je ne sais pas, lors du passage d'un fichier d'une version à l'autre, si la traduction se fait automatiquement.

Sans trop savoir si j'ai fait avancer le schmilblick...

Bonne soirée

@+

 

[haleakala]

Re : paramètres régionaux et format de la fonction TEXTE

Bonsoir Tibo,

Merci pour la réponse.
Une recherche un peu approfondie sur internet m'a apporté le lien suivant: <http://support.microsoft.com/kb/468095/fr>
Je n'ai pas encore essayé la solution.

Un passage du texte clair (il s'agit d'un article sur la fonction TEXTE():

La description ci-dessus montre une limite de portée de cette fonction à
travers les versions et les paramétrages systèmes des utilisateurs.
La formule =TEXTE(A1;"jjjj jj mmmm aaaa") écrite dans une version
Française d'Excel avec des paramètres régionaux Français, ne fonctionne
plus chez un autre utilisateur paramétré intégralement en Anglais.

Il est toutefois possible d'utiliser une fonction qui permet un affichage
correct de la date quelles que soient la version d'Excel et les
paramètres régionaux définis.
Il s'agit de la fonction DATE().
Dans l'exemple, cette fonction s'écrirait :
=DATE(STXT(A1;7;2);STXT(A1;4;2);STXT(A1;1;2))
ou
= DATE(99;10;18)
Il suffit ensuite d'appliquer le format "jjjj jj mmmm aaaa" sur la
cellule contenant la formule.
La formule sera traduite par Excel et le format adapté au contexte
quelles que soient la version d'Excel et les paramètres régionaux
définis.

Concernant le format des décimales, dans mon cas, en reprenant l'exemple donné dans mon premier message, la cellule K5 contient le chiffre "6", et la formule

="texte 1 " & TEXTE(K5;"0.0")

renvoie le texte suivant: "texte 1 0.6", et la formule

=TEXTE(K5;"0.0")

renvoie "0.6"...
Voilà mon problème illustré...

Merci pour vos conseils!

Haleakala

 

[chris]

Re : paramètres régionaux et format de la fonction TEXTE

Bonjour
Effectivement je viens d'avoir le même problème avec la fonction texte sur un fichier destiné à une version US.
J'ai contourné le problème avec d'autres fonctions mais qui ont allourdit mes formules.

 

[haleakala]

Re : paramètres régionaux et format de la fonction TEXTE

bon, ok, en relisant ma réponse, je me suis rendu compte que la solution proposée ne répondait au problème.
Je repose donc clairement le problème: on a en format d'entrée des différences liées aux paramètres régionaux, et je souhaite avoir comme format de sortie un format indépendant de ces paramètres. Dans le cas des exemples ci-dessus, le format américain, malgré des fichiers excel tournant parfois avec des paramètres régionaux français.

Je suis en train de regarder du côté des macros. Ca ne me gène pas de rajouter une petite macro qui donne le format de sortie indépendamment des paramètres régionaux.

Voilà les derniers éléments en date...

A plus,

Haleakala

 

[chris]

Re : paramètres régionaux et format de la fonction TEXTE

Bonjour
Effectivement je viens d'avoir le même problème avec la fonction texte sur un fichier destiné à une version US.
J'ai contourné le problème avec d'autres fonctions mais qui ont alourdit mes formules.

 

[job75]

Re : paramètres régionaux et format de la fonction TEXTE

Bonsoir,
Concernant le problème du séparateur décimal, la formule
="texte 1 " & TEXTE(K5;GAUCHE(1/100;3))
fonctionnera quelle que soit l'option choisie (1/100 devient 0,01 ou 0.01).
A+

 

[abcd]

Re : paramètres régionaux et format de la fonction TEXTE

Bonjour,

Pour la première question, ne peux-tu employer le format personnalisé : "texte 1 "0,0 et saisir =K5 dans la cellule ?
Sur une version US la , sera automatiquement remplacée par un .

abcd

 

[haleakala]

Re : paramètres régionaux et format de la fonction TEXTE

Bonjour, et merci pour vos réponses.
J'ai cherché entre temps, et j'ai trouvé une solution. C'est probablement pas la plus jolie, mais ça marche.

Pour les nombres à décimales:
Je récupère les chiffres avant la virgule, et j'ajoute le point puis la première décimale (la formule ne me semble pas très optimisée).
Ca donne:

="texte 1" & ARRONDI(K6;0)&"."&ARRONDI(ARRONDI(K6;1)-ARRONDI(K6;0);0)

Pour les dates:
la fonction écrite en macro suivante:

Function displayDate(dtDate As Date) As String
    Dim stDateResult As String
    Dim iMonth As Integer
    Dim stMonth As String
    
    iMonth = Month(dtDate)
    
    Select Case iMonth
        Case 1
            stMonth = "Jan"
        Case 2
            stMonth = "Feb"
        Case 3
            stMonth = "Mar"
        Case 4
            stMonth = "Apr"
        Case 5
            stMonth = "May"
        Case 6
            stMonth = "Jun"
        Case 7
            stMonth = "Jul"
        Case 8
            stMonth = "Aug"
        Case 9
            stMonth = "Sep"
        Case 10
            stMonth = "Oct"
        Case 11
            stMonth = "Nov"
        Case 12
            stMonth = "Dec"
    End Select

    stDateResult = Day(dtDate) & "-" & stMonth & "-" & Year(dtDate)
    displayDate = stDateResult
    
End Function

Voilà. Si vous avez des idées pour rendre cela plus joli, je suis preneur, cependant les deux formules ne sont pas très sollicitées dans mon fichier excel, donc perdre 1E-9 sec pour l'exécution ne me gène pas.

Haleakala

 

[Tibo]

Re : paramètres régionaux et format de la fonction TEXTE

Bonjour,

J'avoue ne pas avoir bien compris ce que tu voulais faire avec ta formule :

="texte 1" & ARRONDI(K6;0)&"."&ARRONDI(ARRONDI(K6;1)-ARRONDI(K6;0);0)

Peux-tu nous mettre plusieurs exemples avec le résultat attendu, car on doit trouver un peu plus simple si on sait ce qu'il faut trouver

A te (re)lire

@+

Edit :

ARRONDI(ARRONDI(K6;1)-ARRONDI(K6;0);0) : semble toujours donner 0

Dans ce cas, tu peux remplacer ta formule par :

="texte 1" & ARRONDI(K6;0)&".0"

Bien que je n'ai pas compris pourquoi le recours à la fonction TEXTE ne fonctionnait pas.

Bon dimanche

@+

 

[haleakala]

Re : paramètres régionaux et format de la fonction TEXTE

OK, désolé, je me suis mal exprimé.
Le problème est donc le suivant: j'ai une formule avec une fonction texte() dont le format est prévu pour des paramètres régionaux US, qui ne marche donc pas avec des paramètres régionaux français (formats de date et de séparateur décimal différents).
C'est le premier point: la formule me donne pour un chiffre "6" le résultat "0.6" et pour une date (5 chiffres) le résultat "dd-Juin-yyyy").

Le deuxième point est que je souhaiterais afficher les résultats sous la forme américaine, indépendamment des paramètres régionaux de l'utilisateur.

En gros, je souhaiterais que le "6" soit affiché "6.0", mais également qu'un "6,3" (en paramètres français) soit affiché "6.3", et je ne peux pas utiliser la fonction format de cellule, car à côté du chiffre se trouve un texte passé également dans la formule en variable.
D'où l'idée de devoir reconstruire le nombre en récupérant d'abord la partie non décimale du chiffre, puis lui ajouter la première décimale en séparant par un ".".

Pour la date, même histoire. Le 12 juin 08 doit être affiché "12-Jun-2008", et également accompagné d'un texte passé en variable dans la formule (donc pas de "format de cellule" possible...).

Voilà, j'espère que le problème est plus compréhensible maintenant...

Haleakala

 

[Kotov]

Re : paramètres régionaux et format de la fonction TEXTE

Bonjour à tous,

Une proposition :
Imaginons la cellule B10 avec la valeur 5,6. Pour la transformer en 5.6, saisir en C10 :

=SI(ESTVIDE(SI(ESTERREUR(GAUCHE(B10;(SI(ESTERREUR(TROUVE(",";B10;1));"";TROUVE(",";B10;1)))-1)&REMPLACER(B10;1;SI(ESTERREUR(TROUVE(",";B10;1));"";TROUVE(",";B10;1));"."));B10;GAUCHE(B10;(SI(ESTERREUR(TROUVE(",";B10;1));"";TROUVE(",";B10;1)))-1)&REMPLACER(B10;1;SI(ESTERREUR(TROUVE(",";B10;1));"";TROUVE(",";B10;1));".")));"";SI(ESTERREUR(GAUCHE(B10;(SI(ESTERREUR(TROUVE(",";B10;1));"";TROUVE(",";B10;1)))-1)&REMPLACER(B10;1;SI(ESTERREUR(TROUVE(",";B10;1));"";TROUVE(",";B10;1));"."));SI(ESTNUM(B10)=VRAI;B10&".0";"");GAUCHE(B10;(SI(ESTERREUR(TROUVE(",";B10;1));"";TROUVE(",";B10;1)))-1)&REMPLACER(B10;1;SI(ESTERREUR(TROUVE(",";B10;1));"";TROUVE(",";B10;1));".")))

A noter (pour les néophytes) : les contrôles ESTVIDE et ESTERREUR permettent d'éviter les messages d'erreurs si la cellule contrôlée est vide ou ne contient pas de virgule. La formule contient également une vérification destinée à restituer une cellule vide si la cellule d'origine contient autre chose qu'une valeur numérique (texte par exemple)
Résultats obtenus :
5,6 -> 5.6
5 -> 5.0
vide -> ""
Texte -> ""

Bonne journée
Kotov

 

[Kotov]

Re : paramètres régionaux et format de la fonction TEXTE

Re,

Voici également une proposition pour régler le problème de date en une unique formule :
Imaginons la date en A2

=TEXTE($A$2;"jj")&"-"&SI(GAUCHE(TEXTE(($A$2);"mmm");3)="fév";"feb"&"-"&TEXTE($A$2;"aaaa");SI(GAUCHE(TEXTE(($A$2);"mmm");3)="avr";"apr"&"-"&TEXTE($A$2;"aaaa");SI(GAUCHE(TEXTE(($A$2);"mmm");3)="mai";"may"&"-"&TEXTE($A$2;"aaaa");SI(GAUCHE(TEXTE(($A$2);"mmm");4)="juin";"jun"&"-"&TEXTE($A$2;"aaaa");SI(GAUCHE(TEXTE(($A$2);"mmm");4)="juil";"jul"&"-"&TEXTE($A$2;"aaaa");SI(GAUCHE(TEXTE(($A$2);"mmm");3)="aoû";"aug"&"-"&TEXTE($A$2;"aaaa");SI(GAUCHE(TEXTE(($A$2);"mmm");3)="déc";"dec"&"-"&TEXTE($A$2;"aaaa");GAUCHE(TEXTE(($A$2);"mmm");3)&"-"&TEXTE($A$2;"aaaa"))))))))

Comme la précédente elle est relativement "indigeste"
Le principe
le jour et l'année sont convertis au format texte. Pour transformer le "mois français" en "mois US", j'extrais les 3 premières lettres du mois français au format texte (les 4 premières pour juin et juillet). Sachant que 7 mois sur 12 s'écrivent différemment au format US, sachant qu'Excel autorise 7 imbrications de "SI" dans une formule, je remplace les 7 mois impliqués par leur abréviation US. Pour les 5 autres mois, leur abréviation US étant identique à l'abréviation FR, je conserve les 3 premières lettres du mois au format texte.

J'espère avoir été suffisamment clair dans la traduction de cette "formule à tiroirs".
Il y a bien longtemps que je ne me prends plus la tête sur les formules, préférant régler ce genre de casse-tête via le VBA.
Mais bon, une petite formule de temps en temps, ça vaut un Sudoku !

A +
Kotov