Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
1.64/5 (4 votes)
See more:
I want to convert number to word in crystal report formula fields.
Suppose the amount is 31500


It should show like:
Thirty One Thousand and Five Hundred
Posted
Updated 12-Jan-20 3:06am

 
Share this answer
 
Comments
fisCO7 12-Nov-10 9:37am    
Thnx for first answer, was great! But need to do it in Crystal Report, not in side C#.
E.F. Nijboer 12-Nov-10 10:03am    
This link will give another example that is aimed for Crystal Reports with VB.NET code to achieve this:
http://www.dotnetspider.com/resources/5462-How-Convert-Rupee-Value-Words-using-VB-NET.aspx

Here also a link to the code snippet that gives an SQL function (could be useful if you are using sql server):
http://www.experts-exchange.com/codeSnippetPopup.jsp?csid=95883

Maybe these links are more helpful.
fisCO7 12-Nov-10 16:58pm    
thank you for solution.
 
Share this answer
 
Formula="Rs. "+ ToWords ({@calculate_TotalAmtwithChrges},0) + " and " +ToWords ((Round({@calculate_TotalAmtwithChrges},2) - Int({@calculate_TotalAmtwithChrges})) * 100, 0) + " Paise only"
 
Share this answer
 
Please try with this code



numbervar RmVal:=0;
numbervar Amt:=0;
numbervar pAmt:=0;
stringvar InWords :="Rupees ";

Amt := type your value ; // 25,12,000


if Amt > 10000000 then RmVal := truncate(Amt/10000000);
if Amt = 10000000 then RmVal := 1;

if RmVal = 1 then
InWords := InWords + " " + towords(RmVal,0) + " crore"
else
if RmVal > 1 then InWords := InWords + " " + towords(RmVal,0) + " crores";


Amt := Amt - Rmval * 10000000;

if Amt > 100000 then RmVal := truncate(Amt/100000);
if Amt = 100000 then RmVal := 1;

if RmVal = 1 then
InWords := InWords + " " + towords(RmVal,0) + " lakhs"
Else
If RmVal > 1 then InWords := InWords + " " + ToWords(RmVal,0) + "Lakhs";

Amt := Amt - Rmval * 100000;

if Amt > 0 then InWords := InWords + " " + towords(truncate(Amt),0);

pAmt := (Amt - truncate(Amt)) * 100;

if pAmt > 0 then
InWords := InWords + " and " + towords(pAmt,0) + " paisa only"
else
InWords := InWords + " only";

UPPERCASE(InWords)
 
Share this answer
 
Comments
anikkket87 5-Jun-12 5:41am    
Thanks Dipak, your solutions worked very well.
Member 13781224 28-Nov-18 6:57am    
i want the code for one thousand rupees = एक हजार रुपये like that
plesee help me

prasad.absoft@gmail.com
Аslam Iqbal 3-Feb-14 5:07am    
5+
Shubham Johri 24-Sep-14 7:27am    
Correct..!! :)
Thanks..!!
Member 13781224 28-Nov-18 6:57am    
i want the code for one thousand rupees = एक हजार रुपये like that
plesee help me

prasad.absoft@gmail.com
 
Share this answer
 
C#
function convertAmountToFigures(damount) {
    var obAmt = damount.split(".");
    var strIntPart = convertNumberToWords(damount);
    var strDecPart = '';
    if (parseInt(obAmt[1]) > 0) {
        strDecPart = ' and ' + convertNumberToWords(parseInt(obAmt[1])) + ' Paise';
    }
    document.getElementById('divinwords').innerHTML = "Rupees" + '  ' + strIntPart + ' ' + strDecPart + ' Only.';
    return ("Rupees" + '  ' + strIntPart + ' ' + strDecPart + ' Only.');
}
 
Share this answer
 
C#
function convertNumberToWords(amount) {
    var junkVal = amount;
    junkVal = Math.floor(junkVal);
    var obStr = new String(junkVal);
    numReversed = obStr.split("");
    actnumber = numReversed.reverse();
    if (Number(junkVal) >= 0) {
        
    }
    else {
        alert('wrong Number cannot be converted');
        return '';
    }
    if (Number(junkVal) == 0) {
        
        return 'Zero';
    }
    if (actnumber.length > 12) {
        alert('Cannot convert ' + actnumber.length + ' digit number');
        return '';
    }
    var iWords = ["Zero", " One", " Two", " Three", " Four", " Five", " Six", " Seven", " Eight", " Nine"];
    var ePlace = ['Ten', ' Eleven', ' Twelve', ' Thirteen', ' Fourteen', ' Fifteen', ' Sixteen', ' Seventeen', ' Eighteen', ' Nineteen'];
    var tensPlace = ['dummy', ' Ten', ' Twenty', ' Thirty', ' Forty', ' Fifty', ' Sixty', ' Seventy', ' Eighty', ' Ninety'];
    var hundsPlace = ['dummy', ' One Hundred', ' Two Hundred', ' Three Hundred', ' Four Hundred', ' Five Hundred', ' Six Hundred', ' Seven Hundred', ' Eight Hundred', ' Nine Hundred'];
    var thousPlace = ['dummy', ' One Thousand', ' Two Thousand', ' Three Thousand', ' Four Thousand', ' Five Thousand', ' Six Thousand', ' Seven Thousand', ' Eight Thousand', ' Nine Thousand'];
    var tensthousPlace = ['dummy', ' Ten Thousand', ' Twenty Thousand', ' Thirty Thousand', ' Forty Thousand', ' Fifty Thousand', ' Sixty Thousand', ' Seventy Thousand', ' Eighty Thousand', ' Ninety Thousand'];
    var iWordsLength = numReversed.length;
    var totalWords = "";
    var inWords = new Array();
    var finalWord = "";
    j = 0;
    for (i = 0; i < iWordsLength; i++) {
        switch (i) {
            case 0:
                if (actnumber[i] == 0 || actnumber[i + 1] == 1) {
                    inWords[j] = '';
                }
                else {
                    inWords[j] = iWords[actnumber[i]];
                }
                inWords[j] = inWords[j]; 
                break;
            case 1:
                tens_complication();
                break;
            case 2:
                if (actnumber[i] == 0) {
                    inWords[j] = '';
                }
                else if (actnumber[i - 1] != 0 && actnumber[i - 2] != 0) {
                    
                    inWords[j] = iWords[actnumber[i]] + ' Hundred ';
                }
                else {
                    inWords[j] = iWords[actnumber[i]] + ' Hundred';
                }
                break;
            case 3:
                if (actnumber[i] == 0 || actnumber[i + 1] == 1) {
                    inWords[j] = '';
                }
                else {
                    inWords[j] = iWords[actnumber[i]];
                }
                if (actnumber[i + 1] != 0 || actnumber[i] > 0) {
                    inWords[j] = inWords[j] + " Thousand";
                }
                break;
            case 4:
                tens_complication();
                break;
            case 5:
                if (actnumber[i] == 0 || actnumber[i + 1] == 1) {
                    inWords[j] = '';
                }
                else {
                    inWords[j] = iWords[actnumber[i]];
                }
                inWords[j] = inWords[j] + " Lakh";
                break;
            case 6:
                tens_complication();
                break;
            case 7:
                if (actnumber[i] == 0 || actnumber[i + 1] == 1) {
                    inWords[j] = '';
                }
                else {
                    inWords[j] = iWords[actnumber[i]];
                }
                inWords[j] = inWords[j] + " Crore";
                break;
            case 8:
                tens_complication();
                break;
            case 9:
                if (actnumber[i] == 0 || actnumber[i + 1] == 1) {
                    inWords[j] = '';
                }
                else {
                    inWords[j] = hundsPlace[actnumber[i]];
                }
                break;
            case 10:
                if (actnumber[i] == 0 || actnumber[i + 1] == 1) {
                    inWords[j] = '';
                }
                else if (i == iWordsLength-1 && actnumber[i] > 0) {
                    inWords[j] = iWords[actnumber[i]] + ' Thousand';
                }
                else if (actnumber[i + 1] > 0 && actnumber[i] > 0) {
                    inWords[j] = tensPlace[actnumber[i + 1]] + iWords[actnumber[i]] + ' Thousand';
                }
                else {
                    inWords[j] = thousPlace[actnumber[i]];
                }
                break;
            case 11:
                if (actnumber[i] == 0 || actnumber[i + 1] == 1) {
                    inWords[j] = '';
                }
                else if (actnumber[i - 1] == 0) {
                    inWords[j] = tensthousPlace[actnumber[i]];
                }
                break;
            default:
                break;
        }
        j++;
    }
    function tens_complication() {
        if (actnumber[i] == 0) {
            inWords[j] = '';
        }
        else if (actnumber[i] == 1) {
            inWords[j] = ePlace[actnumber[i - 1]];
        }
        else {
            inWords[j] = tensPlace[actnumber[i]];
        }
    }
    inWords.reverse();
    for (i = 0; i < inWords.length; i++) {
        switch (inWords[i]) {
            case " Thousand":
                if (i > 0) {
                    if (inWords[i - 1] != "")
                        finalWord += inWords[i];
                }
                else
                    finalWord += inWords[i];
                break;
            case " Lakh":
                if (i > 0) {
                    if (inWords[i - 1] != "")
                        finalWord += inWords[i];
                }
                else
                    finalWord += inWords[i];
                break;
            case " Crore":
                if (i > 0) {
                    if (inWords[i - 1] != "")
                        finalWord += inWords[i];
                    else
                        finalWord += inWords[i];
                }
                else
                    finalWord += inWords[i];
                break;
            default:
                finalWord += inWords[i];
                break;
        }
    }
    
    return finalWord;
}
 
Share this answer
 
v2
SQL
The given code can explain to convert the number to word in crystal report

1) right click on your formula field
2) click edit formula
3) formula workshop window will open
4) left side of the window u have 'report custom functions'
5)right click and select new
6)give the name for function


VB
Function ConvertDigit(MyDigit as string)
        if Val(MyDigit) = 1 then
                    ConvertDigit  = "One"
        elseif Val(MyDigit) = 2 then
                    ConvertDigit  = "Two "
        elseif Val(MyDigit) = 3 then
                   ConvertDigit  = "Three "
        elseif Val(MyDigit) = 4 then
                    ConvertDigit  = "Four "
        elseif Val(MyDigit) = 5 then
                    ConvertDigit  = "Five "
        elseif Val(MyDigit) = 6 then
                    ConvertDigit  = "Six "
        elseif Val(MyDigit) = 7 then
                    ConvertDigit  = "Seven "
        elseif Val(MyDigit) = 8 then
                    ConvertDigit  = "Eight "
        elseif Val(MyDigit) = 9 then
                    ConvertDigit  = "Nine "
end if
    End Function


7) and the right top you have a drop down. it shows 'Crystal Syntax' as default
8)change this to basic syntax.
9) save the function.

do the above steps for the following functions also

VB
Function ConvertHundreds(MyNumber as string)
        Dim Result As String

        ' Exit if there is nothing to convert.
        If Val(MyNumber) = 0 Then Exit Function

        ' Append leading zeros to number.
        MyNumber = Right("000" & MyNumber, 3)

        ' Do we have a hundreds place digit to convert?
        If Left(MyNumber, 1) <> "0" Then
            Result = ConvertDigit(Left(MyNumber, 1)) & " Hundreds "
        End If

        ' Do we have a tens place digit to convert?
        If Mid(MyNumber, 2, 1) <> "0" Then
            Result = Result & ConvertTens(Mid(MyNumber, 2))
        Else
            ' If not, then convert the ones place digit.
            Result = Result & ConvertDigit(Mid(MyNumber, 3))
        End If

        ConvertHundreds = Trim(Result)
    End Function
Function ConvertTens(MyTens as string)
        Dim Result As String

        ' Is value between 10 and 19?
        If Val(Left(MyTens, 1)) = 1 Then
            if Val(MyTens) = 10 then
                Result = "Ten"
            elseif Val(MyTens) = 11 then
                Result = "Eleven"
            elseif Val(MyTens) = 12 then
                Result = "Twelve"
            elseif Val(MyTens) = 13 then
                Result = "Thirteen"
            elseif Val(MyTens) = 14 then
                Result = "Fourteen"
            elseif Val(MyTens) = 15 then
                Result = "Fifteen"
            elseif Val(MyTens) = 16 then
                Result = "Sixteen"
            elseif Val(MyTens) = 17 then
                Result = "Seventeen"
            elseif Val(MyTens) = 18 then
                Result = "Eighteen"
            elseif Val(MyTens) = 19 then
                Result = "Nineteen"
            end if
        Else
            if Val(Left(MyTens, 1)) = 2 then
                Result = "Twenty "
            elseif Val(Left(MyTens, 1)) = 3 then
                Result = "Thirty "

            elseif Val(Left(MyTens, 1)) = 4 then
                Result = "Forty "

            elseif Val(Left(MyTens, 1)) = 5 then
                Result = "Fifty "

            elseif Val(Left(MyTens, 1)) = 6 then
                Result = "Sixty "

            elseif Val(Left(MyTens, 1)) = 7 then
                Result = "Seventy "
            elseif Val(Left(MyTens, 1)) = 8 then
                Result = "Eighty "
            elseif Val(Left(MyTens, 1)) = 9 then
                Result = "Ninety "
        end if
 Result = Result & ConvertDigit(Right(cstr(MyTens), 1))
end if
ConvertTens = Result
    End Function
 Function RupeesToWord(MyNumber As string)
        Dim Temp
        Dim Rupees, Paisa As String
        Dim DecimalPlace, iCount
        Dim Hundreds, Words As String
        MyNumber = Trim(CStr(MyNumber))
        DecimalPlace = InStr(MyNumber, ".")
          If DecimalPlace > 0 Then
                      Temp = Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2)
            Paisa = " and " & ConvertTens(Temp) & " Paisa"

            ' Strip off paisa from remainder to convert.
            MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))

          if Paisa= " and  Paisa" then
Paisa=""
end if

        End If
        Dim TM As String  ' If MyNumber between Rs.1 To 99 Only.
        TM = Right(MyNumber, 2)

        If Len(MyNumber) > 0 And Len(MyNumber) <= 2 Then
            If Len(TM) = 1 Then
                Words = ConvertDigit(TM)

          if Paisa= " and  Paisa" then
                 Paisa=""
          end if
                RupeesToWord = "Rupees " & Words & Paisa & " Only"
   Exit Function

            Else
                If Len(TM) = 2 Then
                    Words = ConvertTens(TM)
          if Paisa= " and  Paisa" then
                 Paisa=""
          end if
                    RupeesToWord = "Rupees " & Words & Paisa & " Only"
                    Exit Function
                End If
            End If
        End If
              Hundreds = ConvertHundreds(Right(MyNumber, 3))
         MyNumber = Left(MyNumber, Len(MyNumber) - 3)
        iCount = 0
        Do While MyNumber <> ""
            'Strip last two digits
            Temp = Right(MyNumber, 2)
                     dim str1 as string
                     if iCount = 0 then
                        str1 =" Thousand "
                    elseif iCount = 2 then
                        str1 =" Lakh "
                    elseif iCount =4 then
                        str1 =" Crore "
                    elseif iCount =6 then
                        str1 =" Arab "
                    elseif iCount =8 then
                        str1 =" Kharab "
                    end if
            If Len(MyNumber) = 1 Then
                If Trim(Words) = "Thousand" Or _
                Trim(Words) = "Lakh  Thousand" Or _
                Trim(Words) = "Lakh" Or _
                Trim(Words) = "Crore" Or _
                Trim(Words) = "Crore  Lakh  Thousand" Or _
                Trim(Words) = "Arab  Crore  Lakh  Thousand" Or _
                Trim(Words) = "Arab" Or _
                Trim(Words) = "Kharab  Arab  Crore  Lakh  Thousand" Or _
                Trim(Words) = "Kharab" Then

                   Words = ConvertDigit(Temp) & str1
                    MyNumber = Left(MyNumber, Len(MyNumber) - 1)
                Else
                    Words = ConvertDigit(Temp) & str1 & Words
                    MyNumber = Left(MyNumber, Len(MyNumber) - 1)
                End If
            Else
                If Trim(Words) = "Thousand" Or _
                   Trim(Words) = "Lakh  Thousand" Or _
                   Trim(Words) = "Lakh" Or _
                   Trim(Words) = "Crore" Or _
                   Trim(Words) = "Crore  Lakh  Thousand" Or _
                   Trim(Words) = "Arab  Crore  Lakh  Thousand" Or _
                   Trim(Words) = "Arab" Then
                    Words = ConvertTens(Temp) & str1
                    MyNumber = Left(MyNumber, Len(MyNumber) - 2)
                Else
                     If Trim(ConvertTens(Temp) & str1) = "Lakh" Or _
                       Trim(ConvertTens(Temp) & str1) = "Crore" Or _
                       Trim(ConvertTens(Temp) & str1) = "Arab" Then

                        Words = Words
                        MyNumber = Left(MyNumber, Len(MyNumber) - 2)
                    Else
                        Words = ConvertTens(Temp) & str1 & Words
                        MyNumber = Left(MyNumber, Len(MyNumber) - 2)
                    End If
                End If
            End If
            iCount = iCount + 2
        Loop
     if Paisa= " and  Paisa" then
        Paisa=""
     end if
         RupeesToWord = "Rupees " & Words & Hundreds & Paisa & " Only"
 end Function
 
Share this answer
 
Comments
fjdiewornncalwe 4-Apr-13 15:23pm    
Plagiarized from source
SumitSaha 8-Apr-14 15:27pm    
Excellently working
MUYIDEEN KAZEEM KAZEEM 6-Aug-14 11:35am    
Not working for me i only get Rupee Arab Only and Rupee Kharab Kharab only and the spelling is nt in UK format. please help
Formula =
"Rs. "+ towords(tonumber(mid(Cstr({@MTotlAmt}),1,Instr(Cstr({@MTotlAmt}),".",1)-1)),0) + " and " + ToWords (tonumber(mid(Cstr({@MTotlAmt}),Instr(Cstr({@MTotlAmt}),".",1)+1,len(Cstr({@MTotlAmt})))),0) + " Paise only"
 
Share this answer
 
v2
Improved answer...
because above answer not working for 1 crore amount...
its showing one crore one lakhs only.. which is wrong..
you can try below answer by doing some modification

numbervar RmVal:=0;
numbervar Amt:=0;
numbervar pAmt:=0;
stringvar InWords :="";
Amt := Round({@Total Amount},0);
if Amt > 10000000 then RmVal := truncate(Amt/10000000);
if Amt = 10000000 then RmVal := 1;
if RmVal = 1 then
InWords := InWords + " " + towords(RmVal,0) + " crore"
else
if RmVal > 1 then InWords := InWords + " " + towords(RmVal,0) + " crores";

Amt := Amt - Rmval * 10000000;
if Amt > 100000 then RmVal := truncate(Amt/100000);
if Amt = 100000 then RmVal := 1;
if Amt = 0 then RmVal := 0;
if RmVal >= 1 then
InWords := InWords + " " + towords(RmVal,0) + " lakhs";

Amt := Amt - Rmval * 100000;
if Amt > 0 then InWords := InWords + " " + towords(truncate(Amt),0);
pAmt := (Amt - truncate(Amt)) * 100;
if pAmt > 0 then
InWords := InWords + " and " + towords(pAmt,0) + " paisa only"
else
InWords := InWords + " only";
UPPERCASE(InWords)
 
Share this answer
 
numberVar crorecount := Int(Sum({ViewBillInvoice.Total_Price})/10000000) ; //count of crores
numberVar lakhcount := Int(Int(Sum({ViewBillInvoice.Total_Price}) - (crorecount*10000000))/100000); //count of lakhs
numberVar decimal :=(Sum({ViewBillInvoice.Total_Price}))- Int (Sum({ViewBillInvoice.Total_Price})) ; //decimal part

stringvar croreSubStr := iif(crorecount>0,ToWords(crorecount,0)+' crore',''); //string you will add at the final result if number is greater of 10000000
stringvar lakhSubStr := iif(lakhcount>0,ToWords(lakhcount,0)+' lakh',''); //string you will add at the final result if number is greater of 100000

ProperCase (croreSubStr+' '+lakhSubStr+' '+ToWords (Int (Sum({ViewBillInvoice.Total_Price})-(crorecount*10000000)-(lakhcount*100000)),0) + ', and ' + ToWords ((decimal * 100),0) + ' Paise Only')
 
Share this answer
 
Comments
Richard Deeming 20-Feb-19 9:49am    
An unexplained code-dump is not a solution. How about explaining what your solution provides that the many other solutions don't?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900