Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Gold Lounger Rebel's Avatar
    Join Date
    Jul 2001
    Location
    Canada
    Posts
    3,024
    Thanks
    0
    Thanked 1 Time in 1 Post

    Convert to Text (XP SP2)

    A friend has asked if there any way to convert a number in a cell to formatted text? For example, A1 = 1233. He wants to (in another cell) refer to A1 and convert 1233 to "one thousand, two hundred and thirty three". I am not an Excel expert but I told him that if this was possible, someone in the lounge would have an answer. I can't find any function that would accomplish this - VBA perhaps? Thanks in advance.
    John
    A Child's Mind, Once Stretched by Imagination...
    Never Regains Its Original Dimensions

  2. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Convert to Text (XP SP2)

    One solution is to use the free add-in, morefunc

    It adds 41 new functions including NBTEXT which does what you want.

  3. #3
    Gold Lounger Rebel's Avatar
    Join Date
    Jul 2001
    Location
    Canada
    Posts
    3,024
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Convert to Text (XP SP2)

    Thanks Tony, that looks like what he's looking for. I will download and give it a try.
    John
    A Child's Mind, Once Stretched by Imagination...
    Never Regains Its Original Dimensions

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Convert to Text (XP SP2)

    MoreFunc is an excellent add-in; I can heartily recommend it. If you would rather avoid installing an add-in, see the thread starting at <post#=177341>post 177341</post#>.

  5. #5
    Gold Lounger Rebel's Avatar
    Join Date
    Jul 2001
    Location
    Canada
    Posts
    3,024
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Convert to Text (XP SP2)

    Hans,
    I tried the MoreFunc add-in but Excel hangs each time I try it (using Excel 2003). I also copied and pasted the code from <!post=this post,177365>this post<!/post> but I get the #NAME error. This looks like the one I need as I can reference the cell containing the number rather than entering the number directly. Any ideas?
    John
    A Child's Mind, Once Stretched by Imagination...
    Never Regains Its Original Dimensions

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Convert to Text (XP SP2)

    Where did you copy Steve's code? If you copied it into a module in the workbook itself, you should be able to use formulas like

    =NumberToWords(A1)

    However, if you copied it into a module in your Personal.xls, the formula should be like this:

    =Personal.xls!NumberToWords(A1)

  7. #7
    Gold Lounger Rebel's Avatar
    Join Date
    Jul 2001
    Location
    Canada
    Posts
    3,024
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Convert to Text (XP SP2)

    I copied it directly into the VBA editor in the workbook itself. I then entered a number (1000) into A1 and then in B1 entered =NumberToWords(A1) and it returned the #NAME? error. OF course, the description for this error is not very helpful.
    John
    A Child's Mind, Once Stretched by Imagination...
    Never Regains Its Original Dimensions

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Convert to Text (XP SP2)

    That error would indicate that either the macro is not in the same workbook, or that the name is spelled differently. First, make sure that the macro is in the same workbook (if you didn't already have other macros in this workbook you would have had to insert a new module before pasting the code in). On the Tools Menu click on Macro, then click on Macros in the fly out menu. In the Macros dialog box, go to the "Macros in" drop down list and select "This Workbook." Is the macro in the list of macros? If not, then it must be in another workbook. If it is, make sure you are spelling the function name correctly in the formula.

    If that does not fix the problem, could you remove all sensitive data from the workbook and upload it?
    Legare Coleman

  9. #9
    Gold Lounger Rebel's Avatar
    Join Date
    Jul 2001
    Location
    Canada
    Posts
    3,024
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Convert to Text (XP SP2)

    Legare,attached is the file. As I was just testing, there is only one number in cell A1 and the formula in C1. If I select cell A1 and run the macro, it works (and of course, overwrites the number in A1 with text). But trying to run the function from C1 (which is what I want) gives me the error. Obviously, I'm doing something wrong.
    John
    A Child's Mind, Once Stretched by Imagination...
    Never Regains Its Original Dimensions

  10. #10
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Convert to Text (XP SP2)

    Hi John,

    The code was in the wrong place, it needs to be placed in a module. I have attached an updated version of your test file.

    You can create a module by selecting Insert>Module in the VB editor.

  11. #11
    Gold Lounger Rebel's Avatar
    Join Date
    Jul 2001
    Location
    Canada
    Posts
    3,024
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Convert to Text (XP SP2)

    Perfect Tony. If I may, one more question. Could the coding be modified so a number such as 12168 be converted to "twelve thousand one hunderd <font color=blue>and</font color=blue> sixty eight" instead of "twelve thousand one hundred sixty eight"? Don't lnow if this is possible but it would be a nice touch.
    John
    A Child's Mind, Once Stretched by Imagination...
    Never Regains Its Original Dimensions

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Convert to Text (XP SP2)

    In the SetHundreds function, change the line

    If sTemp > "" Then sTemp = sTemp + " "

    to

    If sTemp > "" Then sTemp = sTemp + " and "

    Note: this will convert 234567 to "Two Hundred and Thirty Four Thousand Five Hundred and Sixty Seven". If you object to the "and" in the thousands part, the code would have to be modified.

  13. #13
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Convert to Text (XP SP2)

    I have attached an example based on a different routine (can not remember where I got this one from). It can also take negative and decimal numbers as inputs.

  14. #14
    Gold Lounger Rebel's Avatar
    Join Date
    Jul 2001
    Location
    Canada
    Posts
    3,024
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Convert to Text (XP SP2)

    Thanks Hans. That looks great. I will pass this on to my friend.
    John
    A Child's Mind, Once Stretched by Imagination...
    Never Regains Its Original Dimensions

  15. #15
    Gold Lounger Rebel's Avatar
    Join Date
    Jul 2001
    Location
    Canada
    Posts
    3,024
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Convert to Text (XP SP2)

    A veritable gold mine. Thanks Tony. I will pass both your solution and the one posted by Hans to my friend. He can then decide which is more suitable for his application. I think he is developing a workbook(s) to calculate and print out cheques to his suppliers. Where would this module be placed if he wants it available to all workbooks?
    John
    A Child's Mind, Once Stretched by Imagination...
    Never Regains Its Original Dimensions

Page 1 of 2 12 LastLast

Posting Permissions

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