jayakodiu@yahoo.com Custom Formatting Numbers Though VBS & VB6 have formatting functions they work only for millions (US system), unlike Excel's custom formatting; if you you say to VB, Format(123456789.456, "###,##,##,###.##"), you get 123,456,789.46 in place of 12,34,56,789.46 - for use in the sub-continent.
The following vbs function takes any type of format that uses comma separaors for integer and zeros for decimals:
z="###,##,##,###.00":x=123456789.47623
zn=len(z):znn=zn:rx=fix(x):px=x-rx
k=inStr(z,".")
if k=0 then rx=rx+cint(px)
if k>0 then
dn=zn-k:tn=10^dn:px=cint(px*tn)
If px=0 then wp=mid(z,k) else wp="."+cstr(px)
znn=zn-dn-1:z=left(z,znn)
End If
n=len(rx):jk=n
for i=znn To 1 step -1
zt=mid(z,i,1)
if zt="," then tt=","+tt
if zt="#" then tt=mid(rx,jk,1)+tt:jk=jk-1
if jk<1 then exit for
Next
wd=tt+wp
msgbox wd
--------------------
A simple utility using the above is in the html file below:
<html> <body>Use commas as separators for thousand/million or lacs/crores; zeros for decimals. <table border="1" cellpadding="3" cellspacing="3" width="491" style="font-family: Arial; font-size: 14pt" height="105"> <tr><td align="right" width="59" height="22">Format</td><td width="379" bgcolor="#C0C0C0" height="22"> <input type=text id=fm value="###,###,###,###,###.000" size="43" style="font-family: Arial; font-size: 14pt" name="fnn"></td></tr> <tr><td align="right" width="59" height="25">Number</td> <td width="379" height="25"><input type=text value="123456789.9723" size="37" style="border-style: solid; border-width: 1" name="ipt"> <input type=button value="Ok" onclick=fmt()></td> </tr> <tr><td align="right" width="59" height="22">Result</td><td width="379" id="rst" height="22"></td></tr> </table> </body> <script language=vbs> Sub fmt() z=fm.value x=ipt.value zn = Len(z) znn = zn rx = Fix(x) px = x - rx k = InStr(z, ".") If k = 0 Then rx = rx + CInt(px) If k > 0 Then dn = zn - k tn = 10 ^ dn px = CInt(px * tn) If px = 0 Then wp = Mid(z, k) Else wp = "." + CStr(px) znn = zn - dn - 1 z = Left(z, znn) End If n = Len(rx) jk = n For i = znn To 1 Step -1 zt = Mid(z, i, 1) If zt = "," Then tt = "," + tt If zt = "#" Then tt = Mid(rx, jk, 1) + tt: jk = jk - 1 If jk < 1 Then Exit For Next rst.innertext= tt + wp end sub </script> </html>