Comment mettre dans 3 colonnes différentes les éléments d'une adresse postale en repérant ce qui est à gauche et à droite du code postal ?
Plusieurs solutions :
par formule :
=GAUCHE(A1;NBCAR(A1)-EQUIV(VRAI;ESTNUM(CNUM(STXT(A1;NBCAR(A1)-
LIGNE(INDIRECT("1:"&NBCAR(A1)));1)));0)-5)
Valider avec Maj+Ctrl+Entrée
CAR(A1)-EQUIV(VRAI;ESTNUM(CNUM(STXT(A1;NBCAR(A1)-
LIGNE(INDIRECT("1:"&NBCAR(A1)));1)));0)-4;5)
Valider avec Maj+Ctrl+Entrée
=STXT(A1;NBCAR(A1)-EQUIV(VRAI;ESTNUM(CNUM(STXT(A1;NBCAR(A1)-
LIGNE(INDIRECT("1:"&NBCAR(A1)));1)));0)+2;999)
Valider avec Maj+Ctrl+Entrée
En VBA
Une fonction personnalisée : (à mattre dans un module ordinaire, pas dans un module de feuille)
Function Pos_CP(Target)
Application.Volatile
For i = 1 To Len(Target)
If (Mid(Target, i, 9 ^ 9) Like "#####*") = True Then Pos_CP = i: Exit
Function
Next
End Function
ensuite en
en A1 12 rue des Coquelicots 12345 Ville la Grand
B1 =GAUCHE(A1;Pos_Cp(A1)-1)
en C1 =STXT(A1;Pos_Cp(A1);5)
en D1 =STXT(A1;Pos_Cp(A1)+6;99)
ou encore ces trois fonctions à utiliser ensemble ou séparément:
Function CodePostal(chaine)
temp = ""
p = Len(chaine)
Do While Not IsNumeric(Mid(chaine, p, 1)) And p > 1
p = p - 1
Loop
Do While IsNumeric(Mid(chaine, p, 1)) And p > 1
temp = Mid(chaine, p, 1) & temp
p = p - 1
Loop
CodePostal = temp
End Function
Function Ville(chaine)
p = Len(chaine)
Do While Not IsNumeric(Mid(chaine, p, 1)) And p > 1
p = p - 1
Loop
Ville = Mid(chaine, p + 2)
End Function
Function Rue(chaine)
temp = ""
p = Len(chaine)
Do While Not IsNumeric(Mid(chaine, p, 1)) And p > 1
p = p - 1
Loop
Do While IsNumeric(Mid(chaine, p, 1)) And p > 1
temp = Mid(chaine, p, 1) & temp
p = p - 1
Loop
Rue = Left(chaine, p)
End Function
par formule :
=GAUCHE(A1;NBCAR(A1)-EQUIV(VRAI;ESTNUM(CNUM(STXT(A1;NBCAR(A1)-
LIGNE(INDIRECT("1:"&NBCAR(A1)));1)));0)-5)
Valider avec Maj+Ctrl+Entrée
CAR(A1)-EQUIV(VRAI;ESTNUM(CNUM(STXT(A1;NBCAR(A1)-
LIGNE(INDIRECT("1:"&NBCAR(A1)));1)));0)-4;5)
Valider avec Maj+Ctrl+Entrée
=STXT(A1;NBCAR(A1)-EQUIV(VRAI;ESTNUM(CNUM(STXT(A1;NBCAR(A1)-
LIGNE(INDIRECT("1:"&NBCAR(A1)));1)));0)+2;999)
Valider avec Maj+Ctrl+Entrée
En VBA
Une fonction personnalisée : (à mattre dans un module ordinaire, pas dans un module de feuille)
Function Pos_CP(Target)
Application.Volatile
For i = 1 To Len(Target)
If (Mid(Target, i, 9 ^ 9) Like "#####*") = True Then Pos_CP = i: Exit
Function
Next
End Function
ensuite en
en A1 12 rue des Coquelicots 12345 Ville la Grand
B1 =GAUCHE(A1;Pos_Cp(A1)-1)
en C1 =STXT(A1;Pos_Cp(A1);5)
en D1 =STXT(A1;Pos_Cp(A1)+6;99)
ou encore ces trois fonctions à utiliser ensemble ou séparément:
Function CodePostal(chaine)
temp = ""
p = Len(chaine)
Do While Not IsNumeric(Mid(chaine, p, 1)) And p > 1
p = p - 1
Loop
Do While IsNumeric(Mid(chaine, p, 1)) And p > 1
temp = Mid(chaine, p, 1) & temp
p = p - 1
Loop
CodePostal = temp
End Function
Function Ville(chaine)
p = Len(chaine)
Do While Not IsNumeric(Mid(chaine, p, 1)) And p > 1
p = p - 1
Loop
Ville = Mid(chaine, p + 2)
End Function
Function Rue(chaine)
temp = ""
p = Len(chaine)
Do While Not IsNumeric(Mid(chaine, p, 1)) And p > 1
p = p - 1
Loop
Do While IsNumeric(Mid(chaine, p, 1)) And p > 1
temp = Mid(chaine, p, 1) & temp
p = p - 1
Loop
Rue = Left(chaine, p)
End Function
GeeDee, Jacques Boisgontier,
Ajouté ou modifié le 05/01/2008 (N°1957)
Ajouté ou modifié le 05/01/2008 (N°1957)
J'ai des apostrophes comme premier caractère d'une série de cellule ce qui oblige excel à considérer le contenu comme du texte. Comme supprimer cette apostrophe dans toute une colonne ?
Copie ta colonne, ouvre l'éditeur Visual basic (clic droit sur le nom de l'onglet et visualiser le code). Colle le contenu de ton texte dans l'éditeur, les apostrophes diparaissent ! il ne te reste plus qu'à faire l'opération inverse.
Cyril Dupont,
Ajouté ou modifié le 15/12/2007 (N°1946)
Ajouté ou modifié le 15/12/2007 (N°1946)
Dans une chaine de type "premier;second;troisième;quatrième", comment trouver le N° de la
position du second ; ?
Utilise cette petite macro :
Si ta chaine est en A1 de la feuil1:
Sub Trouve()
Dim chaine As String
chaine = [feuil1!A1]
ch1 = Mid(chaine, 1 + InStr(1, chaine, ";"))
ch2 = InStr(1, chaine, ";") + InStr(1, ch1, ";")
MsgBox ch2
End Sub
Si ta chaine est en A1 de la feuil1:
Sub Trouve()
Dim chaine As String
chaine = [feuil1!A1]
ch1 = Mid(chaine, 1 + InStr(1, chaine, ";"))
ch2 = InStr(1, chaine, ";") + InStr(1, ch1, ";")
MsgBox ch2
End Sub
Serge Garneau,
Ajouté ou modifié le 23/04/2005 (N°1611)
Ajouté ou modifié le 23/04/2005 (N°1611)
Comment remplacer dans un texte importé le caractère * par un retour à la ligne ?
Tu sélectionnes ta plage de cellule,
barre des menus / édition / rechercher / remplacer
pour rechercher, tu inscris : ~*
Pour remplacer , tu places le curseur dans la boîte de saisie, et tu tapes 0010 (ou 0013)
en conservant
enfoncée la touche "ALT" à droite de la barre d'espacement enfoncé.
Tu cliques sur remplacer .... Voilà !
barre des menus / édition / rechercher / remplacer
pour rechercher, tu inscris : ~*
Pour remplacer , tu places le curseur dans la boîte de saisie, et tu tapes 0010 (ou 0013)
en conservant
enfoncée la touche "ALT" à droite de la barre d'espacement enfoncé.
Tu cliques sur remplacer .... Voilà !
Denis Michon,
Ajouté ou modifié le 02/04/2005 (N°1601)
Ajouté ou modifié le 02/04/2005 (N°1601)
Par macro, j'entre du texte dans une cellule. Je voudrais que ce texte soit tronqué tous les
100 caractères mais sans couper de mots. Comment faire ?
Le texte de la question ci dessus a été tronqué avec la macro ci dessous :
Function Decoupage(TxTronque As String, LgMax As Integer) As String
Dim i As Integer
Dim p As Integer
Dim FinLigne As Long
p = 1
i = 0
Do While i < Len(TxTronque)
FinLigne = InStr(p, TxTronque, Chr(10))
If FinLigne > LgMax Then
i = i + LgMax
Else: i = FinLigne + LgMax
End If
Do While Mid(TxTronque, i, 1) <> " "
i = i - 1
If i = 0 Then
If FinLigne = 0 Then i = p + LgMax: Exit Do
i = FinLigne + LgMax: Exit Do
End If
Loop
Mid(TxTronque, i, 1) = vbCr
i = i + LgMax
p = i + 1
Loop
Decoupage = TxTronque
End Function
Function Decoupage(TxTronque As String, LgMax As Integer) As String
Dim i As Integer
Dim p As Integer
Dim FinLigne As Long
p = 1
i = 0
Do While i < Len(TxTronque)
FinLigne = InStr(p, TxTronque, Chr(10))
If FinLigne > LgMax Then
i = i + LgMax
Else: i = FinLigne + LgMax
End If
Do While Mid(TxTronque, i, 1) <> " "
i = i - 1
If i = 0 Then
If FinLigne = 0 Then i = p + LgMax: Exit Do
i = FinLigne + LgMax: Exit Do
End If
Loop
Mid(TxTronque, i, 1) = vbCr
i = i + LgMax
p = i + 1
Loop
Decoupage = TxTronque
End Function
Laurent Longre, Flo Cabon, (N°160)
Je voudrais couper une phrase, sans couper les mots, de telle sorte que chaque tronçon de
phrase, mis dans des cellules adjacentes, ne comporte pas plus de 20 caractères.
Public Sub Parse20PerCell()
Dim bigString As String
Dim tempStr As String
Dim
cell As Range
Dim pos As Integer
Set cell = Range("A1")
bigString = cell.Text
Do While bigString <> ""
Set cell = cell.Offset(0, 1)
If Len(bigString) < 21 Then
cell.Value = Trim(bigString)
bigString = ""
Else
tempStr = Right(StrReverse(bigString), 21)
pos = InStr(tempStr, " ")
If pos = 0 Then
MsgBox "More than 20 contiguous characters between spaces."
Else
cell.Value = Trim(StrReverse(Mid(tempStr, pos + 1, 255)))
bigString = Mid(bigString, 22 - pos, 255)
End If
End If
Loop
End Sub
Si tu travailles avec with XL97, XL98 or XL2001, tu as besoin de ta propre
fonction StrReverse. En voici une:
Public Function StrReverse(reverseString
As String) As String
Dim i As Long
For i = Len(reverseString) To 1 Step -1
StrReverse = StrReverse & Mid(reverseString, i, 1)
Next i
End Function
***************************************
Autre solution :
Le nombre maximal de caractères de la phrase est dans cet exemple supposé être
inférieur à 1000 et le nombre de cellules dans lesquelles le texte est réparti
est supposé être inférieur à 100.
Sub test1()
Set rng = Sheets(1).Range("A1")
iTotal = Mid(rng, k + 1, 1000) & " "
For j = 2 To 100
For i = 21 To 1 Step -1
If Mid(iTotal, i, 1) = " " Then
k = i
Exit For
End If
Next
rng(1, j).Value = Mid(iTotal, 1, k - 1)
iTotal = Mid(iTotal, k + 1, 1000)
Next
End Sub
(traduc Flo Cabon)
Si tu travailles avec with XL97, XL98 or XL2001, tu as besoin de ta propre
fonction StrReverse. En voici une:
Public Function StrReverse(reverseString
As String) As String
Dim i As Long
For i = Len(rev
Dim bigString As String
Dim tempStr As String
Dim
cell As Range
Dim pos As Integer
Set cell = Range("A1")
bigString = cell.Text
Do While bigString <> ""
Set cell = cell.Offset(0, 1)
If Len(bigString) < 21 Then
cell.Value = Trim(bigString)
bigString = ""
Else
tempStr = Right(StrReverse(bigString), 21)
pos = InStr(tempStr, " ")
If pos = 0 Then
MsgBox "More than 20 contiguous characters between spaces."
Else
cell.Value = Trim(StrReverse(Mid(tempStr, pos + 1, 255)))
bigString = Mid(bigString, 22 - pos, 255)
End If
End If
Loop
End Sub
Si tu travailles avec with XL97, XL98 or XL2001, tu as besoin de ta propre
fonction StrReverse. En voici une:
Public Function StrReverse(reverseString
As String) As String
Dim i As Long
For i = Len(reverseString) To 1 Step -1
StrReverse = StrReverse & Mid(reverseString, i, 1)
Next i
End Function
***************************************
Autre solution :
Le nombre maximal de caractères de la phrase est dans cet exemple supposé être
inférieur à 1000 et le nombre de cellules dans lesquelles le texte est réparti
est supposé être inférieur à 100.
Sub test1()
Set rng = Sheets(1).Range("A1")
iTotal = Mid(rng, k + 1, 1000) & " "
For j = 2 To 100
For i = 21 To 1 Step -1
If Mid(iTotal, i, 1) = " " Then
k = i
Exit For
End If
Next
rng(1, j).Value = Mid(iTotal, 1, k - 1)
iTotal = Mid(iTotal, k + 1, 1000)
Next
End Sub
(traduc Flo Cabon)
Si tu travailles avec with XL97, XL98 or XL2001, tu as besoin de ta propre
fonction StrReverse. En voici une:
Public Function StrReverse(reverseString
As String) As String
Dim i As Long
For i = Len(rev
JE McGimpsey, Alan Beban, (N°159)
Comment trier par ordre alphabétique croissant ou décroissant une cellule Excel contenant une
chaine de caractères ?
Tu peux utiliser cette fonction de T. Shuttleworth avec quelques modifications :
Option Compare Text
Function SortString(ByVal iRange, Optional Croissant As Boolean = True)
'Trevor Shuttleworth, mpep, modifié fs, mpfe
Dim i%, j%, sTemp$
For j = 1 To Len(iRange) - 1
For i = 1 To Len(iRange) - 1
If Mid(iRange, i, 1) > Mid(iRange, i + 1, 1) Then
sTemp = Mid(iRange, i, 1)
Mid(iRange, i, 1) = Mid(iRange, i + 1, 1)
Mid(iRange, i + 1, 1) = sTemp
End If
Next
Next
If Croissant = False Then
For i = Len(iRange) To 1 Step -1
SortString = SortString & Mid(iRange, i, 1)
Next
Exit Function
End If
SortString = iRange
End Function
Tu peux aussi utiliser cette
solution par formule MATRICIELLE en utilisant la
XLL (morefun.xll) (téléchargeable sur http://longre.free.fr)
{=MCONCAT(TRIV(STXT(A1;SUITE(NBCAR(A1);1);1);;1))}
Option Compare Text
Function SortString(ByVal iRange, Optional Croissant As Boolean = True)
'Trevor Shuttleworth, mpep, modifié fs, mpfe
Dim i%, j%, sTemp$
For j = 1 To Len(iRange) - 1
For i = 1 To Len(iRange) - 1
If Mid(iRange, i, 1) > Mid(iRange, i + 1, 1) Then
sTemp = Mid(iRange, i, 1)
Mid(iRange, i, 1) = Mid(iRange, i + 1, 1)
Mid(iRange, i + 1, 1) = sTemp
End If
Next
Next
If Croissant = False Then
For i = Len(iRange) To 1 Step -1
SortString = SortString & Mid(iRange, i, 1)
Next
Exit Function
End If
SortString = iRange
End Function
Tu peux aussi utiliser cette
solution par formule MATRICIELLE en utilisant la
XLL (morefun.xll) (téléchargeable sur http://longre.free.fr)
{=MCONCAT(TRIV(STXT(A1;SUITE(NBCAR(A1);1);1);;1))}
Frédéric Sigonneau, Laurent Longre, (N°158)
Je souhaite modifier le contenu d'une cellule contenant du texte et des chiffres en supprimant
le texte et en transformant les nombres à virgules. Par exemple : 19.17 (c) à transformer en
19,17.
Variante 1 :
Si pas d'encombrement dans les colonnes d'à côté : Données > Convertir > ...
tu gardes la colonne comprenant les valeurs numériques > Edition remplacer >
Point par Virgule
Variante 2 :
Sub Remplace()
Dim Cel As Range
For Each Cel In Selection
For x = 1 To Len(Cel)
leCar = Mid(Cel, x, 1)
codCar = Asc(Mid(Cel, x, 1))
If codCar >= 48 And codCar <= 57 Or codCar = 44 Or codCar = 46 Then
maChaine = maChaine & leCar
Next x
Range(Cel.Address) = CDbl(maChaine)
maChaine = ""
Next Cel
End Sub
Variante 3 :
Avec ta chaine en A1 : (attention, tout sur la même ligne...)
=CNUM(STXT(SUBSTITUE(A1;".";",");EQUIV(0;(ESTERREUR(STXT(A1;LIGNE(INDIRECT
("1:"&NBCAR(A1)));1)*1)*1);0);NBCAR(A1)+1-SOMME((ESTERREUR(STXT(A1;LIGNE(INDIRECT
("1:"&NBCAR(A1)));1)*1)*1))))
Matricielle (validation par Ctrl+Maj)
PS : pour des nombres non décimaux supprimer le +1
PSbis : valable que pour le séparateur décimal = virgule. sinon, pour
séparateur décimal = point et chaine comprenant une virgule, inverser les
arguments dans SUBSTITUE
Si pas d'encombrement dans les colonnes d'à côté : Données > Convertir > ...
tu gardes la colonne comprenant les valeurs numériques > Edition remplacer >
Point par Virgule
Variante 2 :
Sub Remplace()
Dim Cel As Range
For Each Cel In Selection
For x = 1 To Len(Cel)
leCar = Mid(Cel, x, 1)
codCar = Asc(Mid(Cel, x, 1))
If codCar >= 48 And codCar <= 57 Or codCar = 44 Or codCar = 46 Then
maChaine = maChaine & leCar
Next x
Range(Cel.Address) = CDbl(maChaine)
maChaine = ""
Next Cel
End Sub
Variante 3 :
Avec ta chaine en A1 : (attention, tout sur la même ligne...)
=CNUM(STXT(SUBSTITUE(A1;".";",");EQUIV(0;(ESTERREUR(STXT(A1;LIGNE(INDIRECT
("1:"&NBCAR(A1)));1)*1)*1);0);NBCAR(A1)+1-SOMME((ESTERREUR(STXT(A1;LIGNE(INDIRECT
("1:"&NBCAR(A1)));1)*1)*1))))
Matricielle (validation par Ctrl+Maj)
PS : pour des nombres non décimaux supprimer le +1
PSbis : valable que pour le séparateur décimal = virgule. sinon, pour
séparateur décimal = point et chaine comprenant une virgule, inverser les
arguments dans SUBSTITUE
Alain Vallon, (N°157)
Comment supprimer dans une sélection de cellules tous les caractères autres que les chiffres et
les lettres ?
à copier dans un module standard.
Tu dois adapter le nom de la feuille et de la plage de cellules
de la procédure ou tu veux faire le ménage.
'-----------------------------
Sub SupprimerCaractères()
Dim Rg As Range, A As Integer
Set Rg = Worksheets("Feuil1").Range("A1:C25")
Application.ScreenUpdating = False
For A = 1 To 256
Select Case A
Case 9, 10, 13, 32 To 48, 58 To 64, 91 To 96, 123 To 255
'Pour les Jokers * et ?
If A = 42 Or A = 63 Then
Mot = "~" & Chr(A) & ""
'Pour le caractère tilde (~)
ElseIf A = 126 Then
Mot = "" & Chr(A) & Chr(A) & ""
Else
Mot = "" & Chr(A) & ""
End If
Rg.Replace What:=Mot, Replacement:="", LookAt:=xlPart
End Select
Next
Set Rg = Nothing
End Sub
Tu dois adapter le nom de la feuille et de la plage de cellules
de la procédure ou tu veux faire le ménage.
'-----------------------------
Sub SupprimerCaractères()
Dim Rg As Range, A As Integer
Set Rg = Worksheets("Feuil1").Range("A1:C25")
Application.ScreenUpdating = False
For A = 1 To 256
Select Case A
Case 9, 10, 13, 32 To 48, 58 To 64, 91 To 96, 123 To 255
'Pour les Jokers * et ?
If A = 42 Or A = 63 Then
Mot = "~" & Chr(A) & ""
'Pour le caractère tilde (~)
ElseIf A = 126 Then
Mot = "" & Chr(A) & Chr(A) & ""
Else
Mot = "" & Chr(A) & ""
End If
Rg.Replace What:=Mot, Replacement:="", LookAt:=xlPart
End Select
Next
Set Rg = Nothing
End Sub
Denis Michon,
Ajouté ou modifié le 25/10/2003 (N°156)
Ajouté ou modifié le 25/10/2003 (N°156)
Comment supprimer des espaces dans une sélection ?
Sub Supprime_Espaces()
Dim patente As Range
Application.ScreenUpdating = False
For Each patente In Selection
patente.Replace What:=" ", Replacement:=""
Next
End Sub
Tu peux aussi utiliser SUPPRESPACE
ou TRIM qui supprime les espaces en trop à droite ou à gauche mais pas à
l'intérieur
d'une chaine de texte.
Il y a deux fonctions Trim utilisables en VBA. La fonction "native"
VBA, qui se
contente, de supprimer les espaces à gauche et à droite d'une
chaîne, et la fonction de feuille de calcul, utilisable avec Application.Trim,
qui se comporte exactement comme Supprespace.
Un petit exemple de la différence entre les deux :
Sub test()
S = " Le trou noir "
MsgBox Trim(S)
MsgBox Application.Trim(S)
End Sub
Dim patente As Range
Application.ScreenUpdating = False
For Each patente In Selection
patente.Replace What:=" ", Replacement:=""
Next
End Sub
Tu peux aussi utiliser SUPPRESPACE
ou TRIM qui supprime les espaces en trop à droite ou à gauche mais pas à
l'intérieur
d'une chaine de texte.
Il y a deux fonctions Trim utilisables en VBA. La fonction "native"
VBA, qui se
contente, de supprimer les espaces à gauche et à droite d'une
chaîne, et la fonction de feuille de calcul, utilisable avec Application.Trim,
qui se comporte exactement comme Supprespace.
Un petit exemple de la différence entre les deux :
Sub test()
S = " Le trou noir "
MsgBox Trim(S)
MsgBox Application.Trim(S)
End Sub
Serge Garneau, Frédéric Sigonneau, (N°155)
Tester la présence d'une chaîne de caractère Comment vérifier qu'une chaîne de caractères se trouve dans une cellule ou dans une variable ?
"=NB.SI(A1;"*texte*")=1
Renvoie VRAI si la chaîne de caractères (texte) se trouve dans la cellule A1
Renvoie VRAI si la chaîne de caractères (texte) se trouve dans la cellule A1
ChrisV, (N°154)
Peut-on obliger un utilisateur à saisir un texte en majuscules dans une cellule ?
Dans barre de menus / données / validation /
dans la liste déroulant du haut de la fenêtre : Personnalisé
Pour limiter la saisie aux lettres majuscules dans la cellule A1
Et cette formule : =EXACT(A1;MAJUSCULE(A1))
Tu définis les messages désirés.
Si tu ne veux absolument pas que l'usager puisse valider l'entrée
de la cellule...Choisir dans l'onglet Alerte d'erreur, dans la liste
déroulant " Arrêt"
dans la liste déroulant du haut de la fenêtre : Personnalisé
Pour limiter la saisie aux lettres majuscules dans la cellule A1
Et cette formule : =EXACT(A1;MAJUSCULE(A1))
Tu définis les messages désirés.
Si tu ne veux absolument pas que l'usager puisse valider l'entrée
de la cellule...Choisir dans l'onglet Alerte d'erreur, dans la liste
déroulant " Arrêt"
Denis Michon,
Ajouté ou modifié le 25/10/2003 (N°153)
Ajouté ou modifié le 25/10/2003 (N°153)
Comment faire pour remplacer dans unevariable un point virgule par une virgule ? Par exemple,
si en A1 j'ai : "B1;B2;B3" et dans mon code, MaVariable=range("A1").value,
comment faire le remplacement dans MaVariable sans toucher à la cellule A1 ?
Il faut passer par une macro :
Sub replacement()
Dim MaVariable As String
MaVariable = "fdfdfd;fdf;df"
MaVariable = Replace(ZoneCach, ";", ",")
MsgBox MaVariable
End Sub
Attention ceci ne fonctionne pas avec excel 97
Si tu es dans ce cas tu dois utiliser
MaVariable=Application.Substitute(MaVariable, ";", ",")
Sub replacement()
Dim MaVariable As String
MaVariable = "fdfdfd;fdf;df"
MaVariable = Replace(ZoneCach, ";", ",")
MsgBox MaVariable
End Sub
Attention ceci ne fonctionne pas avec excel 97
Si tu es dans ce cas tu dois utiliser
MaVariable=Application.Substitute(MaVariable, ";", ",")
Frédéric Sigonneau, Pierre Fauconnier, (N°152)
Dans une colonne au format texte quand j'applique la fonction remplacer la valeur suivante
**85 par rien sur un nombre du genre **850005256325 les 0 disparaissent ( 5256325 ). Comment
l'éviter ?
A défaut... Et quand le rechercher remplacer d'excel est trop intelligent ...
Sub remplt()
txtSup = InputBox("Quelle chaîne de caractères" _
& "désirez-vous supprimer ?")
txtRemp = InputBox("Par quoi voulez-vous la remplacer ?")
Application.ScreenUpdating = False
For Each c In Selection
c.Value = Replace(c.Value, txtSup, txtRemp)
Next c
End Sub
********************************
La fonction Replace n'est pas disponible pour la version Excel 97.
Il faut la remplacer par la fonction Substitute
Sub remplt()
txtSup = Application.InputBox("Quelle chaîne de caractères" _
& "désirez-vous supprimer ?")
txtRemp = Application.InputBox("Par quoi voulez-vous la remplacer ?")
Application.ScreenUpdating = False
For Each c In Selection
c.value=Application.Substitute(c.Value, txtSup, txtRemp)
'c.Value = Replace(c.Value, txtSup, txtRemp) 'Non disponible Excel 97
Next c
End Sub
Sub remplt()
txtSup = InputBox("Quelle chaîne de caractères" _
& "désirez-vous supprimer ?")
txtRemp = InputBox("Par quoi voulez-vous la remplacer ?")
Application.ScreenUpdating = False
For Each c In Selection
c.Value = Replace(c.Value, txtSup, txtRemp)
Next c
End Sub
********************************
La fonction Replace n'est pas disponible pour la version Excel 97.
Il faut la remplacer par la fonction Substitute
Sub remplt()
txtSup = Application.InputBox("Quelle chaîne de caractères" _
& "désirez-vous supprimer ?")
txtRemp = Application.InputBox("Par quoi voulez-vous la remplacer ?")
Application.ScreenUpdating = False
For Each c In Selection
c.value=Application.Substitute(c.Value, txtSup, txtRemp)
'c.Value = Replace(c.Value, txtSup, txtRemp) 'Non disponible Excel 97
Next c
End Sub
ChrisV, Denis Michon, (N°151)
Quels sont les différents jokers que l'on peut utiliser dans les fenêtres rechercher remplacer
~? Et ... comment faire pour remplacer "Ca vaut ***" par "Ca vaut +++" ?
* permet de rechercher n'importe quel groupe de caractère alors que ? permet
d'omettre un seul caractère.
Pour rechercher des caractères spéciaux comme *, ~?, ~ il faut les faire
précéder d'un tilde :
~*, ~~, ~?...
d'omettre un seul caractère.
Pour rechercher des caractères spéciaux comme *, ~?, ~ il faut les faire
précéder d'un tilde :
~*, ~~, ~?...
Robert Dezan, (N°150)
Comment supprimer le premier mot d'une phrase ? (séparé du reste par une espace)
Supposons que A1 contient "Bonjour les amis", alors la formule
suivante:
=STXT(A1;CHERCHE(" ";A1;1)+1;NBCAR(A1))
te retourne "les amis".
suivante:
=STXT(A1;CHERCHE(" ";A1;1)+1;NBCAR(A1))
te retourne "les amis".
Laurent Longre, (N°149)
J'ai importé en colonne A d'un classeur des données d'un autre logiciel mais j'ai plein de
petits carrés que je voudrais supprimer.
Tu peux utiliser cette macro pour connaitre les codes des caractères qui génèrent des petits
carrés.
Sub acode()
For i = 1 To 255
Range("a" & i) = Chr(i)
Next
End Sub
Pour remplacer ces caratères par une espace, tu peux alors utiliser cette macro :
Sub Macro1Carrés()
'Isabelle, MPFE 2002
Dim c
For Each c In Range("A1:" & _
Range("A1").SpecialCells(xlCellTypeLastCell).Address)
For i = 1 To 31
Application.StatusBar = c.Address & " " & i
On Error Resume Next
Range(c.Address) = Application.Substitute(c, Chr(i), " ")
'Err.Clear
'Resume
Next
Range(c.Address) = Application.Substitute(c, Chr(127), " ")
Range(c.Address) = Application.Substitute(c, Chr(129), " ")
Range(c.Address) = Application.Substitute(c, Chr(141), " ")
Range(c.Address) = Application.Substitute(c, Chr(143), " ")
Range(c.Address) = Application.Substitute(c, Chr(144), " ")
Range(c.Address) = Application.Substitute(c, Chr(157), " ")
Next
Application.StatusBar = False
End Sub
carrés.
Sub acode()
For i = 1 To 255
Range("a" & i) = Chr(i)
Next
End Sub
Pour remplacer ces caratères par une espace, tu peux alors utiliser cette macro :
Sub Macro1Carrés()
'Isabelle, MPFE 2002
Dim c
For Each c In Range("A1:" & _
Range("A1").SpecialCells(xlCellTypeLastCell).Address)
For i = 1 To 31
Application.StatusBar = c.Address & " " & i
On Error Resume Next
Range(c.Address) = Application.Substitute(c, Chr(i), " ")
'Err.Clear
'Resume
Next
Range(c.Address) = Application.Substitute(c, Chr(127), " ")
Range(c.Address) = Application.Substitute(c, Chr(129), " ")
Range(c.Address) = Application.Substitute(c, Chr(141), " ")
Range(c.Address) = Application.Substitute(c, Chr(143), " ")
Range(c.Address) = Application.Substitute(c, Chr(144), " ")
Range(c.Address) = Application.Substitute(c, Chr(157), " ")
Next
Application.StatusBar = False
End Sub
isabelle, (N°148)
Quel est le nombre maximum de caractères qu'on peut entrer dans une cellule ?
De mémoire, sous XL95, le maximum est de 255 caractères (ou -ce qui est
moins connu- de 1024 caractères s'il s'agit d'une formule). Sous XL2000,
le maximum est passé à 32000 caractères. A+
moins connu- de 1024 caractères s'il s'agit d'une formule). Sous XL2000,
le maximum est passé à 32000 caractères. A+
Laurent Mortézai, (N°147)
J'ai des espaces bizzares dans certaines cellules et je voudrais les supprimer mais je n'y
parviens pas.
Un espace saisi dans une cellule par une barre
d'espacement donne un chr(32). Il arrive lorque l'on
importe des données que cet espace soit un chr(160).
Tu peux essayer d'utiliser ceci :
dim MonRange as range
dim Mot as string
Mot=chr(160)
set MonRange=Range("A1:A50")
MonRange.Replace What:=mot, Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
d'espacement donne un chr(32). Il arrive lorque l'on
importe des données que cet espace soit un chr(160).
Tu peux essayer d'utiliser ceci :
dim MonRange as range
dim Mot as string
Mot=chr(160)
set MonRange=Range("A1:A50")
MonRange.Replace What:=mot, Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Denis Michon, (N°146)
Comment effacer le caractère le plus à droite dans une cellule ?
Si ton texte est en B10
=GAUCHE(B10;NBCAR(B10)-1)
=GAUCHE(B10;NBCAR(B10)-1)
Popi, (N°145)
Comment faire avec une formule de la feuille pour extraire le dernier mot d'une phrase ?
"=RIGHT(A1,MATCH("
",MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1))),1),0))
ce qui donne en français :
=DROITE(A1;EQUIV("
";STXT(A1;NBCAR(A1)-LIGNE(INDIRECT("1:"&NBCAR(A1)));1);0))
Formule matricielle à valider par controle shift enter.
",MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1))),1),0))
ce qui donne en français :
=DROITE(A1;EQUIV("
";STXT(A1;NBCAR(A1)-LIGNE(INDIRECT("1:"&NBCAR(A1)));1);0))
Formule matricielle à valider par controle shift enter.
George Simms, (N°144)
Je suis à la recherche d'un moyen ou d'une macro pour supprimer les espaces qui se trouvent
devant des chiffres que je colle à partir d'un site
Sub menage()
For Each cellule In ActiveSheet.UsedRange
cellule.Value = LTrim(cellule.Value)
Next
End Sub
For Each cellule In ActiveSheet.UsedRange
cellule.Value = LTrim(cellule.Value)
Next
End Sub
Clément Marcotte, (N°143)
J'ai des adresses qui se présentent de la façon suivante :
SA DUDU
85130 LA VERRIE
DUPONT
49300 ANGERS Je souhaite en extraire le CP
Une petite matricielle ?
Attention, les formules sont à saisir sans retours à la ligne...
{=1*STXT(A1;EQUIV(0;(ESTERREUR(STXT(A1;LIGNE(INDIRECT("1:"&NBCAR(A1)));1)*1)*1);
0);NBCAR(A1)-SOMME((ESTERREUR(STXT(A1;LIGNE(INDIRECT("1:"&NBCAR(A1)));1)*1)*1)))}
Et comme Thierry a eu le malheur de luidemander d'expliquer... voici le pourquoi du comment !
Préambule : Cette formule est une formule générique pour extraire une, et
une seule, valeur numérique contenue dans une chaîne de caractères et elle
n'est pas spécifique à l'extraction d'un code postal
Convention : "zzz" pour "Z'eussplikation"
But du jeu : avec la fonction STXT, extraire d'une chaîne à partir d'une
position P, un nombre N caractères : =STXT(A1;P;N)
La fonction STXT renvoyant du texte, dans = 1*STXT(A1;P;N) le 1*. sert
simplement à transformer le résultat en numérique et pourrait être remplacé
(plus long) par =CNUM(STXT(A1;P;N)
Par rapport à la question posée (Code Postal), la formule pourrait se
résumer à :
= 1*STXT(A1;P;5)
=1*STXT(A1;EQUIV(0;(ESTERREUR(STXT(A1;LIGNE(INDIRECT("1:"&NBCAR(A1)));1)*1)*
1);0);5)
Pour en finir (?) avec le CP on pourrait, pour extraire celui-ci d'une
chaîne telle que :
12,Av zaza 75000 PARIS ou 125 ter..etc.
Faire porter l'extraction sur une partie (droite) de la chaîne et non sur la
chaîne entière en remplaçant, dans la formule, A1(chaîne complète) par
=DROITE(A1;NBCAR(A1)-5)
Le -5 est fixé arbitrairement : on peut supposer, sans grands risques, que
tout ce qui concerne le numéro de la rue, est avant le 5ème caractère
Ce qui pourrait donner :
=1*STXT(DROITE(A1;NBCAR(A1)-5);EQUIV(0;(ESTERREUR(STXT(DROITE(A1;NBCAR(A1)-5
);LIGNE(INDIRECT("1:"&NBCAR(A1)));1)*1)*1);0);5)
Revenons à nos moutons (la formule générique) en prenant comme exemple en A1
:
DUPONT Marcel 75000 PARIS
[A] ** Obtention de P dans la formule =1*STXT(A1;P;N)
P est obtenu par :
=EQUIV(0;(ESTERREUR(STXT(A1;LIGNE(INDIRECT("1:"&NBCAR(A1)));1)*1)*1);0)
qui renvoie 15 (position du 1° chiffre(le 7))
[A-1] ** zzz du EQUIV :
=EQUIV(valeur cherchée ; matrice(ou plage) de recherche ; type de recherche)
La matrice de recherche (2° argument) est obtenue par :
(ESTERREUR(STXT(A1;LIGNE(INDIRECT("1:"&NBCAR(A1)));1)*1)*1)
qui renvoie une matrice de constantes :
{1;1;1;1;1;1;1;1;1;1;1;1;1;1;0;0;0;0;0;1;1;1;1;1;1}
=EQUIV(0;{1;1;1;1;1;1;1;1;1;1;1;1;1;1;0;0;0;0;0;1;1;1;1;1;1};0)
renvoie donc 15 puisque le 1° zéro(valeur cherchée) dans la matrice de
constantes est en 15° position (recherche exacte par le 3° argument = 0)
[A-1-1] ** zzz du (ESTERREUR(STXT(...
[A-1-1-1] ** zzz du LIGNE(INDIRECT("1:"&NBCAR(A1)))
Ca permet, en interne, de bâtir un tableau de constantes représentant les
nombres de 1 à NBCAR(A1)
Résultat :
{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}
[A-1-1-2] ** zzz du STXT(A1;LIGNE(INDIRECT(.
STXT(A1;
{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};1)
Le STXT renvoie (sous forme de matrice) 1 caractère(3° argument = 1) en
partant successivement de la position 1, puis 2,..puis 25 de la chaîne A1
Résultat
:{"D";"U";"P";"O";"N";"T";"
";"M";"a";"r";"c";"e";"l";&quo
;
";"7";"5";"0";"0";"0";"
";"P";"A";"R";"I";"S"}
On en est donc à :
(ESTERREUR({"D";"U";"P";"O";"N";"T"
"
";"M";"a";"r";"c";"e";"l";&quo
;
";"7";"5";"0";"0";"0";"
";"P";"A";"R";"I";"S"}*1)*1)
[A-1-1-3] ** zzz de ce résultat
{"D";"U";"P";"O";"N";"T";"
";"M";"a";"r";"c";"e";"l";&quo
;
";"7";"5";"0";"0";"0";"
";"P";"A";"R";"I";"S"}*1
En multipliant chaque élément de la matrice par 1, les caractères non
numériques vont renvoyer une valeur d'erreur (#VALEUR!) et on va obtenir la
matrice :
{#VALEUR!;#VALEUR!;#VALEUR!;#VALEUR!;#VALEUR!;#VALEUR!;#VALEUR!;#VALEUR!;#VA
LEUR!;#VALEUR!;#VALEUR!;#VALEUR!;#VALEUR!;#VALEUR!;7;5;0;0;0;#VALEUR!;#VALEU
R!;#VALEUR!;#VALEUR!;#VALEUR!;#VALEUR!}
Si je teste chaque élément de la matrice avec ESTERREUR :
ESTERREUR({#VALEUR!;#VALEUR!;#VALEUR!;#VALEUR!;#VALEUR!;#VALEUR!;#VALEUR!;#V
ALEUR!;#VALEUR!;#VALEUR!;#VALEUR!;#VALEUR!;#VALEUR!;#VALEUR!;7;5;0;0;0;#VALE
UR!;#VALEUR!;#VALEUR!;#VALEUR!;#VALEUR!;#VALEUR!})
j'obtiens :
{VRAI;VRAI;VRAI;VRAI;VRAI;VRAI;VRAI;VRAI;VRAI;VRAI;VRAI;VRAI;VRAI;VRAI;FAUX;
FAUX;FAUX;FAUX;FAUX;VRAI;VRAI;VRAI;VRAI;VRAI;VRAI}
Et si je multiplie par 1 ce résultat, la matrice va se transformer en
matrice de 1 et 0 (rappel VRAI = 1 et FAUX = 0)
D'où le résultat utilisé comme 2° argument du EQUIV:
{1;1;1;1;1;1;1;1;1;1;1;1;1;1;0;0;0;0;0;1;1;1;1;1;1}
[B] ** Obtention de N dans la formule =1*STXT(A1;P;N)
NBCAR(A1)+1-SOMME((ESTERREUR(STXT(A1;LIGNE(INDIRECT("1:"&NBCAR(A1)));1)*1)*1
))
Voir chapitre précédent pour la partie
(ESTERREUR(STXT(A1;LIGNE(INDIRECT("1:"&NBCAR(A1)));1)*1)*1)
On a donc :
N = NBCAR(A1)-SOMME({1;1;1;1;1;1;1;1;1;1;1;1;1;1;0;0;0;0;0;1;1;1;1;1;1})
Résultat : =25-20
La somme équivaut au nbre de caractères non numériques et donc, la
différence entre le nbre de caractères total de la chaîne (NBCAR(A1)) et la
somme, donne le nbre de caractères à extraire (3° argument de la fonction
STXT)
Au passage on comprend pourquoi la formule n'est pas valide si, dans la
chaîne, il y a plusieurs "secteurs" numériques !
Bonus :
Lorsque la valeur numérique incluse dans la chaîne, peut être une valeur
décimale
Ex : Zaza a mangé 24,56 croquettes
S'il y a cohérence entre le séparateur décimal du système et celui présent
dans la chaîne il suffit d'ajouter +1 au NBCAR présent dans le 3° argument
de STXT :
{=1*(STXT(A1;EQUIV(0;(ESTERREUR(STXT(A1;LIGNE(INDIRECT("1:"&NBCAR(A1)));1)*1
)*1);0);NBCAR(A1)+1-SOMME((ESTERREUR(STXT(A1;LIGNE(INDIRECT("1:"&NBCAR(A1)))
;1)*1)*1))))}
S'il le séparateur décimal du système est <> de celui présent dans la
chaîne, utiliser SUBSTITUE et aussi ajouter +1 au NBCAR présent dans le 3°
argument de STXT :
Pour un séparateur décimal du système = le point et une virgule dans la
chaîne :
{=1*(STXT(SUBSTITUE(A1;",";".");EQUIV(0;(ESTERREUR(STXT(A1;LIGNE(INDIRECT(&
uot;1
:"&NBCAR(A1)));1)*1)*1);0);NBCAR(A1)+1-SOMME((ESTERREUR(STXT(A1;LIGNE(INDIRE
CT("1:"&NBCAR(A1)));1)*1)*1))))}
Bon, ben, moi j'ai fini et j'en suis fort aise !!
Attention, les formules sont à saisir sans retours à la ligne...
{=1*STXT(A1;EQUIV(0;(ESTERREUR(STXT(A1;LIGNE(INDIRECT("1:"&NBCAR(A1)));1)*1)*1);
0);NBCAR(A1)-SOMME((ESTERREUR(STXT(A1;LIGNE(INDIRECT("1:"&NBCAR(A1)));1)*1)*1)))}
Et comme Thierry a eu le malheur de luidemander d'expliquer... voici le pourquoi du comment !
Préambule : Cette formule est une formule générique pour extraire une, et
une seule, valeur numérique contenue dans une chaîne de caractères et elle
n'est pas spécifique à l'extraction d'un code postal
Convention : "zzz" pour "Z'eussplikation"
But du jeu : avec la fonction STXT, extraire d'une chaîne à partir d'une
position P, un nombre N caractères : =STXT(A1;P;N)
La fonction STXT renvoyant du texte, dans = 1*STXT(A1;P;N) le 1*. sert
simplement à transformer le résultat en numérique et pourrait être remplacé
(plus long) par =CNUM(STXT(A1;P;N)
Par rapport à la question posée (Code Postal), la formule pourrait se
résumer à :
= 1*STXT(A1;P;5)
=1*STXT(A1;EQUIV(0;(ESTERREUR(STXT(A1;LIGNE(INDIRECT("1:"&NBCAR(A1)));1)*1)*
1);0);5)
Pour en finir (?) avec le CP on pourrait, pour extraire celui-ci d'une
chaîne telle que :
12,Av zaza 75000 PARIS ou 125 ter..etc.
Faire porter l'extraction sur une partie (droite) de la chaîne et non sur la
chaîne entière en remplaçant, dans la formule, A1(chaîne complète) par
=DROITE(A1;NBCAR(A1)-5)
Le -5 est fixé arbitrairement : on peut supposer, sans grands risques, que
tout ce qui concerne le numéro de la rue, est avant le 5ème caractère
Ce qui pourrait donner :
=1*STXT(DROITE(A1;NBCAR(A1)-5);EQUIV(0;(ESTERREUR(STXT(DROITE(A1;NBCAR(A1)-5
);LIGNE(INDIRECT("1:"&NBCAR(A1)));1)*1)*1);0);5)
Revenons à nos moutons (la formule générique) en prenant comme exemple en A1
:
DUPONT Marcel 75000 PARIS
[A] ** Obtention de P dans la formule =1*STXT(A1;P;N)
P est obtenu par :
=EQUIV(0;(ESTERREUR(STXT(A1;LIGNE(INDIRECT("1:"&NBCAR(A1)));1)*1)*1);0)
qui renvoie 15 (position du 1° chiffre(le 7))
[A-1] ** zzz du EQUIV :
=EQUIV(valeur cherchée ; matrice(ou plage) de recherche ; type de recherche)
La matrice de recherche (2° argument) est obtenue par :
(ESTERREUR(STXT(A1;LIGNE(INDIRECT("1:"&NBCAR(A1)));1)*1)*1)
qui renvoie une matrice de constantes :
{1;1;1;1;1;1;1;1;1;1;1;1;1;1;0;0;0;0;0;1;1;1;1;1;1}
=EQUIV(0;{1;1;1;1;1;1;1;1;1;1;1;1;1;1;0;0;0;0;0;1;1;1;1;1;1};0)
renvoie donc 15 puisque le 1° zéro(valeur cherchée) dans la matrice de
constantes est en 15° position (recherche exacte par le 3° argument = 0)
[A-1-1] ** zzz du (ESTERREUR(STXT(...
[A-1-1-1] ** zzz du LIGNE(INDIRECT("1:"&NBCAR(A1)))
Ca permet, en interne, de bâtir un tableau de constantes représentant les
nombres de 1 à NBCAR(A1)
Résultat :
{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}
[A-1-1-2] ** zzz du STXT(A1;LIGNE(INDIRECT(.
STXT(A1;
{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};1)
Le STXT renvoie (sous forme de matrice) 1 caractère(3° argument = 1) en
partant successivement de la position 1, puis 2,..puis 25 de la chaîne A1
Résultat
:{"D";"U";"P";"O";"N";"T";"
";"M";"a";"r";"c";"e";"l";&quo
;
";"7";"5";"0";"0";"0";"
";"P";"A";"R";"I";"S"}
On en est donc à :
(ESTERREUR({"D";"U";"P";"O";"N";"T"
"
";"M";"a";"r";"c";"e";"l";&quo
;
";"7";"5";"0";"0";"0";"
";"P";"A";"R";"I";"S"}*1)*1)
[A-1-1-3] ** zzz de ce résultat
{"D";"U";"P";"O";"N";"T";"
";"M";"a";"r";"c";"e";"l";&quo
;
";"7";"5";"0";"0";"0";"
";"P";"A";"R";"I";"S"}*1
En multipliant chaque élément de la matrice par 1, les caractères non
numériques vont renvoyer une valeur d'erreur (#VALEUR!) et on va obtenir la
matrice :
{#VALEUR!;#VALEUR!;#VALEUR!;#VALEUR!;#VALEUR!;#VALEUR!;#VALEUR!;#VALEUR!;#VA
LEUR!;#VALEUR!;#VALEUR!;#VALEUR!;#VALEUR!;#VALEUR!;7;5;0;0;0;#VALEUR!;#VALEU
R!;#VALEUR!;#VALEUR!;#VALEUR!;#VALEUR!}
Si je teste chaque élément de la matrice avec ESTERREUR :
ESTERREUR({#VALEUR!;#VALEUR!;#VALEUR!;#VALEUR!;#VALEUR!;#VALEUR!;#VALEUR!;#V
ALEUR!;#VALEUR!;#VALEUR!;#VALEUR!;#VALEUR!;#VALEUR!;#VALEUR!;7;5;0;0;0;#VALE
UR!;#VALEUR!;#VALEUR!;#VALEUR!;#VALEUR!;#VALEUR!})
j'obtiens :
{VRAI;VRAI;VRAI;VRAI;VRAI;VRAI;VRAI;VRAI;VRAI;VRAI;VRAI;VRAI;VRAI;VRAI;FAUX;
FAUX;FAUX;FAUX;FAUX;VRAI;VRAI;VRAI;VRAI;VRAI;VRAI}
Et si je multiplie par 1 ce résultat, la matrice va se transformer en
matrice de 1 et 0 (rappel VRAI = 1 et FAUX = 0)
D'où le résultat utilisé comme 2° argument du EQUIV:
{1;1;1;1;1;1;1;1;1;1;1;1;1;1;0;0;0;0;0;1;1;1;1;1;1}
[B] ** Obtention de N dans la formule =1*STXT(A1;P;N)
NBCAR(A1)+1-SOMME((ESTERREUR(STXT(A1;LIGNE(INDIRECT("1:"&NBCAR(A1)));1)*1)*1
))
Voir chapitre précédent pour la partie
(ESTERREUR(STXT(A1;LIGNE(INDIRECT("1:"&NBCAR(A1)));1)*1)*1)
On a donc :
N = NBCAR(A1)-SOMME({1;1;1;1;1;1;1;1;1;1;1;1;1;1;0;0;0;0;0;1;1;1;1;1;1})
Résultat : =25-20
La somme équivaut au nbre de caractères non numériques et donc, la
différence entre le nbre de caractères total de la chaîne (NBCAR(A1)) et la
somme, donne le nbre de caractères à extraire (3° argument de la fonction
STXT)
Au passage on comprend pourquoi la formule n'est pas valide si, dans la
chaîne, il y a plusieurs "secteurs" numériques !
Bonus :
Lorsque la valeur numérique incluse dans la chaîne, peut être une valeur
décimale
Ex : Zaza a mangé 24,56 croquettes
S'il y a cohérence entre le séparateur décimal du système et celui présent
dans la chaîne il suffit d'ajouter +1 au NBCAR présent dans le 3° argument
de STXT :
{=1*(STXT(A1;EQUIV(0;(ESTERREUR(STXT(A1;LIGNE(INDIRECT("1:"&NBCAR(A1)));1)*1
)*1);0);NBCAR(A1)+1-SOMME((ESTERREUR(STXT(A1;LIGNE(INDIRECT("1:"&NBCAR(A1)))
;1)*1)*1))))}
S'il le séparateur décimal du système est <> de celui présent dans la
chaîne, utiliser SUBSTITUE et aussi ajouter +1 au NBCAR présent dans le 3°
argument de STXT :
Pour un séparateur décimal du système = le point et une virgule dans la
chaîne :
{=1*(STXT(SUBSTITUE(A1;",";".");EQUIV(0;(ESTERREUR(STXT(A1;LIGNE(INDIRECT(&
uot;1
:"&NBCAR(A1)));1)*1)*1);0);NBCAR(A1)+1-SOMME((ESTERREUR(STXT(A1;LIGNE(INDIRE
CT("1:"&NBCAR(A1)));1)*1)*1))))}
Bon, ben, moi j'ai fini et j'en suis fort aise !!
Alain Vallon, (N°142)
Est il possible dans une cellule d'ôter le chiffre qui suit un nom ? Je m'explique : Dans un
tableau , on a des noms suivis d'un numéro (sans espace) ZAZA1 , ZAZA2 etc... Mon but est de
trouver ZAZA , en enlevant les numéros sachant que parfois on peut trouver aussi ZAZA11,
ZAZA252 voire ZA345ZA.
Function onlylettre(s As String)
For a = 1 To Len(s)
If Mid(s, a, 1) <= 9 Then
Else
onlylettre = onlylettre + Mid(s, a, 1)
End If
Next
End Function
attention : si un chiffre se trouve au milieu du mot il est également supprimé
ou encore : (ici on conserve au lieu d'enlever)
Range("B1")= Left(Range("A1"),4)
Si tu veux conserver les ref du genre ZA353ZA
Function onlyyou(zaza)
num = Len(zaza)
start:
If IsNumeric(Right(zaza, 1)) Then
zaza = Left(zaza, Len(zaza) - 1)
num = num - 1
GoTo start
End If
onlyyou = zaza
End Function
For a = 1 To Len(s)
If Mid(s, a, 1) <= 9 Then
Else
onlylettre = onlylettre + Mid(s, a, 1)
End If
Next
End Function
attention : si un chiffre se trouve au milieu du mot il est également supprimé
ou encore : (ici on conserve au lieu d'enlever)
Range("B1")= Left(Range("A1"),4)
Si tu veux conserver les ref du genre ZA353ZA
Function onlyyou(zaza)
num = Len(zaza)
start:
If IsNumeric(Right(zaza, 1)) Then
zaza = Left(zaza, Len(zaza) - 1)
num = num - 1
GoTo start
End If
onlyyou = zaza
End Function
Kimonku, Jacky, Jacques Chaussard, (N°141)
J'ouvre un fichier texte et je le lis ligne par ligne : Il faut que je vérifie à chaque passage
que la ligne (retstring) contient la chaine de caractère ".htm".Comment faire ?
Par exemple, pour rechercher un z dans parlez
position = instr([début],"parlez","z")
début est un nombre falcutatif, il permet de débuter la recherche ailleurs
qu'au début de la chaîne
Si la chaîne recherchée est présente, le résultat est la position du premier
caractère de la chaîne recherchée dans la chaîne testée. Comme cette
fonction fait la différence entre majuscules et minuscules, il faut soit
tester les deux, soit tester tout en majuscules ou tout en minuscules.
Tu pourrais avoir quelque chose comme cela
Do While f.AtEndOfStream <> True
retstring= f.ReadLine
position = instr(ucase(f.Readline);".HTM")
if position > 0 then
Instructions s'il a ".HTM"
else
Instruction si pas ".HTM"
end if
Loop
ou ce qui reviendrait au même
Do While
f.AtEndOfStream <> True
retstring= f.ReadLine
position =
instr(lcase(f.Readline);".htm")
if position > 0 then
Instructions s'il a ".htm"
else
Instruction si pas ".htm"
end if
Loop
Évidemment si tu veux juste ".htm", SANS .HTM, tu enlèves les
Ucase() et les Lcase()
position = instr([début],"parlez","z")
début est un nombre falcutatif, il permet de débuter la recherche ailleurs
qu'au début de la chaîne
Si la chaîne recherchée est présente, le résultat est la position du premier
caractère de la chaîne recherchée dans la chaîne testée. Comme cette
fonction fait la différence entre majuscules et minuscules, il faut soit
tester les deux, soit tester tout en majuscules ou tout en minuscules.
Tu pourrais avoir quelque chose comme cela
Do While f.AtEndOfStream <> True
retstring= f.ReadLine
position = instr(ucase(f.Readline);".HTM")
if position > 0 then
Instructions s'il a ".HTM"
else
Instruction si pas ".HTM"
end if
Loop
ou ce qui reviendrait au même
Do While
f.AtEndOfStream <> True
retstring= f.ReadLine
position =
instr(lcase(f.Readline);".htm")
if position > 0 then
Instructions s'il a ".htm"
else
Instruction si pas ".htm"
end if
Loop
Évidemment si tu veux juste ".htm", SANS .HTM, tu enlèves les
Ucase() et les Lcase()
Clément Marcotte, (N°140)
J'ai des textes du genre blablabla>texte à extraire quelconque <123 Sachant que les seuls
points de repères sont les < et > (uniques dans le texte) et le nombre fixe de caractères
après le <
"=STXT(A1;CHERCHE(">";A1)+1;NBCAR(A1)-CHERCHE(">";A1)-4)
ou encore :
=SUBSTITUE(STXT(A1;TROUVE(">";A1)+1;999);"<123";)
ChrisV
[-11,32%] :-P (le - 11,32% c'est parce que sa formule est plus courte de 11,32%
que celle de Laurent !)
maintenant quand à savoir le refaire... dixit misange
mais si mais si, Misange
donc Laurent dit
qu'il y a pour la longueur totale
=NBCAR(A1) = 30
texte_a_extraire
=STXT(A1;numéro de départ;numéro d'arret)
numéro de départ: on met plus 1 pour commencer après le ">"
=CHERCHE(">";A1)+1
numéro d'arrêt: la totale moins ceusse du début moins les habitués de la fin
=NBCAR(A1)-CHERCHE(">";A1)-4
il ne reste qu'à substituer les deux dernières dans =STXT(...
***************
Avec un peu de chance, un Longre ou un Sigonneau va sortir une version
allégée... dixit Laurent et voilà la réponse (qui fonctionne très très bien !!)
de AV :
Ben moi j'ai une version en surcharge pondérale !
Si la chaîne à extraire est en A1 :
=DROITE(GAUCHE(INDIRECT(CAR(IMPAIR(64))&CAR(PAIR(9)+39));CHERCHE
(CAR(PGCD(120;60));INDIRECT(CAR(DEGRES(PI())/3+5)&CAR(COMBIN(10;2)
+4+TANH(0))))-(ENT(ACOS(-0.5))-(TAN(45*PI()/180))));NBCAR
(GAUCHE(INDIRECT(CAR((SOMME.CARRES(6;5)-1)+TRONQUE(5.25))&CAR(PPCM(24;36)-23))
;CHERCHE(CAR(RACINE(3600));INDIRECT(CAR(EXP(LN(65)))&CAR(FACT(4)+(5^2))))-
(SIN(PI()/2))))-CHERCHE(CAR(ATAN(1)+61.2146018366025);INDIRECT(CAR(PLAFOND
(64.59; MOD(3; QUOTIENT(5; 2))))&CAR(FACTDOUBLE(6)+ENT(LN(3.7))))))
Pour ne pas faire d'erreur vous pouvez télécharger le classeur exemple...
Alain
Une vraie logique shadock à la hauteur d'un de leurs principes :
"Avec un escalier prévu pour la montée on réussit souvent à monter plus bas
qu'on ne serait descendu avec un escalier prévu pour la descente. "
Et voilà, sur un scénario de misange, vous avez vu apparaître dans le désordre
ou encore :
=SUBSTITUE(STXT(A1;TROUVE(">";A1)+1;999);"<123";)
ChrisV
[-11,32%] :-P (le - 11,32% c'est parce que sa formule est plus courte de 11,32%
que celle de Laurent !)
maintenant quand à savoir le refaire... dixit misange
mais si mais si, Misange
donc Laurent dit
qu'il y a pour la longueur totale
=NBCAR(A1) = 30
texte_a_extraire
=STXT(A1;numéro de départ;numéro d'arret)
numéro de départ: on met plus 1 pour commencer après le ">"
=CHERCHE(">";A1)+1
numéro d'arrêt: la totale moins ceusse du début moins les habitués de la fin
=NBCAR(A1)-CHERCHE(">";A1)-4
il ne reste qu'à substituer les deux dernières dans =STXT(...
***************
Avec un peu de chance, un Longre ou un Sigonneau va sortir une version
allégée... dixit Laurent et voilà la réponse (qui fonctionne très très bien !!)
de AV :
Ben moi j'ai une version en surcharge pondérale !
Si la chaîne à extraire est en A1 :
=DROITE(GAUCHE(INDIRECT(CAR(IMPAIR(64))&CAR(PAIR(9)+39));CHERCHE
(CAR(PGCD(120;60));INDIRECT(CAR(DEGRES(PI())/3+5)&CAR(COMBIN(10;2)
+4+TANH(0))))-(ENT(ACOS(-0.5))-(TAN(45*PI()/180))));NBCAR
(GAUCHE(INDIRECT(CAR((SOMME.CARRES(6;5)-1)+TRONQUE(5.25))&CAR(PPCM(24;36)-23))
;CHERCHE(CAR(RACINE(3600));INDIRECT(CAR(EXP(LN(65)))&CAR(FACT(4)+(5^2))))-
(SIN(PI()/2))))-CHERCHE(CAR(ATAN(1)+61.2146018366025);INDIRECT(CAR(PLAFOND
(64.59; MOD(3; QUOTIENT(5; 2))))&CAR(FACTDOUBLE(6)+ENT(LN(3.7))))))
Pour ne pas faire d'erreur vous pouvez télécharger le classeur exemple...
Alain
Une vraie logique shadock à la hauteur d'un de leurs principes :
"Avec un escalier prévu pour la montée on réussit souvent à monter plus bas
qu'on ne serait descendu avec un escalier prévu pour la descente. "
Et voilà, sur un scénario de misange, vous avez vu apparaître dans le désordre
Alain Vallon, ChrisV, Laurent Mortézai, Isabelle, (N°139)
Comment insérer facilement un "caractère spécial" dans excel si on ne connait pas son
code ?
Tu pourrais créer un bouton personnalisé, et lui affecter la procédure
suivante :
Sub TableCaract()
Shell "CHARMAP.EXE", vbNormalFocus
End
Sub
Attention, la table des caractères est un composant optionnel de
Windows, il
faut vérifier qu'il soit bien installé sur ton ordi.
suivante :
Sub TableCaract()
Shell "CHARMAP.EXE", vbNormalFocus
End
Sub
Attention, la table des caractères est un composant optionnel de
Windows, il
faut vérifier qu'il soit bien installé sur ton ordi.
ChrisV, (N°138)
Suite à une importation d'un fichier dans excel, je voudrais enlever les lignes vides. A l'oeil
certaines lignes le sont mais excel voit quelque chose dedans que je ne vois pas. Comment
détecter les éventuels caractères parasites ?
Ce caractère parasite pourrait bien être le caractère 160.
Copie cette procédure dans un module
sub quelcaractere()
caractère = mid(activecell.value,1,1)
texte = "Le code de ce caractère est " & asc(caractère)
MsgBox (texte)
end sub
Mets le curseur sur une des cellules récalcitrantes et
lance la macro. Tu connaitras ainsi le code du caractère invisible.
Copie cette procédure dans un module
sub quelcaractere()
caractère = mid(activecell.value,1,1)
texte = "Le code de ce caractère est " & asc(caractère)
MsgBox (texte)
end sub
Mets le curseur sur une des cellules récalcitrantes et
lance la macro. Tu connaitras ainsi le code du caractère invisible.
GeeDee, (N°137)
Comment faire pour avoir une liste des lettres de l'alphabet qui s'incrémente automatiquement ?
Pourquoi n'utilises-tu pas la liste personnalisée (Menu Outils/Options/Onglet Liste Pers.)
Pour ajouter cette liste par macro :
Sub AjoutListePers()
Application.AddCustomList ListArray:=Array("A", "B", "C","D", "E", "F", "G", "H", _
"I", "J", "K", "L", "M","N", "O", "P", "Q", "R", "S",
"T", "U", "V", "W", "X", "Y",
"Z")
MsgBox "La nouvelle liste est la numéro : " & Application.CustomListCount
End Sub
Tu pourras ainsi incrémenter une série manuellement dans Excel en plus de ce que tu veux faire !
pour mettre à jour ta liste déroulante :
Private Sub UserForm_Initialize()
Dim NuListe As Byte
NuListe = 5
For n = 1 To UBound(Application.GetCustomListContents(NuListe))
ComboBox1.AddItem Application.GetCustomListContents(NuListe)(n)
Next n
End Sub
Où NuListe correspond au numéro de liste que tu as rajouter dans l'onglet Liste Pers.
(chez moi, la 5ème liste)
Pour ajouter cette liste par macro :
Sub AjoutListePers()
Application.AddCustomList ListArray:=Array("A", "B", "C","D", "E", "F", "G", "H", _
"I", "J", "K", "L", "M","N", "O", "P", "Q", "R", "S",
"T", "U", "V", "W", "X", "Y",
"Z")
MsgBox "La nouvelle liste est la numéro : " & Application.CustomListCount
End Sub
Tu pourras ainsi incrémenter une série manuellement dans Excel en plus de ce que tu veux faire !
pour mettre à jour ta liste déroulante :
Private Sub UserForm_Initialize()
Dim NuListe As Byte
NuListe = 5
For n = 1 To UBound(Application.GetCustomListContents(NuListe))
ComboBox1.AddItem Application.GetCustomListContents(NuListe)(n)
Next n
End Sub
Où NuListe correspond au numéro de liste que tu as rajouter dans l'onglet Liste Pers.
(chez moi, la 5ème liste)
Benead, (N°136)