Closed Thread
Results 1 to 23 of 23

Convert a Numeric value to Words Without Vba

  1. #1
    Forum Contributor HaroonSid's Avatar
    Join Date
    02-28-2014
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    2,008

    Thumbs up Convert a Numeric value to Words Without Vba

    Note : It works for a value having two decimal places. It truncates if you have a value having more than two decimal places.
    Note : The following formula can convert amount one less than 1 trillion into words.

    For example : $ 1,250.50 = One Thousand Two Hundred Fifty Dollars and Fifty Cents

    $1,250.50 is placed in cell B5.

    Paste the following formula in cell C5.

    source : http://www.listendata.com/2013/12/co...rds-using.html

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by HaroonSid; 06-01-2014 at 11:32 AM.
    Use Code-Tags for showing your code :
    Please mark your question Solved if there has been offered a solution that works fine for you
    If You like solutions provided by anyone, feel free to add reputation using STAR *

  2. #2
    Valued Forum Contributor
    Join Date
    11-02-2012
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    564

    Re: Convert a Numeric value to Words Without Vba

    That's one heck of a formula. Thanks for the effort.

  3. #3
    Forum Contributor HaroonSid's Avatar
    Join Date
    02-28-2014
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    2,008

    Re: Convert a Numeric value to Words Without Vba

    i think this is largest formula

  4. #4
    Registered User
    Join Date
    09-14-2014
    Location
    The Netherlands
    MS-Off Ver
    2010
    Posts
    5

    Re: Convert a Numeric value to Words Without Vba

    i think there is a function for that in excell itself

  5. #5
    Registered User
    Join Date
    09-19-2014
    Location
    USA
    MS-Off Ver
    2010
    Posts
    20

    Re: Convert a Numeric value to Words Without Vba

    This is an awesome formula

    Heres my contribution to the great work,

    I have translated this into 2 more formats:

    Standard Mexico Spanish Lettering (including the billions/milliards correction for spanish)
    Standard Mexico Spanish Cheque formatting

    I hope they are usefull to someone else, took me 2 hours to troubleshoot all the minute text bugs and wording nuances when converting to spanish, so enjoy (god i hate my language lol)

    As a recomendation, use notepad or another text editor to replace N34 with the cell number you use on your sheet, theres lots of iterations in the formula, doing drag and drop on each one by hand will probably take a long time.

    Standard Mexico Spanish Lettering:

    $12,525,681.93
    Shows as:
    DOCE MILLON(ES), QUINIENTOS VEINTE Y CINCO MIL, SEISCIENTOS OCHENTA Y UN PESOS CON NOVENTA Y TRES CENTAVOS

    Please Login or Register  to view this content.
    Standard Mexico Spanish Cheque formatting:

    $12,525,681.93
    Shows as:
    ( DOCE MILLON(ES), QUINIENTOS VEINTE Y CINCO MIL, SEISCIENTOS OCHENTA Y UN PESOS 93/100 M.N. )

    Please Login or Register  to view this content.
    Last edited by XionicFire; 03-01-2016 at 12:32 AM. Reason: Fixed Typo in one of the formulas

  6. #6
    Registered User
    Join Date
    02-16-2014
    Location
    qatar
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Convert a Numeric value to Words Without Vba

    This is great, thanks for sharing!

  7. #7
    Forum Contributor HaroonSid's Avatar
    Join Date
    02-28-2014
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    2,008

    Re: Convert a Numeric value to Words Without Vba

    Welcome@leyo

  8. #8
    Registered User
    Join Date
    02-16-2014
    Location
    qatar
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Convert a Numeric value to Words Without Vba

    thanks
    this very nice!!

  9. #9
    Registered User
    Join Date
    11-14-2018
    Location
    Singapore
    MS-Off Ver
    2010
    Posts
    3

    Re: Convert a Numeric value to Words Without Vba

    Quote Originally Posted by HaroonSid View Post
    Note : It works for a value having two decimal places. It truncates if you have a value having more than two decimal places.
    Note : The following formula can convert amount one less than 1 trillion into words.

    For example : $ 1,250.50 = One Thousand Two Hundred Fifty Dollars and Fifty Cents

    $1,250.50 is placed in cell B5.

    Paste the following formula in cell C5.


    Please Login or Register  to view this content.

    Wow Haroon, thanks for sharing your codes if you generated it yourself from scratch!
    Yours is the only solution for such conversion without vba.

    Are we able to do it from:

    One Thousand Two Hundred Fifty Dollars and Fifty-One Cents

    to

    One Thousand Two Hundred Fifty and Cents Fifty-One Only


    Greetings from Singapore by the way!

  10. #10
    Forum Contributor HaroonSid's Avatar
    Join Date
    02-28-2014
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    2,008

    Re: Convert a Numeric value to Words Without Vba

    Quote Originally Posted by balling89 View Post
    Wow Haroon, thanks for sharing your codes if you generated it yourself from scratch!
    Yours is the only solution for such conversion without vba.

    Are we able to do it from:

    One Thousand Two Hundred Fifty Dollars and Fifty-One Cents

    to

    One Thousand Two Hundred Fifty and Cents Fifty-One Only


    Greetings from Singapore by the way!
    Try this
    Please Login or Register  to view this content.
    and i did not make this code
    i mentioned above in my post

  11. #11
    Registered User
    Join Date
    11-14-2018
    Location
    Singapore
    MS-Off Ver
    2010
    Posts
    3

    Re: Convert a Numeric value to Words Without Vba

    Quote Originally Posted by HaroonSid View Post
    Try this
    Please Login or Register  to view this content.
    and i did not make this code
    i mentioned above in my post
    Thank you for your swift response!

    For $1,250.51, with your above updated code, i got this:
    One Thousand Two Hundred Fifty Dollars and Fifty-One Only

    May I ask if you are able to churn out this instead?
    One Thousand Two Hundred Fifty and Cents Fifty-One Only

    The removal of word 'Dollars' and additional of Cents after the 'and' word
    Much warm thanks!

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,911

    Re: Convert a Numeric value to Words Without Vba

    balling89 welcome to the forum

    Unfortunately your post does not comply with Rule 4 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  13. #13
    Registered User
    Join Date
    11-14-2018
    Location
    Singapore
    MS-Off Ver
    2010
    Posts
    3

    Re: Convert a Numeric value to Words Without Vba

    Quote Originally Posted by FDibbins View Post
    balling89 welcome to the forum

    Unfortunately your post does not comply with Rule 4 of our Forum . Do not post a question in the thread of another member -- start your own thread.

    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

    Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

    My many apologies. Duly noted. Thank you for the kind heads-up.

  14. #14
    Forum Contributor HaroonSid's Avatar
    Join Date
    02-28-2014
    Location
    india
    MS-Off Ver
    Excel 2013
    Posts
    2,008

    Re: Convert a Numeric value to Words Without Vba

    looking for a indian currency version

  15. #15
    Registered User
    Join Date
    02-27-2012
    Location
    mumbai
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: Convert a Numeric value to Words Without Vba

    Great Formula...
    But what is recommended?
    Using the VBA code conversion or using this formula in the cell, in terms of speed of the calculation.
    Especially if the formula is being used for 10 times or more in the same spreadsheet?

  16. #16
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,911

    Re: Convert a Numeric value to Words Without Vba

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

  17. #17
    Registered User
    Join Date
    07-22-2012
    Location
    Chittagong
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Convert a Numeric value to Words Without Vba

    Hi,

    It was helpful for me. But, I got a problem, when input data 132.68 in cell "B6" it shows #VALUE. would you please check the issue.

    regards

    rif

  18. #18
    Registered User
    Join Date
    07-22-2012
    Location
    Chittagong
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Convert a Numeric value to Words Without Vba

    Quote Originally Posted by HaroonSid View Post
    Note : It works for a value having two decimal places. It truncates if you have a value having more than two decimal places.
    Note : The following formula can convert amount one less than 1 trillion into words.

    For example : $ 1,250.50 = One Thousand Two Hundred Fifty Dollars and Fifty Cents

    $1,250.50 is placed in cell B5.

    Paste the following formula in cell C5.

    source : http://www.listendata.com/2013/12/co...rds-using.html

    Please Login or Register  to view this content.

    It's not working while i input 132.68 in cell "B6".

  19. #19
    Registered User
    Join Date
    12-16-2019
    Location
    Alaska
    MS-Off Ver
    office 2010
    Posts
    13

    Re: Convert a Numeric value to Words Without Vba

    Hi,
    I made a template about the subject before. I created a NumbertoText function in Module1(VBA Window).
    The function can be used on all sheets of the workbook.

    For example, enter "the number 50" into cell G1, and enter the following formula into another cell:
    =NumbertoText(G1)
    The result will be as this : Fifty Dollars and No Cents
    Attached Files Attached Files

  20. #20
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    51,911

    Re: Convert a Numeric value to Words Without Vba

    kadr thanks for the input, but perhaps you missed reading the title properly - it specified withOUT using VBA

  21. #21
    Registered User
    Join Date
    05-23-2020
    Location
    Guwahati, Assam, India
    MS-Off Ver
    2019
    Posts
    23

    Thumbs up Re: Convert a Numeric value to Words Without Vba

    Thanks a lot for this...
    Asha Kanta Sharma
    Guwahati, India

  22. #22
    Registered User
    Join Date
    11-16-2020
    Location
    Oman
    MS-Off Ver
    Ms office 2016
    Posts
    1

    Re: Convert a Numeric value to Words Without Vba

    Hello Sir,
    I need to spell currency number to text up to 3 decimal place without VBA. Please provide the formula.
    Thank you.

  23. #23
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,648

    Re: Convert a Numeric value to Words Without Vba

    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] convert words to numeric value
    By sekharks in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-29-2013, 03:58 AM
  2. Convert words into a numeric value
    By matric in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-29-2012, 01:56 AM
  3. how do I convert numeric data to words?
    By azohawk in forum Excel General
    Replies: 3
    Last Post: 06-14-2006, 09:15 PM
  4. [SOLVED] convert numeric value to words, error msg!
    By HHTrang in forum Excel General
    Replies: 2
    Last Post: 04-13-2006, 03:50 AM
  5. is there any function to convert the numeric number to words
    By new.microsoft.com in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-25-2005, 05:30 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1