Discussion :

[RyuAutodidacte]

ok je vais voir de mon coté, je retravaille la fonction ...

[patricktoulon]

re
ok il ne reste plus que rtrim a corriger

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
Function ChangeAllCellpropertiesInRange(ByRef RnG As Range, prop As String)
    Dim R As Variant, Addr
    With RnG
        Addr = "'" & .Parent.Name & "'!" & .Address
        Select Case UCase(prop)
        Case "LOWER", "UPPER", "PROPER", "APPTRIM":
            prop = Replace(UCase(prop), "APPTRIM", "TRIM")
            R = Evaluate("IF(ISTEXT(" & Addr & ")," & UCase(prop) & "(" & Addr & "),REPT(" & Addr & ",1))")
        Case "LTRIM": R = Evaluate("IF(ISTEXT(" & Addr & "),MID(" & Addr & ",FIND(MID(TRIM(" & Addr & "),1,2)," & Addr & ",1),LEN(" & Addr & ")),REPT(" & Addr & ",1))")
        Case "RTRIM": R = Evaluate("IF(ISTEXT(" & Addr & "),MID(" & Addr & ",1,FIND(TRIM(RIGHT(SUBSTITUTE(TRIM(" & Addr & "), "" "", REPT("" "", 100)), 100))," & Addr & ",1)+LEN(TRIM(RIGHT(SUBSTITUTE(TRIM(" & Addr & "), "" "", REPT("" "", 100)), 100)))-1),REPT(" & Addr & ",1))")
 
        Case "TRIM": .Value = Evaluate("IF(ISTEXT(" & Addr & "),MID(" & Addr & ",FIND(MID(TRIM(" & Addr & "),1,2)," & Addr & ",1),LEN(" & Addr & ")),REPT(" & Addr & ",1))")
            R = Evaluate("IF(ISTEXT(" & Addr & "),MID(" & Addr & ",1,FIND(TRIM(RIGHT(SUBSTITUTE(TRIM(" & Addr & "), "" "", REPT("" "", 100)), 100))," & Addr & ",1)+LEN(TRIM(RIGHT(SUBSTITUTE(TRIM(" & Addr & "), "" "", REPT("" "", 100)), 100)))-1),REPT(" & Addr & ",1))")
        End Select
    End With
    ChangeAllCellpropertiesInRange = R
End Function
 
Sub test()
    Dim DL, RnG As Range
    DL = Cells(Rows.Count, 3).End(xlUp).Row
    Set RnG = Sheets(1).Range("C2:C" & DL)
    'RnG.Parent.Activate
    RnG.Value = ChangeAllCellpropertiesInRange(RnG, "rtrim")    'majuscule ou minuscule l'argument de propertie
End Sub

par contre sans activer la feuille NADA !!!!!

[patricktoulon]

Ah!!! je viens de remarquer quelque chose ryu

quand je suis sur une autre feuille avec Addr ca modifie que la cells(1) de la RnG
ca peut peut etre t'aiguiller sur la modif a faire dans ta formule

[eriiic]

Bonjour,

Juste une interrogation en passant.
Quand je vois la longueur de la formule pour Rtrim, il faudra peut-être contrôler que ce ne soit pas plus lent que de poser la fonction seule suivi d'un .Value=.Value.
eric

[mercatog]

re

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
Sub test()
    Dim DL, RnG As Range
    DL = Sheets(1).Cells(Rows.Count, 3).End(xlUp).Row
    Set RnG = Sheets(1).Range("C2:C" & DL)
    'RnG.Parent.Activate
    RnG.Value = ChangeAllCellpropertiesInRange(RnG, "rtrim")    'majuscule ou minuscule l'argument de propertie
End Sub

par contre sans activer la feuille NADA !!!!!

[patricktoulon]

re
oui Mercatog
plus précisément avec Addr dument construit quand on est pas sur la feuille concernée seule la cellule1 de la plage de référence est modifié (voir demo animée plus haut)

testé sur 2007 et 2013

[mercatog]

Re Patrick

Sur ta démos, ta variable DL pointe sur la feuille active.

Regardes ce que j'ai ajouté en rouge.

[patricktoulon]

OupsssTU A RAISON !!!
la fatigue


bon ben il ne reste plus que "Rtrim" a corriger et on aura tout bon pour:
Ltrim
Rtrim
Trim
Apptrim(equivalent de application.trim pour VBA)
LOWER
UPPER
PROPER

je suis entrain d'en étudier d'autre

[RyuAutodidacte]

Re,

C'est ce que j'ai constaté dans mes 1er tests, je n'ai eu que RTrim comme problème, là pas fini, je re dès que je peux

[patricktoulon]

re
oui exact seul rtrim pose problème avec Addr dynamique avec ou pas et le sheets concerné soit actif ou pas

[RyuAutodidacte]

oui exact seul rtrim pose problème avec Addr dynamique avec ou pas et le sheets concerné soit actif ou pas

Ha non, comme je l'ai fait, le RTRIM, avec la Formule initiale (pas de ADR) sur la feuille active, ça marche

[patricktoulon]

re
oui mais le fait d'être limité ou obligé d'être sur le sheets concerné limite les possibilité d'exploit
tu tiens le bon bout tu va m'en sortir une de chez pétaouchnoc et on pourra enfin finir l'écriture de la fonction chacun sa façon

[RyuAutodidacte]

En Attendant :

Voilà ou j'en suis (je préfère que ça soit la fonction qui fasse le travaille :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
Sub test()
Dim Rng As Range, DL As Long, T!
    T = Timer
    With Sheets("Feuil2")
        DL = .Cells(.Rows.Count, 3).End(xlUp).Row
        Set Rng = .Range("C2:C" & DL)
    End With
    TULP_LT_RT_CellsInRange Rng, , , True ' => Range, Array(0, 1, 2, 3) (=>pour Param :"TRIM", "UPPER", "LOWER", "PROPER"), True, True
    MsgBox Format(Timer - T, "0.000 s")
End Sub

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Function TULP_LT_RT_CellsInRange(ByRef Rng As Range, Optional Param As Variant = Empty, Optional LT As Boolean, Optional RT As Boolean)
Dim Adr As String
    ChangeTxT = Array("TRIM", "UPPER", "LOWER", "PROPER")
    Application.ScreenUpdating = False
    With Rng
        Adr = "'" & .Parent.Name & "'!" & .Address
        If Not IsEmpty(Param) Then
            For i = LBound(Param) To UBound(Param)
                .Value = Evaluate("IF(ISTEXT(" & Adr & ")," & ChangeTxT(Param(i)) & "(" & Adr & "),REPT(" & Adr & ",1))")
            Next
        End If
 
        If LT Then .Value = Evaluate("IF(ISTEXT(" & Adr & "),MID(" & Adr & ",FIND(MID(TRIM(" & Adr & "),1,2)," & Adr & ",1),LEN(" & Adr & ")),REPT(" & Adr & ",1))")
        If RT Then .Value = Evaluate("IF(ISTEXT(" & Adr & "),MID(" & Adr & ",1,FIND(TRIM(RIGHT(SUBSTITUTE(TRIM(" & Adr & "), "" "", REPT("" "", 100)), 100))," & Adr & ",1)+LEN(TRIM(RIGHT(SUBSTITUTE(TRIM(" & Adr & "), "" "", REPT("" "", 100)), 100)))-1),REPT(" & Adr & ",1))")
 
    End With
    Application.ScreenUpdating = True
End Function

Je cherche une formule me permettant soit de compter le nombre d'espace en fin de texte, soit une formule me permettant de faire comme StrReverse (à condition qu'elle ne soit pas trop compliquée) ... si on peut trouver se serait pas mal
fais signe si tu trouves

[patricktoulon]

je ne sais pas si ca peut t'aider mais la fonction trouve peut te donner l'emplacement du dernier " " a partir de la droite
pour cela il te suffit de couper la chaine en 2

exemple en A1 j'ai '***AAAAAAA*****" (les Etoiles représentent des espaces )

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=TROUVE(" ";A1;NBCAR(A1)/2)

)

[RyuAutodidacte]

non ça m'aide pas car qd il y a plusieurs mots, c'est faux :

vois sur quoi je teste : "***djio****jioj*dfsdf f9****dfidoijf*****"
vois tu tous les espaces, he oui je choisi l’Extrême (pour les espaces) afin que le résultat soit bon à chaque fois

Edit : j'ai trouvé cette formule pour le nombre d'espace à gauche :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=TROUVE(GAUCHE(SUPPRESPACE(C19); 1); C19)-1

faudrait trouvé pour la droite sa simplifierai les formules

Edit 2 : le problème c'est que les formules travaillent de gauche à droite et non de droite à gauche d'où la complication (contrairement à vba)

[mercatog]

Juste on peut utiliser aussi

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Adr = .Address(External:=True)

[RyuAutodidacte]

Bonjour à tous,

@mercatog : , par contre je ne pense pas l’utiliser pour l’instant, me focalisant surtout sur la problématique de la formule du RTrim …

Concernant la recherche de formule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=STXT(A1;{10;9;8;7;6;5;4;3;2;1};1)

Y a-t-il moyen de faire une formule qui marche ressemblant dans le principe à celle du dessus,
dont le 2ème argument serait qq chose du style NBCAR(A1) vers 1, ou autre y ressemblant
afin de faire comme un StrReverse de vba ??

[Vba14]

Bonjour à tous,
Eh les gars vous dormez de temps en temps

Est-ce que cette formule pourrez faire l'affaire ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=SOMME(NBCAR(A2),NBCAR(A3),NBCAR(A4))

Il y a tellement de possibilités avec NBCAR :

Imaginons le libellé suivant en cellule A1 : Les olives du jardin

La formule NBCAR(A1) renvoie 20 (17 lettres + 3 espaces = 20 caractères)


Associée à d'autres fonctions, NBCAR() permet de calculer par exemple:

• Le nombre d'espaces:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=NBCAR(A1)-NBCAR(SUBSTITUE(A1;" ";""))

Dans cette formule, la fonction SUBSTITUE() remplace les espaces par du vide, encadrée par NBCAR() nous avons donc le nombre de caractères de la chaîne une fois les espaces éliminés, il suffit de retrancher ce résultat du nombre de caractères total de la chaîne pour avoir par différence le nombre d'espaces.


• Le nombre de mots:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=NBCAR(A1)-NBCAR(SUBSTITUE(A1;" ";""))+1

• ou encore le nombre de caractères différents de la lettre i:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=NBCAR(SUBSTITUE(A1;"i";""))

Il est également possible de calculer le nombre de caractères d'une plage de cellules


Il faut pour cela utiliser une formule matricielle (donc validée simultanément par les touches MAJ+CTRL+ENTER)


• Par exemple, nous avons en A1 le libellé Pommes et en A2 le libellé Poires:

La formule pour compter le nombre de caractères en A1 et A2 est :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

{=SOMME(NBCAR(A1:A2))}

• Pour compter le nombre de fois où apparaît la lettre o minuscule:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

{=SOMME(NBCAR(A1:A2)-NBCAR(SUBSTITUE(A1:A2;"o";"")))}

Il conviendrait de diviser ce total par le nombre de caractères cherchés, mais NBCAR("o") étant égal à 1 cette opération est inutile.


• Ou encore le nombre de fois où apparaît la chaîne de caractères Po :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

{=SOMME(NBCAR(A1:A2)-NBCAR(SUBSTITUE(A1:A2;"Po";"")))/NBCAR("Po")}

[RyuAutodidacte]

Bonjour Vba14,

Non c’est pas ok, le but est de pourvoir connaître le nombre d’espaces en fin de texte dans une cellule ou d’inverser le texte comme un StrReverse en vba, mais tout ça en formule …

[eriiic]

Bonjour à tous,

A2 : chaine
B2 : caractère
C2 : start (=NBCAR(A2) si depuis fin de chaine)

InstrRev(A2,B2,C2) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=MAX(SI(STXT(A2;LIGNE(INDIRECT("$1:$"&C2));1)=B2;LIGNE(INDIRECT("$1:$"&C2))))

Position dernier caractère <> " " :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=MAX(SI(STXT(A2;LIGNE(INDIRECT("$1:$"&NBCAR(A2)));1)<>" ";LIGNE(INDIRECT("$1:$"&NBCAR(A2)))))

Toutes deux matricielles.
eric