XL 2016 Extraction de la partie centrale de texte alphanumérique de cellules placées en colonne B

[Webperegrino]

Bonjour Le Forum,

Après quelques heures de recherches et d'amalgame de parties de formule j'ai réussi à produire des extraction de partie de contenu de cellules de colonne B dans le fichier ci-joint (elles sont construites selon le principe qui figure dans les deux première cellules en vertical).

Toutefois je suis devant un obstacle que je n'arrive pas à résoudre.

Dans la colonne B : en plus des lieux et de leurs secteurs j'ai des Horaires à UNE PLAGE ou à DEUX PLAGES.

Comment généraliser la formule qui est en C3 qui n'a qu'une plage horaire… (… avec le résultat des détails que je trouve en K3)

... pour qu'elle fonctionne aussi en cellule C4 (copié collé) pour afficher le résultat à double horaire désiré… (… avec le résultat des détails que je trouve en H4)

J'ai la formule C3 qui donne bien le résultat obtenu en K3 après décomposition.

Comment glisser en C4 une adaptation de cette formule selon la progression E4 - H4 ?

C'est à ce niveau que je bloque.
Cordialement,
Webperegrino

 

[JHA]

Bonjour à tous,

Un début de piste

=STXT(B3;CHERCHE("??:?? - ??:??";B3)-1;14)
&" "&
SIERREUR(STXT(SUBSTITUE(B3;STXT(B3;CHERCHE("??:?? - ??:??";B3)-1;14);"");CHERCHE("??:?? - ??:??";SUBSTITUE(B3;STXT(B3;CHERCHE("??:?? - ??:??";B3)-1;14);""))-1;14);"")

JHA

 

[Webperegrino]

Le Forum,
Bonsoir JHA,
Votre "début de piste" est en réalité une véritable solution, dans la mesure où je vais pouvoir parvenir à supprimer le retour à la ligne qu'il reste à l'affichage du résultat dans la cellule où se trouve la belle formule.
J'ai essayé avec SUPPRESPACE() mais pour le moment ce n'est pas concluant.
Merci pour votre aide, je suis tout près du but.
Cordialement,
Webperegrino

 

[Webperegrino]

Le Forum,
JHA,
J'ai trouvé, grâce à vous ça fonctionne parfaitement !
Merci encore une fois.
Cordialement,
Webperegrino

 

[Webperegrino]

Le Forum,
JHA,
Hélas, faux espoir : la formule que j'ai voulu adapter m'enlève tous les retours à la ligne et non le premier seulement (quant il y a deux phases horaires).
Webperegrino

....
Eureka !
Je suis parti du principe =SI(GAUCHE(C4;1)=CAR(10);DROITE(C4;NBCAR(C4)-1);C4) ... en mettant à la place de 'C4' votre belle formule.
Ça donne le résultat souhaité dans cette dernière version qui me convient parfaitement.
Bonne soirée,
Merci JHA, j'ai encore progressé en VBA.
Cordialement,
Webperegrino

 

[Webperegrino]

Le forum,
JHA,
J'ai complété mon message précédent en y joignant un fichier rectificatif avec une formulation adaptée de la vôtre.
Merci encore JHA,
Webperegrino

 

[Webperegrino]

Bonjour Le FOrume,
Bonjour JHA,
Je reviens ici car la belle formule proposéepar vous en #2 me pose à nouveau problème dans la poursuite de mes travaux.

En effet, je me suis rendu compte, en extrayant le texte du résultat en colonne de droite que :
- 08:00 - 11:00 obtenu présente un espace de cette façon ...
► 08:00 - 11:00blanc
- le double horaire présente aussi au milieu un espace que la formule vient placer ...
► 14:00 - 16:00Blanc
► 17:30 - 21:00

Une rectification de votre formule pourrait-elle supprimer cet espace gênant pour la poursuite des recherches de similitudes sur les horaires avec d'autres cellules qui n'ont justement pas ces deux espaces ?

Merci pour toute solution.

Avec la première j'ai tenté sur le résultat GAUCHE(résultat;13) ; ça peut ainsi fonctionner ;
Avec le double horaire comment reconstituer le retour à la ligne sans cet espace parasite ?
GAUCHE(résultat;13) & "
" & Droite(résultat;13) me paraît un peu tiré par les cheveux ; et comme les miens sont déjà blancs...

Webperegrino

 

[Cousinhub]

Bonjour,
Comme j'avais effectué ces extractions l'autre jour, voici une solution utilisant Power Query.
L'avantage, tu peux avoir autant de créneaux horaires que tu veux dans une même cellule...
Regarde si cela t'intéresse. Pour mettre à jour, clic droit dans la requête, "Actualiser"
Bonne journée, et bon W-E

 

[Webperegrino]

Le Forum,
Bonsoir Cousinhub,
Un Webperegrino, "né à Brest-même", mais exporté à Lorient suite à mariage avec une Lorientaise...
est heureux d'écrire à un autre Brestois !
Ta proposition semble alléchante, mais je n'utilise que des formules et du VBA ; là s'arrête mon évolution dans Excel.
Donc, l'utilisation "Power Query" est complètement inconnue pour moi et pour mon application de plus de 3 Mo.
Je vais toutefois fureter pour voir comment utiliser cette partie inconnue d'Excel et voir si je peux avancer selon ton procédé.
Je n'ai pas trouvé de formule dans ton fichier, annexé en #8, me permettant d'apprécier cette méthode.
Merci
Webperegrino

 

[Webperegrino]

Le Forum,
Non, vraiment je suis perdu pour Power Query. J'utilise Excel 2016.
Webperegrino

 

[job75]

Bonjour le forum,

Si les heures à extraire sont toujours suives par le mot "Groupe" :

=STXT(B3;TROUVE(":";B3)-2;CHERCHE("Groupe";B3)-TROUVE(":";B3)+1)

A+

 

[mapomme]

Bonjour à tous,

Une piste :

=STXT(B3;CHERCHE(":";B3)-2;13)&SIERREUR(CAR(10)&STXT(STXT(B3;CHERCHE(STXT(B3;CHERCHE(":";B3)-2;13);B3)+14;999);CHERCHE(":";STXT(B3;CHERCHE(STXT(B3;CHERCHE(":";B3)-2;13);B3)+14;999))-2;13);"")

 

[job75]

Sinon voyez ce fichier avec la fonction VBA :

Function Extrait(x As String) As String
Dim s, i%
s = Split(x, vbLf)
For i = 0 To UBound(s)
    If s(i) Like "##:## - ##:##" Then Extrait = Extrait & vbLf & s(i)
Next
Extrait = Mid(Extrait, 2)
End Function

 

[Webperegrino]

Bonjour Le Forum,

Bonjour Job75,
Grand merci pour votre proposition que je vais étudier très attentivement.

Je viens de finir d'élaborer une solution créée, en cellules D9 à D11, selon une progression de formules que j'avais placées entre E9 et R9 dans le fichier ci-annexé.
Cela semble fonctionner : j'ai enfin réussi à retirer les espaces inutiles tout en conservant le retour à la ligne quand il y a deux horaires qui se suivent.

Merci Job75, votre dernière proposition me semble plus parfaite que la mienne.
Je l'étudie dans l'après-midi.
Webperegrino

 

[Webperegrino]

Le Forum,
Re-Job75,
Alors là, chapeau bas. !
Avec votre formule #13 nous rentrons dans la Cour des Grands (du Forum) !

J'ai honte avec mon énorme formule qui me donne un résultat équivalent en #14.
Grand merci à vous : je fais un grand pas dans l'Univers d'Excel Download !

Cordialement,
Webperegrino