Returning two decimal places

[nayone]

Hi I have a function in a workbook that reformats a number, taking out decimal place & replacing it with a comma:

MsgBox ReformatNumber((Sheet1.Cells(1, 1).Value))
& the function :
Private Function ReformatNumber(ByVal Num As Single) As String
ReformatNumber = Replace(Num, ".", ",")
End Function

now this works fine but I am trying to add zeros to the end so if a user enters 1 or 1.2 we get 1,00 & 1,20 not 1, & 1,2 as currently
I have tried Mid(Num, Trim(Len(Num)) -2 1, 1) = "." to identify items & then add zeros but the 'len' command always returns 4? within the function for any length of text??

any pointers as this ones in got me

thx

Alex

 

[westconn1]

try like this
reformatnumber = replace(format(num,"0.00"),".",",")

 

[nayone]

Works fine thx very much

Had got round it with

If Left(Right(Num, 3), 1) = "." Then
ReformatNumber = Replace(Num, ".", ",")
ElseIf Left(Right(Num, 2), 1) = "." Then
ReformatNumber = Replace(Num, ".", ",") & "0"
Else
ReformatNumber = Num & "," & "00"
'End If

But your code is much neater, thx very much

Alex