Detecter si un nombre du format 20-04-2001 s'est transforme en date (avec /)

[Drazuhl]

Hello,

Je bosse sur un fichier excel qui me permet de gagner beaucoup de temps en verifiant une liste de nombres du format suivant:

15625-89-5
42978-66-5
119-61-9

Mon probleme est que les gens qui creent ces listes se trompent parfois. Par exemple ca leur arrive de mettre un espace a la fin du nombre ou un O a la place d'un 0. Etant donne que j'utilise une formule qui a besoin d'avoir le nombre ecrit exactement, j'ai cree une colonne ou je verifie mes nombres avec ce type de formule:

=-ISERR(SEARCH("O",I35))+1 (celle la m'affiche un chiffre 1 si jamais il y a un 0 dans la cellule)

Comme je me suis rendu compte que parfois au lieu d'avoir les chiffres separes par des -, ils sont separes par des /, j'ai voulu utiliser le meme type de formule pour verifier que mes nombres n'en contiennent pas.

Mais la ou ca bloque c'est qu'en fait, lorsqu'il y a des /, c'est parceque Excel a automatiquement transforme mes nombres en dates (par exemple 119-61-9 ne change pas, mais 2004-03-04 devient 04/03/2004).
Et comme ce n'est que du formatage, le / "ne compte pas" et ma formule ISERR ne les detecte pas. Si je rajoute manuellement un / dans la cellule, ma formule le trouve, mais elle ne trouve pas les 2 / automatiques...

Et si j'essaye de coller mes chiffres avec l'option "Match destination formating", au lieu de me remettre mon nombre original avec les - -, ca me fait une espece de division et ca me donne un chiffre (par exemple pour le chiffre transforme en date: 20/04/2013 ca va me donner 41384).

J'ai beau formatte ma colonne pour que les chiffres soient consideres comme du texte pour eviter que cette division arrive, ma formule ne detecte toujours pas les /...

Je ne sais pas si vous me comprenez, mais si vous avez une idee de comment je pourrais m'assurer que tous les / soient detectes, ca serait top.

Et tant que je suis la, je voudrais aussi que la hauteur de mes lignes s'adapte automatiquement au texte (qui peut varier de 1 a 6-7 lignes de texte). Ca marche tres bien avec l'outil hauteur automatique pour le texte que je copie colle moi meme. Mais quand je colle ma liste dans une colonne, ca importe automatiquement du texte dans la colonne d'a cote (via ce type de formule: =IF(ISNA(MATCH(I2,F:F,0)),"",INDIRECT("g"&MATCH(I2,F:F,0)))) et le texte importe n'est pas pris en compte par l'outil hauteur automatique...

Any idea pour regler ce probleme?

Merci beaucoup,

Matthieu

 

[mécano41]

Re : Detecter si un nombre du format 20-04-2001 s'est transforme en date (avec /)

Bonjour,

Si tu écris 2004-03-04 dans une feuille vierge (dont les cellules sont donc au format STANDARD), EXCEL te transforme cela en DATE mais si tu formates les données en TEXTE, avant de les importer, elles ne seront pas transformées.

Si tu veux écrire dans une feuille sans avoir cette modification par EXCEL, il faut formater les cellules avant d'écrire dedans.

Cordialement

 

[Drazuhl]

Re : Detecter si un nombre du format 20-04-2001 s'est transforme en date (avec /)

Bonjour,

Merci pour la reponse.

Mon probleme c'est que je recois ces listes deja creee par nos differents fournisseurs.

Et si je re-formate ces cellules en texte, mon nombre (qui a la base etait 21-03-2013 par exemple et est devenu automatiquement 21/03/2013) est transforme a nouveau en 41354 par exemple...

Et j'ai essaye de coller ce 21/03/2013 dans une cellule deja formate en texte, ca ne change rien.

A la limite si je ne peux pas recuperer le 21-03-2013 original, ce n'est pas la fin du monde, mais je voudrais au moins que ma formule:

=-ISERR(SEARCH("/",I2))+1

Soit capable re trouver les / /. Parceque quand c'est vraiment considere comme une date et pas du texte par Excel, il ne retrouve pas les / /.

Bref ca me parait complique mais j'aimerais bien trouver une solution...

Merci

 

[mécano41]

Re : Detecter si un nombre du format 20-04-2001 s'est transforme en date (avec /)

Bonjour,


Avec ceci en D4 : D11 par ex. :

10-11-12
56236
2003-12-10
10-12-2003
2003-12-10
10-12-2003
10/12/2004
10/12/2004

Avec la formule suivante en F4 : F11 par ex. :

=SI(ESTTEXTE(D5);D5;JOUR(D5)&"-"&MOIS(D5)&"-"&ANNEE(D5))

on a en F4 : F11

10-11-12
56236
2003-12-10
10-12-2003
2003-12-10
10-12-2003
10-12-2004
10-12-2004

Le problème c'est que 2004-12-05 et 05-12-2004 donnent la même date donc le même texte recomposé. Si tu n'as aucun moyen de faire la différence (comme par ex. que la fin est un indice sur 2 chiffres), je ne vois pas...

Cordialement

 

[Dormeur74]

Re : Detecter si un nombre du format 20-04-2001 s'est transforme en date (avec /)

Bonjour tout le monde,
Je pense qu'une macro peut régler assez facilement ce type de pb. Voici une approche qu'il faudra peut-être compléter et qui remplace les "/" par des "-" et les "O" par des "0".
Exemple en PJ.

 

[Drazuhl]

Re : Detecter si un nombre du format 20-04-2001 s'est transforme en date (avec /)

Hello,

Merci pour les reponses.

Alors VBA j'en avais entendu parle en cherchant des solutions, mais a vrai dire je n'ai jamais test, donc ca reste envisageable mais va falloir que je me forme ^^

J'ai reussi a ouvrir le gestionnaire de macro, j'ai vu ce que tu avais fait, maintenant il faut que je comprenne, notament pourquoi il y a un J dans ta formule...
Je viens de regarder un ou deux tuto et j'ai deja fait une maccro pour effacer rapidement les donnees d'une colonne pour pouvoir reutiliser le fichier facilement!

Mecano, je vais regarder ta formule, merci. Pour repondre a ta question j'ai regarde le format des nombres en fait ca marche comme ca:

A-B-C

A peut aller de 2 a 6 chiffres
B est toujours compose de 2 chiffres
C est toujours compose de 1 chiffre

Vu que je travaille avec des fournisseurs chinois et qu'en Chine les dates s'ecrivent comme ca: 2013/06/07 (donc dans le sens inverse de la France), en fait il y a juste un type de nombre qui peut etre transforme en date. C'est quand "A" est compose de 4, ou peut etre 2 chiffres et que B est inferieur a 12. Par exemple 1993-11-9 sera transforme en 09/11/1993

Donc puisqu'il n'y a qu'un sens possible pour la date, est ce qu'on peut etre sur de regler le probleme avec ta formule?

Ensuite avec une maccro je devrais pouvoir reussir a ce que tout se fasse plus ou moins automatiquement

Merci,

Matthieu

 

[mécano41]

Re : Detecter si un nombre du format 20-04-2001 s'est transforme en date (avec /)

Bonjour,

En formule, y a aussi la possibilité d'utiliser ceci qui semble fonctionner dans tous les cas (j'avais déjà essayé mais probablement mal car avec une date, j'avais une erreur et j'ai abandonné... c'est le code de Dormeur 74 qui m'a incité à réessayer) :

=SUBSTITUE(D5;"/";"-")

Cordialement

 

[Drazuhl]

Re : Detecter si un nombre du format 20-04-2001 s'est transforme en date (avec /)

Hello Mecano,

Merci pour ta proposition, mais ca n'a pas l'air de fonctionner chez moi.

Avec la date: 04/01/9003

Ca me donne le nombre 2594322 au lieu de 04-01-9003
Et si je change l'option de collage, ca me donne directement 2594322 dans la cellule de base donc c'est pas mieux...

D'autre part, ce 04-01-9003 serait faux parceque comme je l'ai dis, le format est forcement A-B-C (A>2 chiffres, B=2 chiffres, C=1 chiffre). Donc a mon avis avant d'etre transforme en date, le nombre etait 9003-01-4

Donc ca me semble complique. Je vais test ta formule SI(ESTEXTE), vu que je suis a peux pret sur de mon format, mais il faudra que j'arrive a eliminer le 0...

Etant donne les risques de creer une erreur si je trouve une formule qui change automatiquement les / par des - (chiffres pas dans le bon ordre), je pense que ca serait plus safe de trouver un moyen de detecter ces /, pour prevenir l'utilisateur qui devra corriger lui meme (il y en a max 1-2 par liste) plutot que d'automatiser des erreurs...

Mais comment faire, la est la question ^^

 

[Drazuhl]

Re : Detecter si un nombre du format 20-04-2001 s'est transforme en date (avec /)

Alors, j'ai teste ta fonction SI(ESTTEXTE) et ca marche pas mal du tout.

Dans le doute (au cas ou ca me transformerais ca en un nombre incorrect), je n'ai pas remplace ma colonne par cette formule, mais je l'ai mis dans la colonne d'a cote et j'utilise une formule =IF(A1<>B1,1,0). Comme ca si il y un probleme, avec un formatage conditionel tout simple je peux afficher en rouge les problemes. Et j'ai meme la colonne B qui me montre ce que le chiffre devrait probablement etre.

Mais je me suis rendu compte que lorsque j'efface les donnes de ma colonne, Excel considere que rien dans une cellule ca veut dire le jour 0 de Janvier 1900 (ca m'affiche 1900-1-0 dans la colonne avec la formule SI(ESTTEXTE). Ca enclenche donc mon formatage conditionel vu qu'il n'y a pas la meme chose dans les deux cellule, bref c'est moche.

Une idee de comment je pourrais resoudre ca? Rajouter a la formule SI une deuxieme condition vraie (Si c'est du texte OU que la cellule est vide alors la condition est vraie)?

 

[Dormeur74]

Re : Detecter si un nombre du format 20-04-2001 s'est transforme en date (avec /)

2594322 et 04-01-9003, c'est pareil, sauf que les formats sont différents. Le premier est au format standard, le second au format date. Le premier correspond au n° de série du jour (type Long).

 

[Drazuhl]

Re : Detecter si un nombre du format 20-04-2001 s'est transforme en date (avec /)

Bon je me reponds tout seul j'ai trouve ^^

=IF(ISTEXT(I2),I2,(IF(COUNTBLANK(I2),"",YEAR(I2)&"-"&MONTH(I2)&"-"&DAY(I2))))

Je crois qu'un puriste d'excel s'arracherait les cheveux en voyant les bricolages que je fait, il doit y avoir des moyens bien plus elegants de proceder, mais ca a l'air de bien amrcher la

Le fichier commence a bien prendre forme!

Tant que j'y suis, est ce que vous sauriez comment est ce que je peux m'arranger pour que l'outil Hauteur automatique (pour que les lignes s'adaptent a la hauteur du texte qu'elles contiennent), fonctionne avec des case qui ont des formules?
En gros j'ai des colonnes dont le contenu des cellules est genere par une formule (qui va chercher le contenu d'autres cellules). Et donc quand ca m'affiche 5-6 lignes de texte, Excel ne s'adapte pas, parceque ce n'est pas vraiment du texte mais plutot le resultat d'une formule.

Et aussi on sait jamais, si il y a un moyen de faire verifier a excel que mes chiffres sont du format A-B-C que j'ai explique.
En gros expliquer a Excel, si un chiffre n'est pas de ce format (2a6chiffres-2chiffres-1chiffre) je voudrais avoir une erreur qui apparait. La ca me parait bien complique (=

EDIT:
Oui je me doutais que le nombre long correspondait a la "traduction" de la date. Mais un fois que j'ai ce nombre ca ne m'arrang epas pour retomber sur le format xxxx-xx-x
Anyway ce probleme est resolu, meme si ca a ete fait a la hache ^^

 

[Drazuhl]

Re : Detecter si un nombre du format 20-04-2001 s'est transforme en date (avec /)

Tiens une autre question.

Je protege ma feuille Excel avec un password, en ne gardant que la colonne ou les nombres sont copie-colle d'accessible.

Mais quand j'utilise ma macro:

Sub Effacer()
'
' Effacer Macro
' Efface les données de la colonne I
'
' Keyboard Shortcut: Ctrl+e
'
Range("I2:I600").Select
Selection.Clear
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Range("I2").Select
End Sub

En plus d'effacer le contenu des cellule, ca efface le format et apparement ca efface aussi le fait que j'avais unlock ces cellules. Donc a la prochaine utilisation je ne peux plus coller mes donnees parceque la colonne est lock!

Je sais qu'il y a une fonction pour effacer simplement le contenu de la cellule, mais je veux aussi effacer les couleurs de fond de la cellule etc...

Est ce que je peux effacer tout ca SANS enlever l'unlockage?

Merci,

Matthieu

 

[Dormeur74]

Re : Detecter si un nombre du format 20-04-2001 s'est transforme en date (avec /)

Désolé ami Drazuhl, j'ai quitté définitivement ce forum il y a un peu plus d'une heure à cause d'un pisse-vinaigre que je ne citerai pas.
Il y aura forcément quelqu'un de mieux luné pour t'aider. Bon vent !