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