This function is something I wrote myself on request of one of my community member from my MS Excel community on Orkut. Through this post I am also attempting to teach blog readers how to move code to Visual Basic Editor for use.

The Function will convert 122 into One Hundred and Twenty Two

1. Press ALT + F11 to start Vb Editor

2. Insert Module

3. Copy paste the entire code into module

4. Check the function in the list of User Define Function.

e.g = N2T(a2)

Where a2 cell contain numeric value

**'The below program is written to convert the number into text 'The logic in solely developed by Sarfaraz Ahmed and is not referred from any internet on internet or any book 'Hence, user in this forum can freely use the function. 'User are requested not to remove the comments**

Dim fplace(0 To 20) As String

Dim splace(0 To 10) As String

Dim PlaceName(1 To 5) As String **Private Sub LoadValue()**

fplace(0) = ""

fplace(1) = "One"

fplace(2) = "Two"

fplace(3) = "Three"

fplace(4) = "Four"

fplace(5) = "Five"

fplace(6) = "Six"

fplace(7) = "Seven"

fplace(8) = "Eight"

fplace(9) = "Nine"

fplace(10) = "Ten"

fplace(11) = "Eleven"

fplace(12) = "Twelve"

fplace(13) = "Thirteen"

fplace(14) = "Fourteen"

fplace(15) = "Fifteen"

fplace(16) = "Sixteen"

fplace(17) = "Seventeen"

fplace(18) = "Eighteen"

fplace(19) = "Nineteen"

fplace(20) = "Twenty"

splace(0) = ""

splace(1) = "Ten"

splace(2) = "Twenty"

splace(3) = "Thirty"

splace(4) = "Forty"

splace(5) = "Fifty"

splace(6) = "Sixty"

splace(7) = "Seventy"

splace(8) = "Eight"

splace(9) = "Ninety"

splace(10) = "Hundredth"

PlaceName(1) = "Hundred"

PlaceName(2) = "Thousand"

PlaceName(3) = "Lakh"

PlaceName(4) = "Crore" **End Sub**

**Function N2T(DbVal As Double) As String**

LoadValue

Dim LFTPART As String

Dim RGTPART As String

Dim RGTLEN As Integer

Dim LFTLEN As Integer

If Len(Trim(DbVal)) < 3 Then

N2T = D2T(DbVal)

ElseIf Len(Trim(DbVal)) = 3 Then

N2T = D2T(Left(DbVal, 1)) & " " & CheckZero(Left(DbVal, 1), "Hundred") & " " & CheckZero(Right(DbVal, 2), "And") & " " & D2T(Right(DbVal, 2))

ElseIf Len(Trim(DbVal)) = 4 Then

N2T = D2T(Left(DbVal, 1)) & " " & CheckZero(Left(DbVal, 1), "Thousand") & " " & D2T(Mid(DbVal, 2, 1)) & " " & CheckZero(Mid(DbVal, 2, 1), "Hundred") & " " & CheckZero(Right(DbVal, 2), "And") & " " & D2T(Right(DbVal, 2))

ElseIf Len(Trim(DbVal)) = 4 Then

N2T = D2T(Left(DbVal, 1)) & " " & CheckZero(Left(DbVal, 1), "Thousand") & " " & D2T(Mid(DbVal, 2, 1)) & " " & CheckZero(Mid(DbVal, 2, 1), "Hundred") & " " & CheckZero(Right(DbVal, 2), "And") & " " & D2T(Right(DbVal, 2))

ElseIf Len(Trim(DbVal)) = 5 Then

N2T = D2T(Left(DbVal, 2)) & " " & CheckZero(Left(DbVal, 1), "Thousand") & " " & D2T(Mid(DbVal, 3, 1)) & " " & CheckZero(Mid(DbVal, 3, 1), "Hundred") & " " & CheckZero(Right(DbVal, 2), "And") & " " & D2T(Right(DbVal, 2))

ElseIf Len(Trim(DbVal)) = 6 Then

N2T = D2T(Left(DbVal, 1)) & " " & CheckZero(Left(DbVal, 1), "Lakh") & " " & D2T(Mid(DbVal, 2, 2)) & " " & CheckZero(Mid(DbVal, 2, 2), "Thousand") & " " & D2T(Mid(DbVal, 4, 1)) & " " & CheckZero(Mid(DbVal, 4, 1), "Hundred") & " " & CheckZero(Right(DbVal, 2), "And") & " " & D2T(Right(DbVal, 2))

ElseIf Len(Trim(DbVal)) = 7 Then

N2T = D2T(Left(DbVal, 2)) & " " & CheckZero(Left(DbVal, 2), "Lakh") & " " & D2T(Mid(DbVal, 3, 2)) & " " & CheckZero(Mid(DbVal, 3, 2), "Thousand") & " " & D2T(Mid(DbVal, 5, 1)) & " " & CheckZero(Mid(DbVal, 5, 1), "Hundred") & " " & CheckZero(Right(DbVal, 2), "And") & " " & D2T(Right(DbVal, 2))

ElseIf Len(Trim(DbVal)) = 8 Then

N2T = D2T(Left(DbVal, 1)) & " " & CheckZero(Left(DbVal, 1), "Crore") & " " & D2T(Mid(DbVal, 2, 2)) & " " & CheckZero(Mid(DbVal, 2, 2), "Lakh") & " " & D2T(Mid(DbVal, 4, 2)) & " " & CheckZero(Mid(DbVal, 4, 2), "Thousand") & " " & D2T(Mid(DbVal, 6, 1)) & " " & CheckZero(Mid(DbVal, 6, 1), "Hundred") & " " & CheckZero(Right(DbVal, 2), "And") & " " & D2T(Right(DbVal, 2))

ElseIf Len(Trim(DbVal)) = 9 Then

N2T = D2T(Left(DbVal, 2)) & " " & CheckZero(Left(DbVal, 2), "Crore") & " " & D2T(Mid(DbVal, 3, 2)) & " " & CheckZero(Mid(DbVal, 3, 2), "Lakh") & " " & D2T(Mid(DbVal, 5, 2)) & " " & CheckZero(Mid(DbVal, 5, 2), "Thousand") & " " & D2T(Mid(DbVal, 7, 1)) & " " & CheckZero(Mid(DbVal, 7, 1), "Hundred") & " " & CheckZero(Right(DbVal, 2), "And") & " " & D2T(Right(DbVal, 2))

Else

N2T = "Value is too big, function support upto 9 digits only"

End If

**End Function** **Private Function D2T(intVal As Double) As String **If intVal > 20 Then

D2T = splace(Left(intVal, 1)) & " " & fplace(Right(intVal, 1))

Else

D2T = fplace(intVal)

End If

End Function

Private Function CheckZero(intVal As Double, PlaceName As String) As String

If intVal = 0 Then

CheckZero = ""

Else

CheckZero = PlaceName

End If

**End Function**

## 4 comments:

Dear cud u let me know how to execute above code... as I have pasted entire code in Module.

Plz send yur reply at kamalgulati7@gmail.com

Thanks & regards,

Kamal Gulati

January 28, 2009 at 9:33 AMDear,

Cud u let me know how to excute this code in Excel as I have pasted entire code in Module.

Plz send yur replt at kamalgulati7@gmail.com

Thanks and regards,

Kamal

January 28, 2009 at 9:34 AMStill I cannot run the code... Plz help kamalgulati7@gmail.com ..

I am from delhi...

Thanks in advance for yur help!

regards,

Kamal

January 30, 2009 at 11:37 PMPlz help as I am nt able to run the code. Kamalgulati7@gmail.com

Thanks in advance!!

January 30, 2009 at 11:42 PM## Post a Comment