About This Code
Brief Description:
Function PunctuateAtThousands(sourceNumber As Variant) As Variant
Contributor:
Andrew Jones
Last Modified:
17 Jun 2002
OpenNTF Disclaimer
All of the program code and information presented in the OpenNTF.org Code Bin are provided "as-is", and should be used at your own risk. OpenNTF.org make no express or implied warranty about anything in the Code Bin, and OpenNTF.org will not be responsible or liable for any damage caused by the use or misuse of anything from this site. OpenNTF.org makes no guarantees about anything. Please thoroughly test all of the knowledge and code you find here before you attempt to use them in your production environment.
Code / Description
There are times when you need to display a number punctuated at thousands. If it's a number field, then one may enable "Punctuate at thousands" feature, but if the field happens to be a text field then there is no such option. This is where this function comes handy. It takes a source, and after processing, it returns punctuated value. While punctuating, this function takes care of decimals and negative signs. I hope this function would make a difference to someone ... somewhere.
Usage / Example
Function PunctuateAtThousands(sourceNumber As Variant) As Variant
Dim tempNumber As Variant
Dim finalNumber As Variant
Dim prefix As Variant
Dim suffix As Variant
Dim pos As Integer
'*************************************
If Left(sourceNumber,1)="-"Then
prefix=Left(sourceNumber,1)
sourceNumber=Right(sourceNumber,Len(sourceNumber)-1)
End If
pos=Instr(sourceNumber,".")
If pos>0 Then
suffix=Right(sourceNumber,Len(sourceNumber)-(pos-1))
sourceNumber=Left(sourceNumber,pos-1)
End If
Do While True
tempNumber=Trim(Right(sourceNumber,3))
If Len(tempNumber)=3 Then
If finalNumber="" Then
finalNumber=","+tempNumber
Else
finalNumber=","+tempNumber+finalNumber
End If
sourceNumber=Left(sourceNumber,Len(sourceNumber)-3)
Else
finalNumber=Trim(tempNumber+finalNumber)
Exit Do
End If
Loop
If Left(finalNumber,1)="," Then
finalNumber=Right(finalNumber,Len(finalNumber)-1)
End If
finalNumber=prefix+finalNumber+suffix
PunctuateAtThousands=finalNumber
End Function