I'm using the following function to format the number of decimals to
show:

Function FormatDecimal(strValue As String) As String
On Error Resume Next

Dim arrArray As Variant
arrArray = Split(strValue, ",")

If Len(arrArray(1)) = 0 Then FormatDecimal = arrArray(0)
If Len(arrArray(1)) = 1 Then FormatDecimal = FormatNumber(strValue,
1)
If Len(arrArray(1)) = 2 Then FormatDecimal = FormatNumber(strValue,
2)
If Len(arrArray(1)) = 3 Then FormatDecimal =
FormatNumber(strValue, 3)
End Function

If strValue = "115" the above function returns "115,000" which is
wrong. I want it to return "115". The reason is that Len(arrArray(1)) =
"Index outside interval" in this case.

Can someone please help me?

Regards,

S

Try this:
Function FormatDecimal(strValue As String) As String
p = InStr(1, strValue, ",")
dec = IIf(p = 0, 0, Len(strValue) - p)
FormatDecimal = FormatNumber(strValue, WorksheetFunction.Min(3, dec))
End Function

Regards,
Stefi

€� ezt Ã*rta:

I'm using the following function to format the number of decimals to
show:

Function FormatDecimal(strValue As String) As String
On Error Resume Next

Dim arrArray As Variant
arrArray = Split(strValue, ",")

If Len(arrArray(1)) = 0 Then FormatDecimal = arrArray(0)
If Len(arrArray(1)) = 1 Then FormatDecimal = FormatNumber(strValue,
1)
If Len(arrArray(1)) = 2 Then FormatDecimal = FormatNumber(strValue,
2)
If Len(arrArray(1)) = 3 Then FormatDecimal =
FormatNumber(strValue, 3)
End Function

If strValue = "115" the above function returns "115,000" which is
wrong. I want it to return "115". The reason is that Len(arrArray(1)) =
"Index outside interval" in this case.

Can someone please help me?

Regards,

S

Thank's a lot Stefi! Looks like this is a really neat function.

Some users have "." instead of "," as decimal separator. Is there a way
to also handle this?

Regards,

S

Stefi skrev:

Try this:
Function FormatDecimal(strValue As String) As String
p = InStr(1, strValue, ",")
dec = IIf(p = 0, 0, Len(strValue) - p)
FormatDecimal = FormatNumber(strValue, WorksheetFunction.Min(3, dec))
End Function

Regards,
Stefi

" ezt írta:

I'm using the following function to format the number of decimals to
show:

Function FormatDecimal(strValue As String) As String
On Error Resume Next

Dim arrArray As Variant
arrArray = Split(strValue, ",")

If Len(arrArray(1)) = 0 Then FormatDecimal = arrArray(0)
If Len(arrArray(1)) = 1 Then FormatDecimal = FormatNumber(strValue,
1)
If Len(arrArray(1)) = 2 Then FormatDecimal = FormatNumber(strValue,
2)
If Len(arrArray(1)) = 3 Then FormatDecimal =
FormatNumber(strValue, 3)
End Function

If strValue = "115" the above function returns "115,000" which is
wrong. I want it to return "115". The reason is that Len(arrArray(1)) =
"Index outside interval" in this case.

Can someone please help me?

Regards,

S