Create the BDT function to convert numbers to words (Bangladeshi Taka)

1.       Use the keyboard shortcut, Alt + F11 to open the Visual Basic Editor (VBE).

2.       Click the Insert tab, and click Module.

3.       Copy the following lines of code.

Function BDT(ByVal MyNumber, Optional Upto As String)

        Dim Taka, Paise, Temp

    Dim DecimalPlace, Count

    Dim i As Long

    Dim Bangladeshi

    Dim Place(99)

    Bangladeshi = Array("", "", " Thousand ", " Lakh ", " Crore ", " Hundred ", " Thousand ", " Lakh ", " Crore ")

    If UCase(Upto) Like "[!TLCANPS]" Or Upto = "" Then

    For i = 0 To UBound(Bangladeshi)

        Place(i) = Bangladeshi(i)

    Next i

    Else

    For i = 0 To InStr(1, " TLCANPS", UCase(Upto))

      Place(i) = Bangladeshi(i)

    Next i

    End If

    DecimalPlace = InStr(MyNumber, ".")

    ' Convert Paise and set MyNumber to Taka amount

    If DecimalPlace > 0 Then

        Paise = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))

        MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))

    End If

    Count = 1

    Do While MyNumber <> ""

        If Count = 1 Then Temp = GetHundreds(Right(MyNumber, 3))

        If Count > 1 Then Temp = GetHundreds(Right(MyNumber, 2))

        If Place(Count) <> "" Then

            If Temp <> "" Then Taka = Temp & Place(Count) & Taka

        Else

            If Temp <> "" Then Taka = Temp & " " & Taka

        End If

        If Count = 1 And Len(MyNumber) > 3 Then

            MyNumber = Left(MyNumber, Len(MyNumber) - 3)

        Else

            If Count > 1 And Len(MyNumber) > 2 Then

                MyNumber = Left(MyNumber, Len(MyNumber) - 2)

            Else

                MyNumber = ""

            End If

        End If

        Count = Count + 1

     Loop

    Select Case Taka

    Case ""

        Taka = "Zero Taka"

    Case "One"

        Taka = "One Taka"

    Case Else

        Taka = Taka & " Taka "

       

    End Select

    Select Case Paise

    Case ""

        Paise = ""

    Case "One"

        Paise = " and One Paisa"

    Case Else

        Paise = " and " & Paise & " Paisa "

    End Select

    BDT = Taka & Paise & " Only"

End Function

 ' Converts a number from 100-999 into text

Function GetHundreds(ByVal MyNumber)

    Dim Result As String

    If Val(MyNumber) = 0 Then Exit Function

    MyNumber = Right("000" & MyNumber, 3) 'Convert the hundreds place

    If Mid(MyNumber, 1, 1) <> "0" Then

        Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "

    End If

     'Convert the tens and ones place

    If Mid(MyNumber, 2, 1) <> "0" Then

        Result = Result & GetTens(Mid(MyNumber, 2))

    Else

        Result = Result & GetDigit(Mid(MyNumber, 3))

    End If

    GetHundreds = Result

End Function

  ' Converts a number from 10 to 99 into text

Function GetTens(TensText)

    Dim Result As String

    Result = "" ' null out the temporary function value

    If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19

        Select Case Val(TensText)

        Case 10: Result = "Ten"

        Case 11: Result = "Eleven"

        Case 12: Result = "Twelve"

        Case 13: Result = "Thirteen"

        Case 14: Result = "Fourteen"

        Case 15: Result = "Fifteen"

        Case 16: Result = "Sixteen"

        Case 17: Result = "Seventeen"

        Case 18: Result = "Eighteen"

        Case 19: Result = "Nineteen"

        Case Else

        End Select

    Else ' If value between 20-99

        Select Case Val(Left(TensText, 1))

        Case 2: Result = "Twenty "

        Case 3: Result = "Thirty "

        Case 4: Result = "Forty "

        Case 5: Result = "Fifty "

        Case 6: Result = "Sixty "

        Case 7: Result = "Seventy "

        Case 8: Result = "Eighty "

        Case 9: Result = "Ninety "

        Case Else

        End Select

        Result = Result & GetDigit (Right(TensText, 1)) 'Retrieve ones place

    End If

    GetTens = Result

End Function

 ' Converts a number from 1 to 9 into text

Function GetDigit(Digit)

    Select Case Val(Digit)

    Case 1: GetDigit = "One"

    Case 2: GetDigit = "Two"

    Case 3: GetDigit = "Three"

    Case 4: GetDigit = "Four"

    Case 5: GetDigit = "Five"

    Case 6: GetDigit = "Six"

    Case 7: GetDigit = "Seven"

    Case 8: GetDigit = "Eight"

    Case 9: GetDigit = "Nine"

    Case Else: GetDigit = ""

    End Select

End Function

 

4.       Paste the lines of code into the Module1 (Code) box.

5.       Press Alt + Q to return to Excel. The BDT function is now ready to use.

 Use the BDT function in individual cells

1.       Type the formula =BDT(A1) into the cell where you want to display a written number, where A1 is the cell containing the number you want to convert. You can also manually type the value like =BDT(22.50).

2.       Press Enter to confirm the formula.

Save your BDT function workbook

Excel cannot save a workbook with macro functions in the standard macro-free workbook format (.xlsx). If you click File > Save. A VB project dialog box opens. Click No.

You can save your file as an Excel Macro-Enabled Workbook (.xlsm) to keep your file in its current format.

1.       Click File > Save As.

2.       Click the Save as type drop-down menu, and select Excel Macro-Enabled Workbook.

3.       Click Save.