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.
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
This was first published in January 2002