Wednesday, August 13, 2014

ARABIC & ENGLISH Amount in Words for Excel

5 years back I have published a script that allows you to view amount in words in Arabic, what we call it “TAFQEET”, the article is published under the following link:

http://mohdaoud.blogspot.com/2008/10/convert-numbers-to-arabic-words-tafqeet_5647.html

I been getting many requests across the years on how to make this works for Excel and my answer was always that you cannot make it as it is an SQL based function and not a VBA.

This morning I have see a comment sharing me a video that shows how to make a simple VBA script to achieve the ENGLISH amount in words and the video is below:

https://www.youtube.com/watch?v=fYAxYtzBi5A

Moving forward I decided to make the same for ARABIC language considering that I have a script that works on .NET does the same and all what I need to do is to make this works for Visual Basic for Applications and the result is below:

1. Download the function from the following path:

http://www.di.jo/files/DI.bas

If that file was viewed and not downloaded, please right click and click “Save Target AS”.

2. Open Excel

image

3. Hit “ALT+F11” to get into the VBA editor

4. Right click the Microsoft Excel Objects Folder and click Import File

image

5. Select the file you just downloaded “DI.bas” and close the VBA editor.

image

6. Get back to your excel and start typing “=DITAFQEET“ you will see that the function was added to your library and ready to be tested!

image

7. Viola! Enjoy the below result

image

NOTE: upon saving the EXCEL you might be getting the following message:

image

Change the “Save As Type” to “Excel Macro-Enabled Workbook”

image

You can download a ready file from the below link:

http://www.di.jo/files/DI%20Tafqeet.zip

Enjoy!


Regards,

--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
http://www.di.jo

29 comments:

ANMB said...

Ahlaan. I want to be able to type in the date; i.e., Saturday, September 13, 2014 and it convert it into Arabic text, with the numbers spelled out.

And I also want to be able to convert the Gregorian date to the corresponding Hijri date, in Arabic text, with the numbers spelled out.

This will be used to create tracing/handwriting worksheets for my young children, using a dotted font.

How can I do that, please?

Thank you.

Sincerely,

ANMB (USA)

Unknown said...

Hey! Nice work on that excel file, I just have one question: Is there a way to make the numbers after the decimal also in words?

Thanks!

Anonymous said...

hi all it is a great vba but unfortunately i cannot copy it into my excel macro, the Arabic terms turns unreadable.thanks for the help

Unknown said...

hey, thank you Mohammad R. Daoud
this is a nice function but i have some problems with it
1- If the fraction 25 piasters it convert it to 250 piasters
2- It make round to the int number like 23,56 it writes it
أربعة وعشرون جنيها و 560 قرشا
3- Is there a way to make the numbers after the decimal also in words?

Mohammad R. Daoud said...

Hello Guys,

I have corrected this, please re-download the excel file again.

Regards,

Mohammad

Unknown said...

Dear Mohammad R. Daoud sorry for the disturbance,
but I still found problems with the new excel macro enabled sheet
1- if there isn't any (قروش او فلس) the word (جنيها او دينارا) hasn't been written at the end of the sentence
2- I want to write the value of the piasters in words also not (550 فلس)
3- If I have the number (3540.55 piasters) it would be written (و 550 قرش)instead of (55 قرش)

Unknown said...

Hello
If you want to change something (characters) the function gives error. I want to do it in Turkish or Azerbaijani, but some characters is not shown in Excell as it in VBA. "çşıəöğü". I will be glad if you help me. amid749@gmail.com

Kindest Regards
Amid

Bader said...

Salam dear Mohammad,

It works very fine in VBA in MS Access 2010, as well. However, I needed to remove the "Attribute" command at the very beginning of the code.
Best regards

Bader said...

I noted that diTafqeet does not show "currency" at the end of the wordings, if the decimals are = 0
I tried to correct, but not succeeded.

Regards

Bader said...

In addition, it is not working well with negative numbers.

Gopinath Menon,Riyadh said...

One Minute is what it took. Awesome. Very useful. Thanks.

Unknown said...

Dear Mr Mohammad,
Thank you for sharing this wonderful function.
Since i'm not an expert i would like to benefit from your expertise and ask you if it is possible to add or change the currency at the end of the converted sentences?

Regards,

And thank you in advance.

NITU said...

Hi, Thanks.
User can also try free GWORD addin formula which auto convert numbers or amount to words or rupees.
Download Link - www.xltool.in | www.gwordformula.blogspot.com

NITU said...

Hi, Thanks.
User can also try free GWORD addin formula which auto convert numbers or amount to words or rupees.
Download Link - www.xltool.in | www.gwordformula.blogspot.com

Haris said...
This comment has been removed by the author.
Haris said...

I am getting something like this for a value of 801.780.

ËãÇäíãÇÆÉ æÇËäÇä ÏíäÇÑÇ æ 780 ÝáÓÇ

Mohammad R. Daoud said...

Change the language in your regional and language options to Arabic for characters to be displayed correctly.

Mohammed Azeem said...

Hello,

I am still getting cya as the result even though i have changed language in control panel. Please help.

Unknown said...

THANKS A LOT , THIS IS VERY HELPFUL TO MY JOB THANKS

استاذ القلم بيك said...

I also changed the language, it sitll pops out as something weird

Azlan Ismun said...

My full application MS Visual Basic 4.0 System Library 1.0
https://goo.gl/19Ucqs

Codes for the System Library 1.0
https://goo.gl/FA26WS

Google Profail
https://goo.gl/wZ38Nv

SAEED TT SHARAFUDHEEN said...

when i type 15383.50

its translated to below:

ÎãÓÉ ÚÔÑ ÇáÝ æËáÇËãÇÆÉ æÇÑÈÚÉ æËãÇäæä ÏíäÇÑÇ æ 500 ÝáÓÇ

could you please help me brother


Ma sha Allah and Thank you for your efforts

Mohammad R. Daoud said...

Change the language in your regional and language options to Arabic for characters to be displayed correctly.

Unknown said...

Thanks alot :)
very helpful, the best I have found to solve a mailmerge to be sent to over 200 people with exact figures:)

OGreen said...

Hello! Your script is wonderful but just one thing, how i can add "فقط لاغير" to the end of tafqit.
Thank you

Unknown said...

Hello! Your script is wonderful but just one thing, how i can add "فقط لاغير" to the end of tafqit.
Thank you

Haitham el amashety said...

HI, I DID change THE language in regional and language options to Arabic AND STILL, I AM GETTING THIS ÓÈÚÉ.

Please help.

RAMESH said...

HI, I DID change THE language in regional and language options to Arabic AND STILL, I AM GETTING THIS ÓÈÚÉ.

Please help.

Anonymous said...

hi i also changed it and still not working

Related Posts:

Related Posts with Thumbnails