Conversion d'importation

Voici quelques cas classiques de valeurs importées.

Pour convertir des chaines contenant des valeurs numériques, on pourra essayer :

-La commande Edition/Remplacer pour remplacer les . par des ,
-Une formule =A1*1
-Ecrire 1 dans une cellule puis Copier Collage spécial/Multiplication

Valeur à convertir

Résultat

VBA

Formule

1.234
1 234.56
1.56

1,234
1234,56
1,56

For Each c In Selection
   c.Value = Val(c.Text)
Next c

=Cnum(Substitue(B2;".";","))

123 456

123456

For Each c In Selection
   c.NumberFormat = "0"
   c.Value = Replace(c.Text, Chr(160), "")
Next c

=CNUM(SUBSTITUE(A1;CAR(160);""))

1,45

1,45

For Each c In Selection
c.Value = Val(Replace(c.Text, ",", "."))
Next c

=B2*1
=Cnum(B2)

+27 377,87
-27 377,88

27377,87
-27377,88

For Each c In Selection
c.Value = Val(Replace(c.Text, ",", "."))
Next c

=Cnum(B7)

1,234.56
123,456.78
12,345.67
1,234.59

1234,56
123456,78
12345,67
1234,59

For Each c In Selection
  c.Value = Val(Replace(c.Text, ",", ""))
Next c

=Cnum(Substitue(Substitue(B11;",";"");".";","))

1.625,60801
1.625,60800
2.215,21500
6.319,74864-
1.181.250,00000-

1625,60801
1625,608
2215,215
-6319,74864
-1181250

For Each c In Selection
c.Value = Val(Replace(Replace(c.Text, ".", ""), ",", "."))
Next c

1,234
123,456
12,667

1.234
123.456
12.667

For Each c In Selection
  c.NumberFormat = "@"
  c.Value = Replace(c.Text, ",", ".")
Next c

=Substitue(B19;",";".")

1,234
12345,456
12,667

1.23
12 345.46
12.67

For Each c In Selection
  c.NumberFormat = "@"
  c.Value = Replace(Format(c.Text, "# ##0.00"), ",", ".")
Next c

=Substitue(Texte(B23;"# ##0,00");",";".")

10/30/07
9/4/2006
10/31/07
1/2/2006

30/10/2007
04/09/2006
31/10/2007
02/01/2006

For Each c In Selection
  temp = Split(c.Text, "/")
  c.Value = DateSerial(temp(2), temp(0), temp(1))
Next c

=DATEVAL(STXT(A1;CHERCHE("/";A1)+1;CHERCHE("/";A1;4)-CHERCHE("/";A1))&GAUCHE(A1;CHERCHE("/";A1))&
STXT(A1;CHERCHE("/";A1;4)+1;4))

123 56 78

123 56 78

=Cnum(SubStitue(A1;" ";""))

Elimine caractères spéciaux:
1234 5 5

123455

For Each c In Selection
  temp = c.Text
  temp2 = ""
  For i = 1 To Len(temp)
    If InStr("0123456789.,",       Mid(temp, i, 1)) <> 0        Then temp2 = temp2 &         Mid(temp, i, 1)
  Next i
  c.Value = temp2
Next c

Exemples

ConversionImport

Barre Utilitaires