Enlever les 0 et convertir en valeurs numériques

**Wyrgle** · 04/01/2017, 16h17

Bonjour le forum,

En colonne D j'ai des contenus du type ="0012345000" (signe = et " compris).

Je souhaite obtenir 12345, soit convertir en valeur numérique et enlever les 0.
Je n'arrive pas à convertir.
Une fois converti je pense utiliser

Code :

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

Columns("D").Replace "0", ""

Comment faire ?

Merci !
Wyrgle

**jerome.vaussenat** · 04/01/2017, 16h29

Salut,

avec cette formule que tu places en colonne E par exemple.
=CNUM(SUBSTITUE(D1;0;""))

Après, tu peux faire un copier /coller valeur et ensuite supprimer la colonne D.

**halaster08** · 04/01/2017, 16h53

Quid de "00123045600"?

Je suis presque certains d'avoir déjà vu un sujet similaire sur ce forum, as tu fais une recherche?

**unparia** · 04/01/2017, 17h03

Bonjour
Avant toute autre chose : un

à halaster08 pour son réflexe
Ensuite : la relecture du message du demandeur montre bien qu'il s'agit de transformer in situ et non d'obtenir et afficher "ailleurs". VBA est alors nécessaire si l'on ne veut pas insérer, convertir, supprimer, etc ...
Et cette transformation est simple à conduire en VBA (autre section de ce forum).

**unparia** · 04/01/2017, 17h43

Bon
Je n'aime pas trop les réponses VBA dans la section Excel, ni les réponses Excel dans la section VBA, mais bon ...
voilà un petit jeu de marelle (je baptise ainsi ce mécanisme pour une raison que chacun comprendra vite) VBA

Exemple sur la colonne C :

Code :

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

1
2
3
4
5
6
7
8
derlig = Range("C" & Rows.Count).End(xlUp).Row
For Each c In Range("C1:C" & derlig).Cells
  toto = Val(c.Text)
  toto = Val(StrReverse(toto))
  toto = StrReverse(toto)
  If Left(c.Formula, 2) = "=""" Then toto = "=""" & toto & """"
  c.Formula = toto
Next

J'ai remis là tes "=, mais je ne vois pas ils pourraient continuer à te servir. Si tu ne les veux plus, il suffit de supprimer la ligne 6 de mon code.

**papouclo** · 04/01/2017, 18h15

Bonjour,
Avec 0012345000 en A1, la formule

Code :

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

=CNUM(SUBSTITUE(SUPPRESPACE(SUBSTITUE(A1;"0";" "));" ";"0"))

renvoie 12345.
Avec 00123045000 en A1, la même formule renvoie 123045.
Mais il faut une colonne supplémentaire pour obtenir les résultats.
Cordialement
Claude

**Wyrgle** · 04/01/2017, 22h56

Envoyé par halaster08

Quid de "00123045600"?

Je suis presque certains d'avoir déjà vu un sujet similaire sur ce forum, as tu fais une recherche?

Bonjour Halaster08,
Oui j'ai effectué une recherche mais je ne trouve rien d'applicable à mon problème.

Bonjour unparia,
Ton code de marelle me rapproche de la solution, mais voila ce que j'obtiens :

Nom : Capture.jpg
Affichages : 272
Taille : 17,1 Ko

Nom : Capture.jpg
Affichages : 272
Taille : 17,1 Ko

(colonne de gauche = les résultats des données recopiées en colonne de droite)

**unparia** · 04/01/2017, 23h41

cela veut dire que certaines de tes cellules contiennent en réalité autre chose que ce que tu y vois.
La preuve en est que l'on y voit (avant traitement) le =" qui disparaîtrait spontanément (toujours avant traitement), Excel considérerait automatiquement qu'il s'agirait là d'une formule dont il afficherait le seul résultat (et le =" ne serait donc pas affiché dans la cellule)
Peux-tu mettre ici une capture d'écran montrant à la fois la barre de formule et la cellule sélectionnée en sélectionnant l'une de ces cellules qui continuent à poser problème ?
J'aimerais également que, toujours alors que cette cellule est sélectionnée, tu me dises ce que renverrait l'exécution de ce petit code :

Code :

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

1
2
3
4
5
MsgBox "valeur   " & Asc(Left(Selection.Value, 1)) & vbCrLf & _
"valeur2   " & Asc(Left(Selection.Value2, 1)) & vbCrLf & _
"texte    " & Asc(Left(Selection.Text, 1)) & vbCrLf & _
"formule    " & Asc(Left(Selection.Formula, 1)) & vbCrLf & _
"-" & Selection.Text & "-" & Selection.Value & "-" & Selection.Formula

En outre : si les 3 premières lignes affichent toutes les 3 61, il va être nécessaire que tu me fasses parvenir un copié/collé de cette donnée, telle qu'elle est dans le fichier d'où tu l'importes.
J'attends

ATTENTION : place-toi bien sur la cellule concernée avant de lancer le code.

Bon : je vois que tu n'es plus là. A demain, dans ce cas.

**RyuAutodidacte** · 05/01/2017, 00h47

Coucou Jacques ;-)
intéressant comme test, ça me serait jamais venu à l'esprit

(j'ai encore débrayé quelques neurones

)
Sans regarder tes codes au début j'étais parti sur CNUM et cherché à inversé avec une formule mais apparemment il n'en n’existe pas qui le fait directement => passage au VBA
C'est qu'après avoir jeter un œil sur tes codes que je me suis rendu compte que je partais sur le même principe (je dirai même plus c'est la même chose

) mais en mode bourrin

Code :

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

Range("A" & i) = StrReverse(Val(StrReverse(Val(Range("A" & i)))))

quoi qu'il soit je trouve sympa ce test et utile
mais bon comme je n'ai pas les donnés du demandeur => pas de problème :

'00000222555000 => 222555
="000558990000" => 55899
="9990001" => 9990001

Amicalement

Ryu

Edit : curieux de voir ce qui peut provoquer ce résultat chez @Wyrgle

**unparia** · 05/01/2017, 07h43

Bonjour RyuAutodidacte
Ce qui est réellement surprenant, c'est le fait que l'on voit bien sur sa capture d'écran que certaines cellules affichent un texte du type ="0000####000" avec alignement à droite. Or cela ne peut normalement se faire que si le contenu est précédé d'un caractère (en général le caractère ' ) et que l'on a de surcroît forcé l'alignement à droite. C'est ce caractère, qui m'intéresse.

EDIT : ce n'est pas pour rien, que j'ai par ailleurs voulu une copie d'écran incluant la barre de formules. Il n'est pas impossible que l'on y découvre "l'inattendu le moins attendu".

**Wyrgle** · 05/01/2017, 23h00

Bonsoir à tous,

Voici une copie d'écran avec la barre de formule :

Nom : Capture.JPG
Affichages : 183
Taille : 16,6 Ko

colonne de gauche = data après macro - colonne de droite = copie des data avant macro.

RyuAutodidacte, merci pour ton code

Code :

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

1
2
3
4
5
6
Sub test()
Dim i As Integer
For i = 1 To ActiveSheet.Cells.Find("*", , , , xlByRows, xlPrevious).Row
    Range("D" & i) = StrReverse(Val(StrReverse(Val(Range("D" & i)))))
Next i
End Sub

Un grand merci au forum, je ne pensais que ma question soulèverait autant d'intérêt !

De même, si j'ai maintenant les données suivantes (sans 0 à enlever) :

Nom : Capture.JPG
Affichages : 156
Taille : 15,2 Ko

Comment modifier ton code pour ne plus avoir les caractères = et " ?

**Marc-L** · 05/01/2017, 23h28

Envoyé par Wyrgle

code pour ne plus avoir les caractères = et " ?

Bonjour !

Code :

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

1
2
3
4
5
Sub Demo()
    With Range("D1", Cells(Rows.Count, 4).End(xlUp))
        .Formula = .Value
    End With
End Sub

_________________________________________________________________________________________________________

Merci de cliquer sur

pour chaque message ayant aidé puis sur

pour clore cette discussion …

_________________________________________________________________________________________________________

Je suis Paris, Istanbul, Berlin, Nice, Bruxelles, Charlie, …

**RyuAutodidacte** · 06/01/2017, 01h47

Envoyé par Wyrgle

RyuAutodidacte, merci pour ton code

Code :

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

1
2
3
4
5
6
Sub test()
Dim i As Integer
For i = 1 To ActiveSheet.Cells.Find("*", , , , xlByRows, xlPrevious).Row
    Range("D" & i) = StrReverse(Val(StrReverse(Val(Range("D" & i)))))
Next i
End Sub

Même si je suis partis sur la même idée que @unparia, il avait déjà donné la réponse en décomposant le code, il suffisait de supprimer la ligne qu'il avait indiqué

Envoyé par Wyrgle

Comment modifier ton code pour ne plus avoir les caractères = et " ?

Voilà @Marc-L (que je salue au passage

) t'as donné la réponse post ci-dessus

**Marc-L** · 06/01/2017, 02h23

Salut Ryu !

Le type de ta variable i pourrait déclencher une erreur avec beaucoup de lignes …

Sinon pour combiner les deux traitements :

Code :

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

1
2
3
4
5
6
7
8
9
10
Sub Demo2()
    With Range("D1", Cells(Rows.Count, 4).End(xlUp))
            .NumberFormat = "General"
            VA = .Value
        For R& = 1 To UBound(VA)
            If VA(R, 1) > "" Then If IsNumeric(VA(R, 1)) Then VA(R, 1) = StrReverse(Val(StrReverse(VA(R, 1))))
        Next
            .Value = VA
    End With
End Sub

_________________________________________________________________________________________________________

Merci de cliquer sur

pour chaque message ayant aidé puis sur

pour clore cette discussion …

**RyuAutodidacte** · 06/01/2017, 13h10

hi Marc,
Merci de ta remarque, mais j'ai un doute sur le pourquoi ?
peux tu m'éclairer stp.

le code

**bboy-eazy** · 06/01/2017, 14h08

Bonjour à tous,

Le type de ta variable i pourrait déclencher une erreur avec beaucoup de lignes …

Merci de ta remarque, mais j'ai un doute sur le pourquoi ?

Je m'immisce dans la conversation car le pourquoi semble être ceci :

Code :

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

Dim i As Integer

le type integer implique un nombre de ligne inférieur à 32000 et quelques lignes
sauf si Marc-L voit encore autre chose qui me/nous serait encore invisible

Bonne journée à tous

**RyuAutodidacte** · 06/01/2017, 14h28

En effet c'est une possibilité, je n'avais pas pensé à cela car la plus part du temps je déclare mes variables en long comme cela

Code :

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

Dim i&

**unparia** · 06/01/2017, 14h59

Re
J'aurais de très loin préféré (on y verrait plus clair) la réponse aux tests que j'avais demandés, réponse qui aurait permis d'affiner énormément et à coup sûr, quels que puseent être les caractères indésirables tant "devant" que "derrière"
En cette absence, nous voilà forcés d'utiliser la méthode bourrin pour être absolument certains de ne pas "couler" si autres caractères fortuits présents.
La voilà donc (et c'est vraiment dommage d'en arriver là) :

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
For k = 1 To Range("A" & Rows.Count).End(xlUp).Row
  c = Range("A" & k).Formula
  For i = 1 To Len(c)
    Select Case Mid(c, i, 1)
      Case 1 To 9
        Exit For
      Case Else
        Mid(c, i, 1) = " "
    End Select
  Next
  For i = Len(c) To 1 Step -1
    Select Case Mid(c, i, 1)
      Case 1 To 9
        Exit For
      Case Else
        Mid(c, i, 1) = " "
    End Select
  Next
  Range("A" & k).Formula = trim(c)
Next

**Marc-L** · 06/01/2017, 17h17

Ryu,

bboy-eazy a mis dans le mille !

Une astuce en cas de doute pour déclarer une variable :
regarder dans l'aide VBA ce que renvoie la propriété concernée (ici Row) …