=ARRONDI($A4;2-ENT(LOG10($A4)))
Function Edit3Ch(ByVal V As Double) As String
Dim Z As String, E As Long
Z = Format(V, ".000E+00"): E = Right$(Z, 3)
Z = Mid$(Z, 2, 3)
Select Case E
Case Is < 1: Edit3Ch = "0," & String$(-E, "0") & Z
Case Is < 3: Edit3Ch = Left$(Z, E) & "," & Mid$(Z, E + 1)
Case Else: Edit3Ch = Z & String$(E - 3, "0")
End Select
End Function
Function Edit3ChU(ByVal Qté As Double, ByVal Unité As String) As String
Dim Z As String, E As Long, M As Long
Z = Format(Qté, ".000E+00"): M = Right$(Z, 3): E = M Mod 3: If E < 0 Then E = E + 3
M = (M - E) \ 3
Z = Mid$(Z, 2, 3): If E > 0 Then Z = Left$(Z, E) & "," & Mid$(Z, E + 1) Else M = M - 1
If Abs(M) > 8 Then Z = Z & "*10^" & 3 * M & " " Else Z = Z & " " & Choose(M + 9, _
"y", "z", "a", "f", "p", "n", "µ", "m", "", "k", "M", "G", "T", "P", "E", "Z", "Y")
Edit3ChU = Z & Unité
End Function
Function Edit3ChU(ByVal Qté As Double, ByVal Unité As String) As String
Dim Z As String, E As Long, M As Long
If Unité = "m" Then
Select Case Qté
Case Is > 9.46098133890965E+15: Unité = "al": Qté = Qté / 9.46098133890965E+15
Case Is > 149597870691#: Unité = "ua": Qté = Qté / 149597870691#
End Select: End If
Z = Format(Qté, ".000E+00"): M = Right$(Z, 3): E = M Mod 3: If E < 0 Then E = E + 3
M = (M - E) \ 3
Z = Mid$(Z, 2, 3): If E > 0 Then Z = Left$(Z, E) & "," & Mid$(Z, E + 1) Else M = M - 1
If M >= 2 Then If Unité = "g" Then Unité = "t": M = M - 2
If Abs(M) > 8 Then Z = Z & "*10^" & 3 * M & Chr$(160) Else Z = Z & Chr$(160) & Choose(M + 9, _
"y", "z", "a", "f", "p", "n", "µ", "m", "", "k", "M", "G", "T", "P", "E", "Z", "Y")
Edit3ChU = Z & Unité
End Function
Toujours pas de réponse !
Toujours pas satisfaisant ?
=Edit3chU(1,5014;"g")
Mais bien sûr qu'il y a ce qu'il faut pour cela, mais vous allez encore dire que ça ne vous convient pas ! C'est le format scientifique. Toutes nos propositions tournent autour de ça d'une façon ou d'une autre.ça m'étonne vraiment que l'on ne trouve rien dans Excel pour avoir une mise en forme avec des chiffres significatifs....
Function Edit2Ch(ByVal V As Double) As String
Dim Z As String, E As Long
Z = Format(V, ".00E+00"): E = Right$(Z, 3)
Z = Mid$(Z, 2, 2)
Select Case E
Case Is < 1: Edit2Ch = "0," & String$(-E, "0") & Z
Case Is = 1: Edit2Ch = Left$(Z, 1) & "," & Mid$(Z, 2)
Case Else: Edit2Ch = Z & String$(E - 2, "0")
End Select
End Function