Formule SUBSTITUE, SUBSTITUE IF [Résolu/Fermé]

Signaler
Messages postés
13
Date d'inscription
mardi 5 décembre 2017
Statut
Membre
Dernière intervention
5 mars 2018
-
Messages postés
13
Date d'inscription
mardi 5 décembre 2017
Statut
Membre
Dernière intervention
5 mars 2018
-
Bonjour,

Je me heurte actuellement à un soucis de taille. En résumé :

Dans ma colonne E, j'ai des horaires au format brut. Dans ma colonne F, je transforme ces horaires au format souhaité grâce à une formule SUBSTITUE. Cependant, dans ma colonne E, j'ai certains horaires au format brut qui respectent des doubles tranches horaires et que je ne parviens pas à transformer grâce à la formule. Je mets les exemples ci dessous :

1) Ce que je réussis à faire :

--> transformer :

<dt>Lundi : </dt> <dd>09h00 à 20h00</dd>

<dt>Mardi : </dt> <dd>09h00 à 20h00</dd>

<dt>Mercredi : </dt><dd>09h00 à 20h00</dd>

<dt>Jeudi : </dt> <dd>09h00 à 20h00</dd>

<dt>Vendredi : </dt><dd>09h00 à 20h30</dd>

<dt>Samedi : </dt> <dd>09h00 à 20h00</dd>

<dt>Dimanche : </dt><dd>Fermé</dd>

--> en :

2:09:00:20:00,3:09:00:20:00,4:09:00:20:00,5:09:00:20:00,6:09:00:20:30,7:09:00:20:00,

2) Ce que je ne réussis pas à faire :

--> transformer :

<dt>Lundi : </dt> <dd>08h30 à 12h00 – 14h00 à 19h15</dd>

<dt>Mardi : </dt> <dd>08h30 à 12h00 – 14h00 à 19h15</dd>

<dt>Mercredi : </dt><dd>08h30 à 12h00 – 14h00 à 19h15</dd>

<dt>Jeudi : </dt> <dd>08h30 à 12h00 – 14h00 à 19h15</dd>

<dt>Vendredi : </dt><dd>08h30 à 12h00 – 14h00 à 19h15</dd>

<dt>Samedi : </dt> <dd>08h30 à 19h15</dd>

<dt>Dimanche : </dt><dd>08h15 à 12h00</dd>

--> en :

2:08:30:12:00,2:14:00:19:15,3:08:30:12:00,3:14:00:19:15,4:08:30:12:00,4:14:00:19:15,5:08:30:12:00,5:14:00:19:15,6:08:30:12:00,6:14:00:19:15,7:08:30:19:15,1:08:15:12:00,

A Savoir : 2 = Lundi ; 3 = Mardi ; 4 = Mercredi ; 5 = Jeudi ; 6 = Vendredi, 7 = Samedi ; 1 = Dimanche


Auriez-vous une solution à m'apporter ? La formule SUBSTITUE IF pourrait-elle fonctionner ? Excel m'indique que je ne peux ajouter de caractères à ma formule déjà trop longue à moins d'utiliser la formule CONCATENATE... Je ne vois pas trop l'idée..De plus, .

En bonus, si vous arrivez à passer :

<ital><dt>Lundi : </dt> <dd>Fermé</dd>
<dt>Mardi : </dt> <dd>Fermé</dd>
<dt>Mercredi : </dt><dd>Fermé</dd>
<dt>Jeudi : </dt> <dd>Fermé</dd>
<dt>Vendredi : </dt><dd>Fermé</dd>
<dt>Samedi : </dt> <dd>Fermé</dd>
<dt>Dimanche : </dt><dd>Fermé</dd>

--> En rien (cellule vide), et non pas, comme c'est le cas actuellement, en : 2:<dd>Fermé,3:<dd>Fermé,4:<dd>Fermé,5:<dd>Fermé,6:<dd>Fermé,7:<dd>Fermé

Je vous met le fichier ici : https://mon-partage.fr/f/Q118isGk/


Je vous remercie !

FS

7 réponses

Messages postés
2172
Date d'inscription
lundi 6 mai 2013
Statut
Membre
Dernière intervention
9 novembre 2020
298
Bonsoir
Pour la première question avec les données en colonne A et la formule en colonne B:
=SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(A2;"</dd>";"£");"<dd>";"");"à";"");";";":");"nbsp";"");"<dt>";"");"</dd>";"");"&";"");"</dt>";"");"Lundi:::";"2:");"Mardi:::";"3:");"Mercredi:::";"4:");"Jeudi:::";"5:");"Vendredi:::";"6:");"Samedi:::";"7:");"Dimanche:::";"1:");"h";":");" – ";",");CAR(10);"");" ";"$");"$";"");"£";",")

A plus tard pour la deuxième.
A tester
Cdlt
Messages postés
2172
Date d'inscription
lundi 6 mai 2013
Statut
Membre
Dernière intervention
9 novembre 2020
298
Pour l'ensemble des 2
=SI(ESTERR(TROUVE("Fermé";SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(A2;"</dd>";"£");"<dd>";"");"à";"");";";":");"nbsp";"");"<dt>";"");"</dd>";"");"&";"");"</dt>";"");"Lundi:::";"2:");"Mardi:::";"3:");"Mercredi:::";"4:");"Jeudi:::";"5:");"Vendredi:::";"6:");"Samedi:::";"7:");"Dimanche:::";"1:");"h";":");" – ";",");CAR(10);"");" ";"$");"$";"");"£";",");1));SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(A2;"</dd>";"£");"<dd>";"");"à";"");";";":");"nbsp";"");"<dt>";"");"</dd>";"");"&";"");"</dt>";"");"Lundi:::";"2:");"Mardi:::";"3:");"Mercredi:::";"4:");"Jeudi:::";"5:");"Vendredi:::";"6:");"Samedi:::";"7:");"Dimanche:::";"1:");"h";":");" – ";",");CAR(10);"");" ";"$");"$";"");"£";",");"")
Cdlt
Messages postés
14934
Date d'inscription
lundi 18 octobre 2004
Statut
Contributeur
Dernière intervention
24 juin 2020
4 245
Bonjour,

Je pense que tu devrais simplifier tes SUBSTITUE par exemple supprimer tout ce qui est inutile < , >, /, dt, dd,  , etc . Puis si ta formule est trop longue tu passes à la colonne suivante.
Toujours zen
La perfection est atteinte, non pas lorsqu'il n'y a plus rien à ajouter, mais lorsqu'il n'y a plus rien à retirer.  Antoine de Saint-Exupéry
Messages postés
2172
Date d'inscription
lundi 6 mai 2013
Statut
Membre
Dernière intervention
9 novembre 2020
298
Petite correction sur la première formule, je viens de m'apercevoir qu'il en manque un bout.
=SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(A20;"</dd>";"£");"<dd>";"");"à";"");";";":");"nbsp";"");"<dt>";"");"</dd>";"");"&";"");"</dt>";"");"Lundi:::";"2:");"Mardi:::";"3:");"Mercredi:::";"4:");"Jeudi:::";"5:");"Vendredi:::";"6:");"Samedi:::";"7:");"Dimanche:::";"1:");"h";":");"agrave";"");" – ";",");CAR(10);"");" ";"$");"$";"");"£";",")
Pour ce qui est de la deuxième formule, je ne crois pas avoir répondu réellement à la question, je regarderai plus tard.
Cdlt
Messages postés
13
Date d'inscription
mardi 5 décembre 2017
Statut
Membre
Dernière intervention
5 mars 2018

Bonjour Frenchie,

Merci beaucoup pour ton prompt retour !

Dans le cas de ta première formule, con y est presque mais ce n'est pas encore tout à fait juste. Voici ce que j'obtiens :

2:08:30:12:00,14:00:19:15,3:08:30:12:00,14:00:19:15,4:08:30:12:00,14:00:19:15,5:08:30:12:00,14:00:19:15,6:08:30:12:00,14:00:19:15,7:08:30:19:15,1:08:15:12:00,

Alors que je devrais obtenir cela :

2:08:30:12:00,2:14:00:19:15,3:08:30:12:00,3:14:00:19:15,4:08:30:12:00,4:14:00:19:15,5:08:30:12:00,5:14:00:19:15,6:08:30:12:00,6:14:00:19:15,7:08:30:19:15,1:08:15:12:00,

En effet il faut que l'indicatif du jour apparaisse avant chaque plage horaire : 2:08:30:12:00,2:14:00:19:15

La seconde formule ne fonctionne pas, j'ai essayer d'apporter quelques modifs mais rien n'y fait...

De plus, une virgule reste à la fin, et il faut à tout prix la supprimer car l'outil sur lequel je souhaite importer les données ne la comprend pas.

J'ai essayé =GAUCHE(G2;NBCAR(G2)-5) mais il se passe quelque chose de bizarre avec les caractères en fin de chaîne... C'est variable. Un coup c'est 5, un coup c'est 3.


Merci beaucoup !

FS
Messages postés
2172
Date d'inscription
lundi 6 mai 2013
Statut
Membre
Dernière intervention
9 novembre 2020
298
Bonjour,
Excusez-moi pour la réponse tardive, mais je n'étais pas disponible.
Voici une proposition avec une fonction personnalisée (au vu de ce que vous demandiez, trouver une formule qui réponde à tout ce que vous vouliez devenait vite une usine à gaz, donc méthode abandonnée).
Avec la fonction personnalisée, il suffit de saisir en B1:
=FormatCible(A1) pour tester le contenu de A1 (à tirer vers le bas)
https://mon-partage.fr/f/nQ7w1W6Y/
En espérant que cela réponde à vos attentes.
Cdlt
Messages postés
13
Date d'inscription
mardi 5 décembre 2017
Statut
Membre
Dernière intervention
5 mars 2018

Bonjour,

Merci pour votre retour et l'aide que vous m'apportez.

En effet cela est finalement bien plus simple. Cependant, je ne saisis pas comment vous configurez la formule FormatCible ...

Je ne peux pas adapter le format pour que cela réponde parfaitement au format cible.

Merci à vous

Cordialement
Messages postés
2172
Date d'inscription
lundi 6 mai 2013
Statut
Membre
Dernière intervention
9 novembre 2020
298
Bonsoir,
En effet cela est finalement bien plus simple. Cependant, je ne saisis pas comment vous configurez la formule FormatCible ...
Je reconnais que je n'ai pas été très explicite. La formule utilisée est une fonction personnalisée, en clair, c'est un bout de programme qui fait le travail d'une formule complexe, voire trop longue. Pour en connaître le contenu il faut accéder au langage Visual basic, appuyez simultanément sur les touches ALTet F11. Du fait de la présence du VBA, il est impératif que le fichier soit enregistré au format "XLSM".
Je ne peux pas adapter le format pour que cela réponde parfaitement au format cible. Que manque-t-il ou qu'est ce qui en trop pour que cela ne corresponde pas au format cible?
En relisant votre demande initiale , je constate 2 choses qui n'apparaissent pas dans le fichier que vous avez déposé; d'une part le jour est suivi de : ,alors qu'ils n'y sont pas dans le fichier, et des sauts de lignes entre chaque journée également absents dans le fichier. Peut-être que c'est cela qui interfère dans le résultat attendu. Pourriez vous m'en dire un peu plus?

Cdlt
Messages postés
13
Date d'inscription
mardi 5 décembre 2017
Statut
Membre
Dernière intervention
5 mars 2018

Bonjour,

Merci pour ces précisions.

En effet, il s'agit d'une erreur de ma part sur le format brut. Le format brut que je vous ai indiqué au départ a déjà subit une modification.

Voici en réalité les possibilités de format brut obtenus après l'extraction de la plateforme :

BRUT :

<dt>Lundi : </dt> <dd>08h30 à 20h00</dd>

<dt>Mardi : </dt> <dd>08h30 à 20h00</dd>

<dt>Mercredi : </dt><dd>08h30 à 20h00</dd>

<dt>Jeudi : </dt> <dd>08h30 à 20h00</dd>

<dt>Vendredi : </dt><dd>08h30 à 20h00</dd>

<dt>Samedi : </dt> <dd>08h30 à 20h00</dd>

<dt>Dimanche : </dt><dd>Fermé</dd>


CIBLE :

2:08:30:20:00,3:08:30:20:00,4:08:30:20:00,5:08:30:20:00,6:08:30:20:00,7:08:30:20:00

BRUT :

<dt>Lundi : </dt> <dd>08h30 à 12h00 – 14h00 à 19h15</dd>

<dt>Mardi : </dt> <dd>08h30 à 12h00 – 14h00 à 19h15</dd>

<dt>Mercredi : </dt><dd>08h30 à 12h00 – 14h00 à 19h15</dd>

<dt>Jeudi : </dt> <dd>08h30 à 12h00 – 14h00 à 19h15</dd>

<dt>Vendredi : </dt><dd>08h30 à 12h00 – 14h00 à 19h15</dd>

<dt>Samedi : </dt> <dd>08h30 à 19h15</dd>

<dt>Dimanche : </dt><dd>08h15 à 12h00</dd>

CIBLE :

2:08:30:12:00,2:14:00:19:15,3:08:30:12:00,3:14:00:19:15,4:08:30:12:00,4:14:00:19:15,5:08:30:12:00,5:14:00:19:15,6:08:30:12:00,6:14:00:19:15,7:08:30:19:15,1:08:15:12:00

BRUT :

<dt>Lundi : </dt> <dd>Fermé</dd>

<dt>Mardi : </dt> <dd>Fermé</dd>

<dt>Mercredi : </dt><dd>Fermé</dd>

<dt>Jeudi : </dt> <dd>Fermé</dd>

<dt>Vendredi : </dt><dd>Fermé</dd>

<dt>Samedi : </dt> <dd>Fermé</dd>

<dt>Dimanche : </dt><dd>Fermé</dd>


CIBLE : néant

Quel que soit le jour, lorsque la mention fermé apparaît, la formule doit le traduire par du vide, peux importe le format brut.

A noter également que lors de l'extraction, le format brut comprend des retours chariots et des caractère non imprimable que je parviens à supprimer grâce à l'application successives d'une recherche/remplace (010 avec la touche ALT maintenue dans la barre recherche, et du vide la barre remplace) puis la formule =SUPPRESPACE, puis la formule =EPURAGE.

Je vous mets en téléchargement la version la plus avancée de mon travail a date. A voir si cela peut vous fournir les informations qui nous permettraient de résoudre ce problème :)

https://mon-partage.fr/f/oQjqiAim/

Merci mille fois !
Messages postés
13
Date d'inscription
mardi 5 décembre 2017
Statut
Membre
Dernière intervention
5 mars 2018

Rebonjour,

Après revérification, et par rapport à votre question : Que manque-t-il ou qu'est ce qui en trop pour que cela ne corresponde pas au format cible?

En fait, le problème réside dans la différence mac / pc. En ouvrant votre fichier sur mac j'ai ce résultat (exemple pris sur les jours à double tranche horaire) :

2:08:30:12:15–14:30:19:15,3:08:30:12:15–14:30:19:15,4:08:30:12:15–14:30:19:15,5:08:30:19:15, 6:08:30:19:15,7:08:30:19:15,1:09:00:12:15

Alors que sur PC, j'obtiens bien le résultat voulu :

2:08:30:12:15,2:14:30:19:15,3:08:30:12:15,3:14:30:19:15,4:08:30:12:15,4:14:30:19:15,5:08:30:19:15, 6:08:30:19:15,7:08:30:19:15,1:09:00:12:15

Le fichier est pourtant bien ouvert dans les 2 cas au format .XLSM. Il doit donc y avoir une différence de compréhension du VBA entre IOS et Windows...

Voici donc la dernière chose que j'essaie de corriger quand à votre fichier :

2:08:30:19:30,3:08:30:19:30,4:08:30:19:30,5:08:30:19:30,6:08:30:19:30,7:08:30:19:30,1:Fermé

--> Le "1:Fermé" ne devrait pas apparaître est laisser place au vide. Mon manque de connaissance en VBA me bloque afin de corriger cela...

Merci encore

FS
Messages postés
2172
Date d'inscription
lundi 6 mai 2013
Statut
Membre
Dernière intervention
9 novembre 2020
298
Bonsoir,
Je viens de terminer le fichier mais je n'avais pas vu le dernier message de 18h29, je vous envoie quand même le fichier.
https://mon-partage.fr/f/qOhsxLyR/
Les formules sont en colonnes I.
En colonne H j'ai mis une formule pour comparer vos résultats de la colonne G et les miens colonne I.
A l'aide d'un filtre (colonne H), si on ne sélectionne que les "Faux", on trouve 14 enregistrements discordants entre vos résultats et les miens. J'ai mis les cellules en défaut sur fond bleu avec les erreurs en rouge.
Faites des essais avec d'autres extractions et revenez ici si il y a encore des cas non traités. Ne vous inquiétez pas si je ne réponds pas rapidemment, je ne suis pas toujours disponible.
Bonne soirée
Cdlt
Messages postés
13
Date d'inscription
mardi 5 décembre 2017
Statut
Membre
Dernière intervention
5 mars 2018

Bonjour Frenchie83,

Je m'excuse pour ce délai, le projet a été mis en stand-by pendant un moment.

Toujours est-il que la réponse que vous m'avez apporté est optimale et j'ai pu résoudre le problème dans son ensemble grâce à vous.

Je vous remercie et vous invite à passer le ticket en résolu.

Bonne journée,

Cordialement,