numeric value into English words

D

danushka

Well-known Member
Joined
Sep 6, 2009
Messages
693
Dear friends i want to change numeric values into English words..for that i have tried this & it's work for me......
http://support.microsoft.com/kb/213360

I'm from oman.so here currency is not a dollars.here we are using Riyals...
Dollars = Riyals
Cents = Baisas

1 Riyal = 1000 Baisa

above VB code working only with two decimal places...but i need to chage that to 3 decimal place..

if i want to change 120.525 above code changing as below..
120.525 - one hundred twenty riyals & fifty two baisas...

but it should be..
120.525 = one hundred twenty riyals & five hundred twenty five baisas..

pls help me to do this.thanks in advance...
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
يا أهلاً بأهل سوريا
replace arabic words as you like

Code: 
Function tafket(Number, Curncey, txtdec)
Dim dec As Currency
If Not IsNumeric(Number) Or Number = "" Or Len(Int(Number)) > 10 Then Exit Function
dec = Number - Int(Val(Number))
ST = Trim(Str(Int(Number)))
xol = Len(ST)
wa = " و "
vn = Val(ST)
Select Case xol
Case 1
     ct = AHAD(vn)
Case 2
     ct = ASHRAT(vn)
Case 3
     ct = MIAT(vn)
Case 4
     vn4 = Val(Mid(ST, 1, 1))
     Select Case vn4
       Case 1
        ct = "ألف"
       Case 2
        ct = "ألفين"
       Case Else
        ct = AHAD(vn4) & " آلاف "
     End Select
     vn4 = Val(Mid(ST, 2, 3))
     ct = ct & IIf(vn4 > 99, wa, "") & MIAT(vn4)
Case 5
     vn5 = Val(Mid(ST, 1, 2))
     ct = IIf(vn5 = 10, ASHRAT(vn5) & " آلاف ", ASHRAT(vn5) & " ألف")
     vn5 = Val(Mid(ST, 3, 3))
     ct = ct & IIf(vn5 > 99, wa, "") & MIAT(vn5)
Case 6
     VN6 = Val(Mid(ST, 1, 3))
     ct = MIAT(VN6)
     VN6 = Val(Mid(ST, 4, 3))
     ct = ct & " ألف " & IIf(VN6 > 99, wa, "") & MIAT(VN6)
Case 7
     vn = Val(Mid(ST, 1, 1))
     Select Case vn
       Case 1
        ct = "مليون "
       Case 2
        ct = "مليونين "
       Case Else
        ct = IIf(vn > 0, AHAD(vn), "") & " ملايين "
    End Select
     vn = Val(Mid(ST, 2, 3))
     ct = ct & IIf(vn > 99, wa, "") & IIf(vn > 0, MIAT(vn) & " ألف ", "")
     vn = Val(Mid(ST, 5, 3))
     ct = ct & IIf(vn > 99, "و", "") & MIAT(vn)
Case 8
   vn = Val(Mid(ST, 1, 2))
   ct = ASHRAT(vn) & " مليون "
   ct = IIf(vn = 10, ASHRAT(vn) & " ملايين ", ct)
   vn = Val(Mid(ST, 3, 3))
   ct = ct & IIf(vn > 99, wa, "") & IIf(vn > 0, MIAT(vn) & " ألف ", "")
   vn = Val(Mid(ST, 6, 3))
   ct = ct & IIf(vn > 99, wa, "") & MIAT(vn)
Case 9
   vn = Val(Mid(ST, 1, 3))
   ct = MIAT(vn) & " مليون "
   vn = Val(Mid(ST, 4, 3))
   ct = ct & IIf(vn > 99, wa, "") & IIf(vn > 0, MIAT(vn) & " الف ", "")
   vn = Val(Mid(ST, 7, 3))
   ct = ct & IIf(vn > 99, wa, "") & MIAT(vn)
Case 10
     vn = Val(Mid(ST, 1, 1))
     Select Case vn
       Case 1
        ct = "مليار "
       Case 2
        ct = "مليارين "
       Case Else
        ct = IIf(vn > 0, AHAD(vn), "") & " مليار"
    End Select
    vn = Val(Mid(ST, 2, 3))
   ct = ct & IIf(vn > 99, wa, "") & IIf(vn > 0, MIAT(vn) & " مليون ", "")
   vn = Val(Mid(ST, 5, 3))
   ct = ct & IIf(vn > 99, wa, "") & IIf(vn > 0, MIAT(vn) & " الف ", "")
   vn = Val(Mid(ST, 8, 3))
   ct = ct & IIf(vn > 99, wa, "") & MIAT(vn)
End Select
ct = " فقط " & ct & " " & Curncey & " " & IIf(Val(dec) > 0, wa & (dec * 100) & txtdec, "") & " لاغير"
If Val(Number) = 0 Then ct = "صفر"
tafket = ct

End Function

Function MIAT(NUM3)
vn3 = Int(NUM3 / 100)
Select Case vn3
   Case 1
      HARF3 = "مائة"
   Case 2
      HARF3 = "مئتان"
   Case 3 To 9
     HARF3 = AHAD(vn3)
     F = "ة"
     B = ""
     D = "ية"
     If vn3 = 8 Then
      HARF3 = Left(HARF3, Len(HARF3) - 2) & "مائة"
     Else
      HARF3 = Left(HARF3, Len(HARF3) - 1) & "مائة"
     End If
   Case Else
      HARF3 = ""
End Select
vn3 = NUM3 - vn3 * 100
If Val(Right(vn3, 1)) = 0 Then wa = " " Else: wa = " و "
 HARF3 = HARF3 & IIf(vn3 > 0, wa & IIf(vn3 < 10, AHAD(vn3), ASHRAT(vn3)), "")
MIAT = HARF3
End Function

Function ASHRAT(NUM2)
wa = " و "
vn2 = Int(NUM2 / 10)
 Select Case NUM2
     Case 10
       HARF2 = "عشرة"
     Case 11
       HARF2 = "إحدى عشر"
     Case 12
       HARF2 = "إثنى عشر"
     Case 13 To 19
       HARF2 = IIf(NUM2 * (10 / 100) <> 0, AHAD(Right(NUM2, 1)) & " ", "") & " عشر"
   End Select

   Select Case vn2
       Case 2
       HARF2 = IIf(AHAD(Right(NUM2, 1)) <> "", AHAD(Right(NUM2, 1)) & wa, "") & " عشرون"
     Case 3
       HARF2 = IIf(AHAD(Right(NUM2, 1)) <> "", AHAD(Right(NUM2, 1)) & wa, "") & " ثلاثون"
    Case 4
        HARF2 = AHAD(Right(NUM2, 1)) & IIf(AHAD(Right(NUM2, 1)) <> "", wa, "") & " أربعون"
     Case 5
        HARF2 = AHAD(Right(NUM2, 1)) & IIf(AHAD(Right(NUM2, 1)) <> "", wa, "") & " خمسون"
     Case 6
        HARF2 = AHAD(Right(NUM2, 1)) & IIf(AHAD(Right(NUM2, 1)) <> "", wa, "") & " ستون"
     Case 7
        HARF2 = AHAD(Right(NUM2, 1)) & IIf(AHAD(Right(NUM2, 1)) <> "", wa, "") & " سبعون"
     Case 8
        HARF2 = AHAD(Right(NUM2, 1)) & IIf(AHAD(Right(NUM2, 1)) <> "", wa, "") & " ثمانون"
     Case 9
        HARF2 = AHAD(Right(NUM2, 1)) & IIf(AHAD(Right(NUM2, 1)) <> "", wa, "") & " تسعون"
 End Select
 ASHRAT = HARF2
End Function

Function AHAD(num1)
 Select Case num1
    Case 0
       harf1 = ""
    Case 1
        harf1 = "واحد"
    Case 2
        harf1 = "إثنان"
    Case 3
        harf1 = "ثلاثة"
    Case 4
        harf1 = "أربعة"
    Case 5
        harf1 = "خمسة"
    Case 6
        harf1 = "ستة"
    Case 7
        harf1 = "سبعة"
    Case 8
        harf1 = "ثمانية"
    Case 9
        harf1 = "تسعة"
  End Select
AHAD = harf1
End Function


worksheet function

Code: 
=tafket(value;"riyals";"basis")
 
Upvote 0
thunder_anger said:
يا أهلاً بأهل سوريا
replace arabic words as you like

Code: 
Function tafket(Number, Curncey, txtdec)
Dim dec As Currency
If Not IsNumeric(Number) Or Number = "" Or Len(Int(Number)) > 10 Then Exit Function
dec = Number - Int(Val(Number))
ST = Trim(Str(Int(Number)))
xol = Len(ST)
wa = " و "
vn = Val(ST)
Select Case xol
Case 1
     ct = AHAD(vn)
Case 2
     ct = ASHRAT(vn)
Case 3
     ct = MIAT(vn)
Case 4
     vn4 = Val(Mid(ST, 1, 1))
     Select Case vn4
       Case 1
        ct = "ألف"
       Case 2
        ct = "ألفين"
       Case Else
        ct = AHAD(vn4) & " آلاف "
     End Select
     vn4 = Val(Mid(ST, 2, 3))
     ct = ct & IIf(vn4 > 99, wa, "") & MIAT(vn4)
Case 5
     vn5 = Val(Mid(ST, 1, 2))
     ct = IIf(vn5 = 10, ASHRAT(vn5) & " آلاف ", ASHRAT(vn5) & " ألف")
     vn5 = Val(Mid(ST, 3, 3))
     ct = ct & IIf(vn5 > 99, wa, "") & MIAT(vn5)
Case 6
     VN6 = Val(Mid(ST, 1, 3))
     ct = MIAT(VN6)
     VN6 = Val(Mid(ST, 4, 3))
     ct = ct & " ألف " & IIf(VN6 > 99, wa, "") & MIAT(VN6)
Case 7
     vn = Val(Mid(ST, 1, 1))
     Select Case vn
       Case 1
        ct = "مليون "
       Case 2
        ct = "مليونين "
       Case Else
        ct = IIf(vn > 0, AHAD(vn), "") & " ملايين "
    End Select
     vn = Val(Mid(ST, 2, 3))
     ct = ct & IIf(vn > 99, wa, "") & IIf(vn > 0, MIAT(vn) & " ألف ", "")
     vn = Val(Mid(ST, 5, 3))
     ct = ct & IIf(vn > 99, "و", "") & MIAT(vn)
Case 8
   vn = Val(Mid(ST, 1, 2))
   ct = ASHRAT(vn) & " مليون "
   ct = IIf(vn = 10, ASHRAT(vn) & " ملايين ", ct)
   vn = Val(Mid(ST, 3, 3))
   ct = ct & IIf(vn > 99, wa, "") & IIf(vn > 0, MIAT(vn) & " ألف ", "")
   vn = Val(Mid(ST, 6, 3))
   ct = ct & IIf(vn > 99, wa, "") & MIAT(vn)
Case 9
   vn = Val(Mid(ST, 1, 3))
   ct = MIAT(vn) & " مليون "
   vn = Val(Mid(ST, 4, 3))
   ct = ct & IIf(vn > 99, wa, "") & IIf(vn > 0, MIAT(vn) & " الف ", "")
   vn = Val(Mid(ST, 7, 3))
   ct = ct & IIf(vn > 99, wa, "") & MIAT(vn)
Case 10
     vn = Val(Mid(ST, 1, 1))
     Select Case vn
       Case 1
        ct = "مليار "
       Case 2
        ct = "مليارين "
       Case Else
        ct = IIf(vn > 0, AHAD(vn), "") & " مليار"
    End Select
    vn = Val(Mid(ST, 2, 3))
   ct = ct & IIf(vn > 99, wa, "") & IIf(vn > 0, MIAT(vn) & " مليون ", "")
   vn = Val(Mid(ST, 5, 3))
   ct = ct & IIf(vn > 99, wa, "") & IIf(vn > 0, MIAT(vn) & " الف ", "")
   vn = Val(Mid(ST, 8, 3))
   ct = ct & IIf(vn > 99, wa, "") & MIAT(vn)
End Select
ct = " فقط " & ct & " " & Curncey & " " & IIf(Val(dec) > 0, wa & (dec * 100) & txtdec, "") & " لاغير"
If Val(Number) = 0 Then ct = "صفر"
tafket = ct

End Function

Function MIAT(NUM3)
vn3 = Int(NUM3 / 100)
Select Case vn3
   Case 1
      HARF3 = "مائة"
   Case 2
      HARF3 = "مئتان"
   Case 3 To 9
     HARF3 = AHAD(vn3)
     F = "ة"
     B = ""
     D = "ية"
     If vn3 = 8 Then
      HARF3 = Left(HARF3, Len(HARF3) - 2) & "مائة"
     Else
      HARF3 = Left(HARF3, Len(HARF3) - 1) & "مائة"
     End If
   Case Else
      HARF3 = ""
End Select
vn3 = NUM3 - vn3 * 100
If Val(Right(vn3, 1)) = 0 Then wa = " " Else: wa = " و "
 HARF3 = HARF3 & IIf(vn3 > 0, wa & IIf(vn3 < 10, AHAD(vn3), ASHRAT(vn3)), "")
MIAT = HARF3
End Function

Function ASHRAT(NUM2)
wa = " و "
vn2 = Int(NUM2 / 10)
 Select Case NUM2
     Case 10
       HARF2 = "عشرة"
     Case 11
       HARF2 = "إحدى عشر"
     Case 12
       HARF2 = "إثنى عشر"
     Case 13 To 19
       HARF2 = IIf(NUM2 * (10 / 100) <> 0, AHAD(Right(NUM2, 1)) & " ", "") & " عشر"
   End Select

   Select Case vn2
       Case 2
       HARF2 = IIf(AHAD(Right(NUM2, 1)) <> "", AHAD(Right(NUM2, 1)) & wa, "") & " عشرون"
     Case 3
       HARF2 = IIf(AHAD(Right(NUM2, 1)) <> "", AHAD(Right(NUM2, 1)) & wa, "") & " ثلاثون"
    Case 4
        HARF2 = AHAD(Right(NUM2, 1)) & IIf(AHAD(Right(NUM2, 1)) <> "", wa, "") & " أربعون"
     Case 5
        HARF2 = AHAD(Right(NUM2, 1)) & IIf(AHAD(Right(NUM2, 1)) <> "", wa, "") & " خمسون"
     Case 6
        HARF2 = AHAD(Right(NUM2, 1)) & IIf(AHAD(Right(NUM2, 1)) <> "", wa, "") & " ستون"
     Case 7
        HARF2 = AHAD(Right(NUM2, 1)) & IIf(AHAD(Right(NUM2, 1)) <> "", wa, "") & " سبعون"
     Case 8
        HARF2 = AHAD(Right(NUM2, 1)) & IIf(AHAD(Right(NUM2, 1)) <> "", wa, "") & " ثمانون"
     Case 9
        HARF2 = AHAD(Right(NUM2, 1)) & IIf(AHAD(Right(NUM2, 1)) <> "", wa, "") & " تسعون"
 End Select
 ASHRAT = HARF2
End Function

Function AHAD(num1)
 Select Case num1
    Case 0
       harf1 = ""
    Case 1
        harf1 = "واحد"
    Case 2
        harf1 = "إثنان"
    Case 3
        harf1 = "ثلاثة"
    Case 4
        harf1 = "أربعة"
    Case 5
        harf1 = "خمسة"
    Case 6
        harf1 = "ستة"
    Case 7
        harf1 = "سبعة"
    Case 8
        harf1 = "ثمانية"
    Case 9
        harf1 = "تسعة"
  End Select
AHAD = harf1
End Function
worksheet function

Code: 
=tafket(value;"riyals";"basis")[/QUOTE]
thanks a lot friend,but i don't want arabic letters.letters should be in English..
Click to expand...
 
Upvote 0
The code originally is for my work

i took it from here
developed it to meet my requirements

so take your time to develop your own to learn something new
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Numbers to Words without VBA
Replies
3
Views
1K
Jeffrey Mahoney
Jeffrey Mahoney
FMHasan
  • Solved
VBA Code for Numeric Value Convert to Word
Replies
3
Views
599
Legacy 143009
L
D
  • Question Question
Convert number to currency
Replies
0
Views
945
djossh
D
M
  • Question Question
Need help on the spell number coding
Replies
6
Views
1K
Zot
Z
D
  • Question Question
VBA for converting numbers to text in a alphanumeric value
Replies
4
Views
2K
swapnilk
S

Forum statistics

Threads
1,224,521
Messages
6,179,290
Members
452,902
Latest member
Knuddeluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top