Trouver le premier espace situé AVANT le Xème caractère d'une suite

B

baptistec

XLDnaute Nouveau
Bonjour,

D'abord merci infiniment à toutes les personnes qui déjà alimentés ce site (et notamment son forum), qui est une véritable mine d'or ! :)

J'ai besoin de découper le texte positionné dans une cellule en morceaux d'un maximum de 20 caractères. Toute ma difficulté est de réussir à gérer cette césure au niveau des espaces.

Soit le texte de ma cellule A1 :
Le tas de riz tentant tenta le rat tenté qui tâta le tas de riz tentant.

Avec la formule LEFT(A1,20) en cellule A2, je récupère les 20 premiers caractères de la cellule A1, à savoir :
Le tas de riz tentan

Mais j'aimerai en fait récupérer au maximum les 20 premiers caractères de la cellule A1 avant le dernier espace, donc en fait obtenir dans mon exemple :
Le tas de riz

Le problème est de calculer la position du dernier espace dans une série :
SEARCH(" ",A1,1) donne "3" car le premier espace de la série est situé troisième position.
SEARCH(" ",A1,20) donne "22" car le premier espace de la série situé après le 20ème caractère est placé en vingt-deuxième position.

J'ai en fait besoin de trouver le premier espace situé avant le 20ème caractère.

J'ai trouvé ici un script permettant de faire une sorte de césure, mais n'y-a il pas une formule pour ça ?

Merci d'avance de votre aide.
 
Trier par date Trier par votes
JNP

JNP

XLDnaute Barbatruc
Re : Trouver le premier espace situé AVANT le Xème caractère d'une suite

Re :),
Tant qu'à faire, à condition d'accepter le retour à la ligne dans les cellules, une découpe automatique dans la même case sur plusieurs lignes :rolleyes:...
Code: 
Function Découpe(Chaine As String, NbreCaractères As Integer) As String
Dim Match, Matches
Application.Volatile
Chaine = " " & Chaine & " "
With CreateObject("vbscript.regexp")
    .Global = True
    .Pattern = " .{1," & NbreCaractères & "}(?= )"
    Set Matches = .Execute(Chaine)
    For Each Match In Matches
        Découpe = Découpe & Match & Chr(10)
    Next
End With
Découpe = Left(Découpe, Len(Découpe) - 1)
End Function
Bonne journée :cool:
 
Vote positif 0 Vote négatif
job75

job75

XLDnaute Barbatruc
Re : Trouver le premier espace situé AVANT le Xème caractère d'une suite

Bonjour Jean-Noel, le fil, le forum,

Un bref passage depuis le midi...

RegExp n'est pas trop facile, alors une solution avec InStrRev :

Code: 
Function Ventile$(texte$, N%, rang%)
Dim i%, deb%, fin%
Application.Volatile
texte = Application.Trim(texte) & " "
For i = 1 To rang
  deb = fin + 1
  fin = deb + N - 1
  fin = InStrRev(Left(texte, fin), " ")
Next
Ventile = Trim(Mid(texte, deb, fin - deb + 1))
End Function

A+
 

Pièces jointes

  • Ventile(1).xls
    33 KB · Affichages: 63
Vote positif 0 Vote négatif
job75

job75

XLDnaute Barbatruc
Re : Trouver le premier espace situé AVANT le Xème caractère d'une suite

Bonsoir,

Dans la macro précédente, le nombre de caractères était en fait limité à N - 1 au lieu de N.

Par ailleurs Application.Volatile semble bien inutile.

Donc utiliser :

Code: 
Function Ventile$(texte$, N%, rang%)
Dim i%, deb%, fin%
texte = Application.Trim(texte) & " "
For i = 1 To rang
  deb = fin + 1
  fin = InStrRev(Left(texte, deb + N), " ")
Next
Ventile = Trim(Mid(texte, deb, fin - deb + 1))
End Function
Nota : il faudrait aussi faire un traitement pour éviter qu'un texte entre parenthèses soit coupé...

A+
 

Pièces jointes

  • Ventile(2).xls
    37.5 KB · Affichages: 59
  • Ventile(2).xls
    37.5 KB · Affichages: 56
  • Ventile(2).xls
    37.5 KB · Affichages: 56
Vote positif 0 Vote négatif
JNP

JNP

XLDnaute Barbatruc
Re : Trouver le premier espace situé AVANT le Xème caractère d'une suite

Re :),
job75 à dit:
Par ailleurs Application.Volatile semble bien inutile.
Cliquez pour agrandir...
Dixit l'aide d'Excel
Cette méthode marque comme volatile une fonction personnalisée. Une fonction volatile doit être recalculée chaque fois qu'un calcul est effectué dans une cellule quelconque de la feuille de calcul
Cliquez pour agrandir...
Après, j'aurais plus aprécié une réponse à mon MP, qu'une simplification de code, mais bon, c'est toi qui voit :rolleyes:...
Bonne soirée :cool:
 
Vote positif 0 Vote négatif
job75

job75

XLDnaute Barbatruc
Re : Trouver le premier espace situé AVANT le Xème caractère d'une suite

Re Jean-Noel,

Excuse-moi, je n'avais pas vu ton MP.

Une fonction non volatile est recalculée quand l'un de ses arguments est modifié.

Cela paraît suffisant ici.

A+
 
Vote positif 0 Vote négatif
JNP

JNP

XLDnaute Barbatruc
Re : Trouver le premier espace situé AVANT le Xème caractère d'une suite

Re :),
job75 à dit:
Excuse-moi, je n'avais pas vu ton MP.
Cliquez pour agrandir...
C'est moi qui m'excuse, j'étais un peu de mauvais poil hier soir :eek:...
C'est vrai que dans ce cas précis, il ne devrait pas y avoir de mise à jour ;).
Mais je me suis fait avoir une fois (avec des résultats erronés du coup :mad:) sur une fonction qui ne s'était pas mise à jour, et depuis, je mets systématiquement un Volatile pour ne pas être pris de nouveau :rolleyes:...
Bonnes vacances :cool:
 
Vote positif 0 Vote négatif
job75

job75

XLDnaute Barbatruc
Re : Trouver le premier espace situé AVANT le Xème caractère d'une suite

Bonjour Jean-Noel, le fil, le forum,

job75 à dit:
Nota : il faudrait aussi faire un traitement pour éviter qu'un texte entre parenthèses soit coupé...
Cliquez pour agrandir...

Le traitement est assez simple, mais il ne faut pas de parenthèses imbriquées :

Code: 
Function Ventile$(texte$, N%, rang%)
Dim i%, deb%, fin%
texte = Application.Trim(texte) & " "
For i = 1 To rang
  deb = fin + 1
  fin = InStrRev(Left(texte, deb + N), " ")
  While InStrRev(Left(texte, fin), "(") _
    > InStrRev(Left(texte, fin), ")")
    fin = InStrRev(Left(texte, fin - 1), " ")
  Wend
Next
Ventile = Trim(Mid(texte, deb, fin - deb + 1))
End Function
En cellule E2 les textes entre parenthèses ne sont pas coupés.

A+
 

Pièces jointes

  • Ventile(3).xls
    38.5 KB · Affichages: 38
  • Ventile(3).xls
    38.5 KB · Affichages: 39
  • Ventile(3).xls
    38.5 KB · Affichages: 40
Vote positif 0 Vote négatif
CISCO

CISCO

XLDnaute Barbatruc
Re : Trouver le premier espace situé AVANT le Xème caractère d'une suite

Bonjour

Une variante en formule en pièce jointe, en ligne 2.

Ma précédente proposition n'était pas polyvalente (le dernier espace avant le 30ème caractère devait être en position 23, 24...ou 30. En position 21 ou 22, cela ne fonctionnait pas).

La nouvelle formule est plus courte, mais plus compliquée à comprendre. Ce coup ci, en B2, la formule matricielle recherche la position du dernier espace dans les 30 premiers caractères, et affiche les x caractères jusqu'à cet espace.

En C2, la formule matricielle utilise le même principe, mais avec le texte obtenu grace à SUBSTITUE(A2;B2;"").

@ plus
 

Pièces jointes

  • concatenerter.xlsx
    10.2 KB · Affichages: 49
  • concatener2003.xls
    20 KB · Affichages: 43
Dernière édition:
Vote positif 0 Vote négatif
job75

job75

XLDnaute Barbatruc
Re : Trouver le premier espace situé AVANT le Xème caractère d'une suite

Bonjour CISCO, le fil, le forum,

Je suis sur un ordi d'emprunt avec Excel 2003, je ne peux donc pas voir ton fichier.

Désolé si je te fais concurrence avec cette solution.

Voir les définitions des noms N texte deb fin matrice.

Bien sûr il y a quelques ressemblances avec la solution par macro.

Nota : pas essayé ici de trouver une solution pour les parenthèses...

A+
 

Pièces jointes

  • Ventile par formule(1).xls
    34 KB · Affichages: 42
Dernière édition:
Vote positif 0 Vote négatif
JNP

JNP

XLDnaute Barbatruc
Re : Trouver le premier espace situé AVANT le Xème caractère d'une suite

Re :),
CISCO à dit:
La nouvelle formule est plus courte, mais plus compliquée à comprendre. Ce coup ci, en B2, la formule matricielle recherche la position du dernier espace dans les 30 premiers caractères, et affiche les x caractères jusqu'à cet espace.
Cliquez pour agrandir...
Plus compliquée, certes, mais efficace : Bravo ;)!
Par contre, il est parti où le Mitou :confused: ?
Bonne soirée :cool:
 
Vote positif 0 Vote négatif
CISCO

CISCO

XLDnaute Barbatruc
Re : Trouver le premier espace situé AVANT le Xème caractère d'une suite

Bonsoir

Je viens de rajouter sur mon précédent post une version enregistrée sous excel 2003... Ca peut toujours servir.

@ plus
 
Dernière édition:
Vote positif 0 Vote négatif
JNP

JNP

XLDnaute Barbatruc
Re : Trouver le premier espace situé AVANT le Xème caractère d'une suite

Re :),
job75 à dit:
Apparemment il s'agit de la Mitou :)
Ah les femmes...
Cliquez pour agrandir...
Mon pauvre Job (désolé, j'ai pas pu m'en empêcher :eek:...), quand je vois le nombre de post où un homme à espéré avoir été claire, je ne suis pas encore convaincu que la Mitou ne soit pas un matou :p...
N'apporte rien au fil, mais vu que celui-ci est démultiplié :rolleyes:...
Bonne fin de journée :cool:
 
Vote positif 0 Vote négatif

Discussions similaires

C
  • Question
Trouver le 6e mot/chiffre d'une phrase
Réponses
8
Affichages
1 K
JHA
J
S
  • Résolu(e)
XL 2019 code vba changement de couleur si modification de la cellule
Réponses
7
Affichages
1 K
syriak
S
C
  • Question
XL 2013 Comment trouver le 2e chiffre d'une suite de chiffre à l'aide d'une formule (sans VBA)
Réponses
8
Affichages
925
JFL_XLD
J
R
  • Question
XL 2016 Compter le nombre max d'une chaine de caractère, d'une même valeur
Réponses
9
Affichages
976
robertduval
R
J
  • Question
Aide obtenir chaîne caractères avant ou après Xe espace dans une cellule
Réponses
2
Affichages
1 K
joce9
J
S
  • Résolu(e)
XL 2016 Extraire un chiffre d'un nom de produits
Réponses
7
Affichages
1 K
Seven
S
chris6999
  • Question
Fonction pour récupérer une chaine de caractère
Réponses
4
Affichages
2 K
chris6999
chris6999
F
  • Question
XL 2016 Nombre d'espace avant le premier caractère dans une cellule
Réponses
13
Affichages
3 K
phlaurent55
phlaurent55
R
  • Question
Insertion d'un caractère suite à l'utilisation de la barre d'espacement
Réponses
4
Affichages
766
Renaud22
R
Niquido
  • Question
extraire les caractères d'une cellule avant et/ou après une date
Réponses
2
Affichages
1 K
Niquido
Niquido

Membres actuellement en ligne

2 membres et 30 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
312 505
Messages
2 089 071
Membres
104 020
dernier inscrit
Mzghal