Formule SUBSTITUE, SUBSTITUE IF

Résolu/Fermé

FormulaSeeker Messages postés 13 Date d'inscription mardi 5 décembre 2017 Statut Membre Dernière intervention 5 mars 2018 - 16 janv. 2018 à 12:40
FormulaSeeker Messages postés 13 Date d'inscription mardi 5 décembre 2017 Statut Membre Dernière intervention 5 mars 2018 - 5 mars 2018 à 16:34

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

A voir également:

Formule SUBSTITUE, SUBSTITUE IF
Formule excel - Guide
Formule si et - Guide
Formule moyenne excel - Guide
Formule excel pour additionner plusieurs cellules - Guide
Mise en forme conditionnelle formule si - Guide

7 réponses

Réponse 1 / 7

Frenchie83 Messages postés 2240 Date d'inscription lundi 6 mai 2013 Statut Membre Dernière intervention 11 août 2023 337
16 janv. 2018 à 18:50

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

Réponse 2 / 7

Frenchie83 Messages postés 2240 Date d'inscription lundi 6 mai 2013 Statut Membre Dernière intervention 11 août 2023 337
16 janv. 2018 à 19:06

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

Réponse 3 / 7

gbinforme Messages postés 14946 Date d'inscription lundi 18 octobre 2004 Statut Contributeur Dernière intervention 24 juin 2020 4 684
Modifié le 16 janv. 2018 à 19:08

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

Réponse 4 / 7

Frenchie83 Messages postés 2240 Date d'inscription lundi 6 mai 2013 Statut Membre Dernière intervention 11 août 2023 337
16 janv. 2018 à 20:32

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

FormulaSeeker Messages postés 13 Date d'inscription mardi 5 décembre 2017 Statut Membre Dernière intervention 5 mars 2018
17 janv. 2018 à 09:54

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

Vous n’avez pas trouvé la réponse que vous recherchez ?

Posez votre question

Réponse 5 / 7

Frenchie83 Messages postés 2240 Date d'inscription lundi 6 mai 2013 Statut Membre Dernière intervention 11 août 2023 337
20 janv. 2018 à 06:18

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

FormulaSeeker Messages postés 13 Date d'inscription mardi 5 décembre 2017 Statut Membre Dernière intervention 5 mars 2018
21 janv. 2018 à 17:50

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

Réponse 6 / 7

Frenchie83 Messages postés 2240 Date d'inscription lundi 6 mai 2013 Statut Membre Dernière intervention 11 août 2023 337
21 janv. 2018 à 18:48

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

FormulaSeeker Messages postés 13 Date d'inscription mardi 5 décembre 2017 Statut Membre Dernière intervention 5 mars 2018
22 janv. 2018 à 17:13

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 !

FormulaSeeker Messages postés 13 Date d'inscription mardi 5 décembre 2017 Statut Membre Dernière intervention 5 mars 2018
22 janv. 2018 à 18:29

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

Réponse 7 / 7

Frenchie83 Messages postés 2240 Date d'inscription lundi 6 mai 2013 Statut Membre Dernière intervention 11 août 2023 337
22 janv. 2018 à 20:13

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

FormulaSeeker Messages postés 13 Date d'inscription mardi 5 décembre 2017 Statut Membre Dernière intervention 5 mars 2018
5 mars 2018 à 16:34

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,

Discussions similaires

exo pix

Formule Excel : "SI" une cellule contient un mot, alors ?

je veut créer des codes

Fonction if exist et else

Discussions similaires

Newsletters