Exctraction de caractère pour former une heure

Résolu/Fermé

sevillaa - 25 sept. 2018 à 10:20
titeufdu89 Messages postés 374 Date d'inscription samedi 28 juillet 2012 Statut Membre Dernière intervention 1 avril 2023 - 25 sept. 2018 à 16:32

Bonjour,

j'ai sur un fichier de suivi de suivi de production sur lequel je suis obliger de réaliser une extraction sur une un numéro de série pour pouvoir le transformer en heure, problème le code de série comprends la date en plus de l'heure, exemple:
N° de série: 180917053121
du coup pour extraire uniquement les 6 derniers caractère j'ai la formule
= "cellule N° de série" - (gauche("cellule";6)*1000000) qui me donne uniquement les caractère correspondant à l'heure. (j'aurais pu utiliser la formule droite aussi j'ai déjà fait les essais)
Cette partie la de mon extraction fonctionne.
J'ai donc un cellule avec uniquement les caractères correspondant à l'heure extrait (dans mon exemple: 053121), j'ai donné à cette cellule un format spécial pour intégrer le premier 0.
J'ai donc pour extraire sous forme d'heure:
ma cellule timer: 053121
je rédige la formule
=TEMPS(STXT(timer;1;2);STXT(timer;3;2);STXT(timer;5;2))
pour faire apparaître sous cette forme: 05:31:21
hors le résultat affiche 05:12:01
Mon problème du coup:
je dois réaliser cette opération sur 42976 lignes de données, et si la formule ne fonctionne pas j'ai peu envie de réaliser l'extraction manuelle, je suspecte la formule de ne pas intégrer le premier 0 :(

merci de votre attention, je suis tout à fait disponible pour vous envoyer par mails extrait du fichier ou image :)

A voir également:

Exctraction de caractère pour former une heure
Caractère ascii - Guide
Caractere speciaux - Guide
Caractère spéciaux - Guide
2go internet = combien d'heure - Forum Opérateurs & Réseaux mobiles

3 réponses

Réponse 1 / 3

titeufdu89 Messages postés 374 Date d'inscription samedi 28 juillet 2012 Statut Membre Dernière intervention 1 avril 2023 38
25 sept. 2018 à 10:37

Bonjour Sevilla,

Supposons que ton numéro de serie est en A1 :
=STXT(A1;NBCAR(A1)-5;2)&":"&STXT(A1;NBCAR(A1)-3;2)&":"&STXT(A1;NBCAR(A1)-1;2)

Avec un format de cellule au format heure "00:00:00:"
Bonne journée

Jc

sevillaa
25 sept. 2018 à 10:43

Bonjour merci de votre aide,
j'ai intégrer votre formule dans ma feuille de calcul, tout fonctionne (sur les 42976 lignes ^^)
pourriez vous m'expliqué le fonctionnement de la formule svp, car du coup même plus besoin de passer par une colonne timer et aucune erreur de transcription en heure ^^
un grand merci

M-12 Messages postés 1331 Date d'inscription lundi 22 septembre 2008 Statut Membre Dernière intervention 8 avril 2023 284
25 sept. 2018 à 10:57

Re,

Tu colles ton N° de série en A3

N° de série: 180917053121

et avec la formule cela te donne l'heure, à mettre au format hh:mm:ss

sevillaa > M-12 Messages postés 1331 Date d'inscription lundi 22 septembre 2008 Statut Membre Dernière intervention 8 avril 2023
25 sept. 2018 à 11:00

merci pour l'explication de votre formule ^^,
bonne journée

titeufdu89 Messages postés 374 Date d'inscription samedi 28 juillet 2012 Statut Membre Dernière intervention 1 avril 2023 38
Modifié le 25 sept. 2018 à 11:19

Je décompose la formule pour que tu comprenne mieux son fonctionnement :
STXT(A1;NBCAR(A1)-5;2) : stxt est une formule qui renvoie un morceau d'une chaine on détermine, la chaine à analyser (ici A1), le numéro du 1er caractère (ici NBCAR(A1)-5) et le nombre de caractères à renvoyer (ici 2).

Pour determiner le numéro du 1er caractère j'ai utiliser la fonction NBCAR qui renvoi le nombre total de caractères dans une chaine auquel j'ai retranché 5 car je souhaite le 6ème caractère à partir de la droite, puis entre 2 car je veux qu'il me renvoie 2 caractères > cette première partie de formule renvoie l'heure...

&":"& > cette concaténation ajoute le séparateur : entre les éléments extraits de la chaine, puis la suite du code extrait les minutes et les secondes et le toute renvoie l'heure au format "00:00:00" et comme excel est vraiment intelligent, il reconnait le format et passe théoriquement tout seul le format de la cellule en heure ;-)

sevillaa
25 sept. 2018 à 11:29

super merci beaucoup j'avais du mal à comprendre le -5 et le &":"& de la formule,
vous avez toute ma gratitude ^^
et merci pour l'explication je saisis mieux la formule, et vu que je risque d'être amener à la réutiliser je voulais la comprendre
je vous souhaite une bonne journée

Réponse 2 / 3

M-12 Messages postés 1331 Date d'inscription lundi 22 septembre 2008 Statut Membre Dernière intervention 8 avril 2023 284
25 sept. 2018 à 10:47

Bonjour,

en supposant que la donnée se trouve en A3

=TEMPS(STXT(DROITE(A3;6);1;2);STXT(DROITE(A3;4);1;2);DROITE(A3;2))

sevillaa
25 sept. 2018 à 10:53

bonjour, merci de votre aide la formule fonctionne ^^
mais le format fait apparaître l'heure en: 5:30 AM,
une simple modification du format suffit ou est ce inhérent à la formule ?

Réponse 3 / 3

DjiDji59430 Messages postés 4135 Date d'inscription samedi 19 avril 2008 Statut Membre Dernière intervention 27 octobre 2024 676
Modifié le 25 sept. 2018 à 10:49

Bonjour,

Ta formule pour avoir les 6 caracteres te donne un nombre parceque tu as *1000000.
D’où bidouillage pour avoir le zéro qui est fictif et est la pour faire beau .!
en réalité avec temps() tu transformes 53121 => 05:12:01
refais ton calcul avec droite(180917053121;6), et tu auras un résultat correct

Crdlmt

sevillaa
25 sept. 2018 à 10:55

bonjour, merci de votre aide, je suppose qu'il faut utiliser la fonction searchtxt pour extraire les caractères inhérent aux heures, minutes et seconde,
merci bien

Discussions similaires

35 GO à l'étranger

Colissimo

Caractère invisible ou vide pour pseudo invisible

Quel est l'équivalent 40 GO d'internet en heures ?

Colis à l'aéroport.