Function to convert money value to pure indian type

[ravitunk]

hi all....i have a string which accepts money...in any form like...123456 or 1,23,456.00 or 3456.00...... finally i want it to be coverted to...1,23,456.00, 3,456.00 (comma after lakhs,thousands....with a double digit precision)....please tell me if there is any fucntion or a code to accomplish this ASAP....

[debasisdas]

you need to use the FORMAT function for the purpose.

[ravitunk]

hey thks for reply...i know format function helps in tht case..but its returning the value as....123,456.00..if the input is more than 1lakh......also i used formatnumber...enen then the result is same....i want it to return...as...1,23,456.00.......reply me ASAP....thks for any kind of help...

you need to use the FORMAT function for the purpose.

[debasisdas]

try like this

Format(12345678.00004, "###,###,###.#####")


AND DO NOT START A DUPLICATE THREAD FOR THE SAME QUESTION

[QVeen72]

Hi,

I Guess, using any Combination of Locale setting and Format, you would end up with a Digit grouping of 3 Digits..
You can write a Format function of your own..
Try this :

Private Function MyFormat(ByVal TNum As String) As String
    '
    Dim TStr As String
    Dim OStr As String
    Dim TempStr As String
    '
    TNum = Replace(TNum, ",", "")
    '
    OStr = Format(TNum, "0.00")
    TStr = Right(OStr, 6)
    If Len(OStr) <= 6 Then
        MyFormat = TStr
    Else
        OStr = Left(OStr, Len(OStr) - 6)
        Do
            TempStr = Right(OStr, 2)
            TStr = TempStr & "," & TStr
            OStr = Left(OStr, Len(OStr) - Len(TempStr))
        Loop Until Trim(OStr) = ""
        MyFormat = TStr
    End If
    '
End Function
 

To Call this Function, use this code:

Private Sub Text1_LostFocus()
    Text1.Text = MyFormat(Text1)
End Sub
 

Regards
Veena

[ravitunk]

thks for the reply..but the result it generates is....12,345,678.00004......wich is not i wanted....plz it should seperate lakhs with a comma...thousands with a comma...and so on.....the output should be like....1,23,45,678.00......plz help me out....

try like this

Format(12345678.00004, "###,###,###.#####")


AND DO NOT START A DUPLICATE THREAD FOR THE SAME QUESTION

[debasisdas]

cant you change the position of comma ?

[smartchap]

Code from QVeen works fine but after modifying for a negative number.

[VijaySofist]

Hi!

Here is the Code you are looking for. Please Find the attachment here with this post.

With Regards
Vijay. R

[ravitunk]

infact..i shud say thks to veena(QVeen72 )....tht code worked well...thks a lot..for ur help..

Hi,

I Guess, using any Combination of Locale setting and Format, you would end up with a Digit grouping of 3 Digits..
You can write a Format function of your own..
Try this :

Private Function MyFormat(ByVal TNum As String) As String
    '
    Dim TStr As String
    Dim OStr As String
    Dim TempStr As String
    '
    TNum = Replace(TNum, ",", "")
    '
    OStr = Format(TNum, "0.00")
    TStr = Right(OStr, 6)
    If Len(OStr) <= 6 Then
        MyFormat = TStr
    Else
        OStr = Left(OStr, Len(OStr) - 6)
        Do
            TempStr = Right(OStr, 2)
            TStr = TempStr & "," & TStr
            OStr = Left(OStr, Len(OStr) - Len(TempStr))
        Loop Until Trim(OStr) = ""
        MyFormat = TStr
    End If
    '
End Function
 

To Call this Function, use this code:

Private Sub Text1_LostFocus()
    Text1.Text = MyFormat(Text1)
End Sub
 

Regards
Veena