Convert to Text (XP SP2)

**Rebel** · 2004-05-20 15:07

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.

**TonyE** · 2004-05-20 15:16

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

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

**Rebel** · 2004-05-20 15:22

Thanks Tony, that looks like what he's looking for. I will download and give it a try.

**HansV** · 2004-05-20 15:24

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#>.

**Rebel** · 2004-05-21 12:55

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?

**HansV** · 2004-05-21 13:13

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)

**Rebel** · 2004-05-21 16:29

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.

**LegareColeman** · 2004-05-21 16:45

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?

**Rebel** · 2004-05-21 17:35

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.

**TonyE** · 2004-05-21 17:43

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.

**Rebel** · 2004-05-21 19:53

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.

**HansV** · 2004-05-21 20:02

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.

**TonyE** · 2004-05-21 20:21

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.

**Rebel** · 2004-05-21 21:56

Thanks Hans. That looks great. I will pass this on to my friend.

**Rebel** · 2004-05-21 22:05

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?

Thread: Convert to Text (XP SP2)

Thread Tools