Friday, February 25, 2011

Number to Word Conversion by Microsoft Excel

Wouldn't it better if your numbers are converted to words automatically in Microsoft Excel? Yes, it is possible with little effort. Just copy the following code to Visual Basic editor in Microsoft Excel.
Step by Step Guide:
1) Open a blank 'worksheet' in Microsoft Excel.
2) Click Tools>Macro>Visual Basic Editor.
(Visual Basic Editor will be open)



3) Click Insert>Module from Visula Basic Editor.
(a blank module window will be open)

4) Pest the following code to the Module . (blue-colored texts)
'http://skydeepblue.blogspot.com/
' Words Macro - Functions to convert number into words
' WordsMB - converts number into words in Million, Billion
' WordsLC - converts number into words in lac, crore

Function WordsMB(ByVal nAmt As Currency, Optional ByVal cents) As String
If IsMissing(cents) Then cents = "Cents"
Dim sAmt, w As String
Dim b, m, t As Boolean
Dim v As Integer
sAmt = Format(nAmt, "000000000000000.00")
' 999, 999, 999, 999, 999 . 99
' 9 h 99 t 9 h 99 b 9 h 99 m 9 h 99 t 9 h 99 and paisa 99
b = False
m = False
t = False
v = Val(Mid(sAmt, 1, 1))
w = ""
If v > 0 Then
b = True
t = True
w = w + wd9(v)
End If
v = Val(Mid(sAmt, 2, 2))
If v > 0 Then
b = True
t = True
w = w + wd2(v)
End If
If t Then w = w + "Thousand "
v = Val(Mid(sAmt, 4, 1))
If v > 0 Then
b = True
w = w + wd9(v) + "Hundred "
End If
v = Val(Mid(sAmt, 5, 2))
If v > 0 Then
b = True
w = w + wd2(v)
End If
If b Then w = w + "Billion "
v = Val(Mid(sAmt, 7, 1))
If v > 0 Then
m = True
w = w + wd9(v) + "Hundred "
End If
v = Val(Mid(sAmt, 8, 2))
If v > 0 Then
m = True
w = w + wd2(v)
End If
If m Then w = w + "Million "
v = Val(Mid(sAmt, 10, 1))
t = False
If v > 0 Then
t = True
w = w + wd9(v) + "Hundred "
End If
v = Val(Mid(sAmt, 11, 2))
If v > 0 Then
t = True
w = w + wd2(v)
End If
If t Then w = w + "Thousand "
v = Val(Mid(sAmt, 13, 1))
If v > 0 Then w = w + wd9(v) + "Hundred "
v = Val(Mid(sAmt, 14, 2))
If v > 0 Then w = w + wd2(v)
v = Val(Mid(sAmt, 17, 2))
If v > 0 Then
w = w + "and " + cents + " " + wd2(v) + "Only."
Else
w = w + "Only."
End If
WordsMB = w
End Function
Function WordsST(ByVal nAmt As Currency, Optional ByVal cents) As String
If IsMissing(cents) Then cents = "Pence"
Dim sAmt, w As String
Dim b, m, t As Boolean
Dim v As Integer
sAmt = Format(nAmt, "000000000000000.00")

' 999, 999, 999, 999, 999 . 99
' 9 h 99 t 9 h 99 b 9 h 99 m 9 h 99 t 9 h 99 and paisa 99
b = False
m = False
t = False
v = Val(Mid(sAmt, 1, 1))
w = ""
If v > 0 Then
b = True
t = True
w = w + wd9(v)
End If
v = Val(Mid(sAmt, 2, 2))
If v > 0 Then
b = True
t = True
w = w + wd2(v)
End If
If t Then w = w + "Thousand "
v = Val(Mid(sAmt, 4, 1))
If v > 0 Then
b = True
w = w + wd9(v) + "Hundred "
End If
v = Val(Mid(sAmt, 5, 2))
If v > 0 Then
b = True
w = w + wd2(v)
End If
If b Then w = w + "Billion "
v = Val(Mid(sAmt, 7, 1))
If v > 0 Then
m = True
w = w + wd9(v) + "Hundred "
End If
v = Val(Mid(sAmt, 8, 2))
If v > 0 Then
m = True
w = w + wd2(v)
End If
If m Then w = w + "Million "
v = Val(Mid(sAmt, 10, 1))
t = False
If v > 0 Then
t = True
w = w + wd9(v) + "Hundred "
End If
v = Val(Mid(sAmt, 11, 2))
If v > 0 Then
t = True
w = w + wd2(v)
End If
If t Then w = w + "Thousand "
v = Val(Mid(sAmt, 13, 1))
If v > 0 Then w = w + wd9(v) + "Hundred "
v = Val(Mid(sAmt, 14, 2))
If v > 0 Then w = w + wd2(v)
v = Val(Mid(sAmt, 17, 2))
If v > 0 Then
w = w + "and " + cents + " " + wd2(v) + "Only."
Else
w = w + "Only."
End If
WordsST = w
End Function

Function WordsLC(ByVal nAmt As Currency, Optional ByVal cents) As String
If IsMissing(cents) Then cents = "Paisa"
Dim sAmt, w As String
Dim c As Boolean
Dim v As Integer
sAmt = Format(nAmt, "000000000000000.00")
' 9 99, 99, 9 99, 99 99, 9 99 . 99
' 9 c 99 l 99 t 9 h 99 c 99 l 99 t 9 h 99 and paisa 99
c = False
v = Val(Mid(sAmt, 1, 1))
w = ""
If v > 0 Then
w = wd9(v) + "Crore "
End If
v = Val(Mid(sAmt, 2, 2))
If v > 0 Then
c = True
w = w + wd2(v) + "Lac "
End If
v = Val(Mid(sAmt, 4, 2))
If v > 0 Then
c = True
w = w + wd2(v) + "Thousand "
End If
v = Val(Mid(sAmt, 6, 1))
If v > 0 Then
c = True
w = w + wd9(v) + "Hundred "
End If
v = Val(Mid(sAmt, 7, 2))
If v > 0 Then
c = True
w = w + wd2(v)
End If
If c Then w = w + "Crore "
v = Val(Mid(sAmt, 9, 2))
If v > 0 Then
w = w + wd2(v) + "Lac "
End If
v = Val(Mid(sAmt, 11, 2))
If v > 0 Then
w = w + wd2(v) + "Thousand "
End If
If t Then w = w + "Thousand "
v = Val(Mid(sAmt, 13, 1))
If v > 0 Then w = w + wd9(v) + "Hundred "
v = Val(Mid(sAmt, 14, 2))
If v > 0 Then w = w + wd2(v)
v = Val(Mid(sAmt, 17, 2))
If v > 0 Then
w = w + "and " + cents + " " + wd2(v) + "Only."
Else
w = w + "Only."
End If
WordsLC = w
End Function

Private Function wd2(ByVal v2 As Integer) As String
' Retuns null to Ninety Nine for parameter value 0 to 99
' It calls functions wd9, wd19 or ties as needed
If v2 < 20 Then
If v2 < 10 Then wd2 = wd9(v2) Else wd2 = wd19(v2)
Else
wd2 = ties(Int(v2 / 10)) + wd9(v2 - (Int(v2 / 10) * 10))
End If
End Function

Private Function ties(ByVal v2 As Integer) As String
' Returns twenty to Ninety for parameter value 2 to 9
Select Case v2
Case 2
ties = "Twenty "
Case 3
ties = "Thirty "
Case 4
ties = "Forty "
Case 5
ties = "Fifty "
Case 6
ties = "Sixty "
Case 7
ties = "Seventy "
Case 8
ties = "Eighty "
Case 9
ties = "Ninety "
Case Else
ties = ""
End Select
End Function

Function wd9(ByVal v2 As Integer) As String
' Returns null to Nine for parameter value 0 to 9
Select Case v2
Case 0
wd9 = ""
Case 1
wd9 = "One "
Case 2
wd9 = "Two "
Case 3
wd9 = "Three "
Case 4
wd9 = "Four "
Case 5
wd9 = "Five "
Case 6
wd9 = "Six "
Case 7
wd9 = "Seven "
Case 8
wd9 = "Eight "
Case 9
wd9 = "Nine "
Case Else
wd9 = ""
End Select
End Function

Private Function wd19(ByVal v2 As Integer) As String
' Returns ten to Nineteen for parameter value 10 to 19
Select Case v2
Case 10
wd19 = "Ten "
Case 11
wd19 = "Eleven "
Case 12
wd19 = "Twelve "
Case 13
wd19 = "Thirteen "
Case 14
wd19 = "Fourteen "
Case 15
wd19 = "Fifteen "
Case 16
wd19 = "Sixteen "
Case 17
wd19 = "Seventeen "
Case 18
wd19 = "Eighteen "
Case 19
wd19 = "Nineteen "
Case Else
wd19 = ""
End Select
End Function



Sub SkyDeepBlue()

End Sub







5) Close the Visual Basic Editor window and return to 'worksheet'.
6) Select any 'cell', where you desire to put the function and then click Insert>Functions.

Select 'User Defined' functions from drop-down list and select 'WordsLC' or 'WordsMB' or 'WordsST' Function; put amount to the 'nAmt' box or type cell number to convet word.
7) See the result.

Friday, February 18, 2011

Bibi Ayesha Escaped from Nightmare

দুঃস্বপ্ন কাটিয়ে উঠেছেন বিবি আয়েশা।।


সবার নিশ্বয়ই টাইম ম্যাগাজিনের সেই বিভৎস নাক, কান কাটা তরুনীর প্রচ্ছদ মনে আছে? যে মেয়েটি কিনা নাক, কান কাটা অবস্থায় কোন রকম শ্বশুররাড়ি থেকে পালিয়ে এসেছিল। আজ একবছর পর সেই মেয়েটিকেই হয়তো আপনি আবিস্কার করবেন নিউইয়র্কের পাতাল রেলে ঠিক আপনারই পাশে। কি ভাবে সম্ভব? দীর্ঘ আট মাস চিকিৎসার পর চিকিৎসকরা আয়েশার কৃত্রিম কান ও নাক জুড়ে দিতে পেরেছেন। ফলে আর দশটি স্বাভাবিক মেয়ের মতোই সে এখন চলাচল করেতে পারছে। আয়েশা এখন একটি গয়নার দোকানে পুথি লাগানো কাজ করছে। প্রতিদিন তাকে পাতাল রেলে যাতায়াত করতে হয়। জিন্সের প্যান্ট পরে আয়েশা যখন নিউইয়র্কের রাস্তায় যাতায়াত করেন, তখন তার সত্যই মনে হয়, তিনি আস্তে আস্তে দুঃস্বপ্ন কাটিয়ে উঠছেন।