NBText function

Dear all

I have NBTEXT function for the first time and can't get 
it to work.  When I have entered the formula and press 
Enter, the cell goes completely blank???  All I want to 
do is convert a number into text format.  I have 
downloaded the add-in function but this crashes my laptop 
every time I use it.

Am I doing something wrong??

Thanks.

Louise

0
12/23/2003 10:17:39 AM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
1476 Views

Similar Articles

[PageSpeed] 37

What add-in is this function in?

-- 
Jim Rech
Excel MVP


0
jrrech (1933)
12/23/2003 12:02:23 PM
the add-in i was told to use in a previous posting is 
morefunc.dll.

Thanks for any help you can offer.

>-----Original Message-----
>What add-in is this function in?
>
>-- 
>Jim Rech
>Excel MVP
>
>
>.
>
0
12/23/2003 2:35:57 PM
Ahh, Laurent Longre's add-in.  Well, I seem to have a problem using it, or
at least the NBTEXT function.  It hangs Excel 97, 2000, 2002 and 2003 for
me!  Sorry but I can't help you if I can't get it to work.

-- 
Jim Rech
Excel MVP


0
jrrech (1933)
12/23/2003 3:10:11 PM
Well I'm glad it's not just me then, having a blonde 
moment!!  Thanks any way.

If you find out a way of converting a figure into text 
format, let me know!!

Thanks.

>-----Original Message-----
>Ahh, Laurent Longre's add-in.  Well, I seem to have a 
problem using it, or
>at least the NBTEXT function.  It hangs Excel 97, 2000, 
2002 and 2003 for
>me!  Sorry but I can't help you if I can't get it to 
work.
>
>-- 
>Jim Rech
>Excel MVP
>
>
>.
>
0
12/23/2003 3:38:19 PM
Louise

No built-in Function, but you can build your own User Defined Function using
the info at: 

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q213360

I use the older version of MOREFUNC.XLL which has the NUMTEXT Function and it
has always worked for me.

Didn't know the new version had exchanged NUMTEXT for NBTEXT.

Gord Dibben Excel MVP

On Tue, 23 Dec 2003 07:38:19 -0800, "Louise" <louise.phillip@coop.co.uk>
wrote:

>Well I'm glad it's not just me then, having a blonde 
>moment!!  Thanks any way.
>
>If you find out a way of converting a figure into text 
>format, let me know!!
>
>Thanks.
>
>>-----Original Message-----
>>Ahh, Laurent Longre's add-in.  Well, I seem to have a 
>problem using it, or
>>at least the NBTEXT function.  It hangs Excel 97, 2000, 
>2002 and 2003 for
>>me!  Sorry but I can't help you if I can't get it to 
>work.
>>
>>-- 
>>Jim Rech
>>Excel MVP
>>
>>
>>.
>>

0
Gord
12/23/2003 6:00:22 PM
Louise

The following function works quite well for converting numbers to text.  The
advantage that it has, over some other 'number to text' code, is that it can
be used for plain numbers, or currencies, and the currency is user
defineable.   Unfortunately I don't know who the author is, but thanks to
him/her anyway.

HTH

Cheers,

John

'-----------------------------------------

'Usage:
'With 123456.88 in Cell A1:
'=NumberToText(A1)
'returns "One Hundred Twenty-Three Thousand Four Hundred Fifty-Six and
Eighty-Eight"
'=NumberToText(A1,"Dollars","Cents")
'returns "One Hundred Twenty-Three Thousand Four Hundred Fifty-Six Dollars
and Eighty-Eight Cents"

'In the line -
'        sDec = "and " & Trim(HundredsToText(CVar(sDec)) & " " & sCent)
' "and " can be changed to "point "
'so that =NumberToText(A1)
'returns "One Hundred Twenty-Three Thousand Four Hundred Fifty-Six point
Eighty-Eight"

Function NumberToText(Num As Variant, Optional vCurName As Variant, Optional
vCent As Variant) As Variant
Dim TMBT As Variant
Dim sNum As String, sDec As String, sHun As String, IC As Integer
Dim Result As String, sCurName As String, sCent As String

If Application.IsNumber(Num) = False Then
 NumberToText = CVErr(xlValue)
 Exit Function
End If

If IsMissing(vCurName) Then
 sCurName = ""
Else
 sCurName = Trim(CStr(vCurName))
End If

If IsMissing(vCent) Then
 sCent = ""
Else
 sCent = Trim(CStr(vCent))
End If

TMBT = Array("", "Thousand", "Million", "Billion", "Trillion",
"Quadrillion", "Quintillion", "Sextillion")

If IsMissing(sCent) Or IsNull(sCent) Then
 sNum = Format(Application.Round(Num, 0), "0")
Else
 sNum = Format(Application.Round(Num, 2), "0.00")
 sDec = Right(sNum, 2)
 sNum = Left(sNum, Len(sNum) - 3)
 If CInt(sDec) <> 0 Then
  sDec = "and " & Trim(HundredsToText(CVar(sDec)) & " " & sCent)
 Else
  sDec = ""
 End If
End If

IC = 0
While Len(sNum) > 0
 sHun = Right(sNum, 3)
 sNum = Left(sNum, Application.Max(Len(sNum) - 3, 0))
 If CInt(sHun) <> 0 Then
  Result = Trim(Trim(HundredsToText(CVar(sHun)) & " " & TMBT(IC)) & " " &
Result)
 End If
 IC = IC + 1
Wend

Result = Trim(Result & " " & sCurName)
Result = Trim(Result & " " & sDec)
NumberToText = Result

End Function

Function HundredsToText(Num As Integer) As String
Dim Units As Variant, Teens As Variant, Tens As Variant
Dim I As Integer, IUnit As Integer, ITen As Integer, IHundred As Integer
Dim Result As String
Units = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven",
"Eight", "Nine")
Teens = Array("Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen",
"Sixteen", "Seventeen", "Eighteen", "Nineteen")
Tens = Array("", "", "Twenty", "Thirty", "Fourty", "Fifty", "Sixty",
"Seventy", "Eighty", "Ninety")
Result = ""
IUnit = Num Mod 10
I = Int(Num / 10)
ITen = I Mod 10
IHundred = Int(I / 10)

If IHundred > 0 Then
 Result = Units(IHundred) & " Hundred"
End If

If ITen = 1 Then
 Result = Result & " " & Teens(IUnit)
Else
 If ITen > 1 Then
  Result = Trim(Result & " " & Tens(ITen) & " " & Units(IUnit))
 Else
  Result = Trim(Result & " " & Units(IUnit))
 End If
End If

HundredsToText = Result

End Function

'End Code
'------------------------------------

"Louise" <louise.phillip@coop.co.uk> wrote in message
news:096201c3c96a$ca29a8b0$a501280a@phx.gbl...
> Well I'm glad it's not just me then, having a blonde
> moment!!  Thanks any way.
>
> If you find out a way of converting a figure into text
> format, let me know!!
>
> Thanks.
>
> >-----Original Message-----
> >Ahh, Laurent Longre's add-in.  Well, I seem to have a
> problem using it, or
> >at least the NBTEXT function.  It hangs Excel 97, 2000,
> 2002 and 2003 for
> >me!  Sorry but I can't help you if I can't get it to
> work.
> >
> >-- 
> >Jim Rech
> >Excel MVP


0
marjohn (17)
12/24/2003 12:15:17 AM
Reply:

Similar Artilces:

ROUND Function with other function
Hi, I have this formula: =IF(R8>0,CONCATENATE("+",R8),R8) and the results shows me for example: +11.6 but what I really want t show is +12. I've been trying to put the "Round" function at a few places in th formula but it doesn't work. The closest I got was with this formula: =ROUND(IF(R8>0,CONCATENATE("+",R8),R8),0) where it showed me 12, without the "+" sign before.... Can anyone help me ? Thank -- Outapi ----------------------------------------------------------------------- Outapin's Profile: http://www.excelforum.com/member...

Returning a range from an inner function
I have a range of data, which has had the DOLLAR() function performed on it. I would like to get the total of this range using the SUM() function. doing SUM(F1:F4) will not work, so I need to convert the data into values that will work. The VALUE() function is perfect for converting the data into values that will work for SUM(), but the formula SUM(VALUE(F1:F4)) does not work because VALUE() will not accept a range as a parameter (and also does not return a range). I need to perform the VALUE() function on each cell before submitting it to SUM(). There must be some way to do this, cons...

JustifyString function problem
Hi, I'm using the "JustifyString" function (from Stephan Lebans) to right justify three columns in a ListBox. Two of them are working fine ... one's a simple number (long integer) and the other's currency. The third causes an Access 2003 crash! That third column is a long integer which is a link to the autonumber field in another table. Both tables are in the query as I need columns from both tables in the ListBox. If I change the JustifyString to reference the autonumber field in the other table, I still get an Access crash. I didn't review the Access er...

automating functions
I am a complete novice at CRM 1.2, but want to be able to create, for example, a workflow process that will monitor the CSR's and the queues and automatically assign a new case to the least busy CSR. Any input would be appreciated. Thanks in advance Jim Please tell me what I would have to do and with what tools in order to create such a workflow process. "jmb" wrote: > I am a complete novice at CRM 1.2, but want to be able to create, for > example, a workflow process that will monitor the CSR's and the queues and > automatically assign a new case to the lea...

Returning a CHAR[] from a function
I have the following function that returns the path of where the program was run from. However I cant seem to return the butter as a CHAR. The code works if I use it in a procedure but not where I need to return the variable CHAR buffer. char CAutoRunDlg::GetProgramPath(void) { char buffer[_MAX_PATH]; //declares maimum path //opens window maximized //_getcwd(,)gets the current working directory // #include <direct.h> //needed for current working directory /* Get the current working directory: */ if( _getcwd( buffer, _MAX_PATH ) == NULL ) perror( "_getcwd error" )...

Turning Off Error Reporting using API Functions and VB
We want to automatically turn off the error reporting options using Windows API functions for the following functions area Click on Start, Settings, Control Panel, System, the Advanced tab and Error Reporting (at the bottom of the screen), which launches the Error Reporting screen. Another way to get to that screen: Open My Computer, right-click on Properties, click on Advanced tab and Error Reporting. Any assistance would be welcome. It's not a program's call. It's the user's decision. --=20 -------------------------------------------------------------------------= --...

Complex functions
Hi All, Suppose you have a very complex and long function that is also subject to a condition, then you will/can have something like: =if(condition(complex function), complex function, other complex function) It can have more lines in the formula bar, so readability below 0 Kelvin. Is there a way to 'name' a function? =If(condition(myfunc = complex function), myfunc, other complex function) With kind regards, JP Yes you can put a formula into an Excel Name (the same as you can give a range an Excel Name. Very powerful it can be too. Do a Google fo...

Match Function
Hi, I have put together a spreadsheet to find codes, states, descriptions. It finds the data by dates. Columns are: Dates, Codes, States, Descriptions If I put in a date it matches the code, state and description. My problem is that if I have 2 dates that are the same it will only match the first date it can find. What kind of formula do I need so that it will output the second or third date data. Thanks for your help. If you need a copy of the spreadsheet please let me know. How many rows of data do you have? -- Biff Microsoft Excel MVP "Melanie" <Melanie@discussio...

If Function Function
okay. So I am trying to workout a formula to compute a commision earning. Basically, if X amount of new business is made, then X*Y (corresponding Commision rate percentage), but if new business =<X, then X*Y (Commision rate percentage that is true). Problem is, it's super confusing...anyone have any suggestions? See if this helps: http://mcgimpsey.com/excel/variablerate.html -- Biff Microsoft Excel MVP "aetejada" <aetejada@discussions.microsoft.com> wrote in message news:AA4E55F7-9435-4BB6-837A-416A59BB72A0@microsoft.com... > okay. So I am...

DateDiff function throwing error in Where clause
I have a set of queries that generate dates and want to use DateDiff to select those records with certain dates are greater than others. The following clause keeps giving me an incompatible data type error: WHERE DateDiff('d',[From date],[DatumStvoreni])>0 I have the exact expression in the SELECT clause and it show positive and negative integers just fine, but when I add this test, I get the error. There are no bad values in the dataset, I've looked, and when I export the DateDiff column to a temp table and run a query on that, it works fine. Only when I use t...

timing a function
Good Morning All, I've writen an MFC C++ application, under VC++6.0 . How can I check how much time it takes for a specific function to complete? I need to know how many miliseconds one of my function call takes. Thanks very much in advance, Dudu Arbel If your accuracy requirement is in ms - use GetTickCount. -- Regards, Nish [VC++ MVP] http://www.voidnish.com http://blog.voidnish.com "dududuil" <dududuil@discussions.microsoft.com> wrote in message news:C0167B90-33D2-4A4F-A73D-DC4EBEF81946@microsoft.com... > Good Morning All, > > I've writen an MFC C++ ...

Outlook 2002 Change Password function
I want users to be able to change password from the Outlook Login Page, but it does noet seem to work. Outlook 2002 Sp1 and Windows Server 2003 (AD) and Exchange 2003 SP1 Important: Outlook client is not member of the domain... There are not many Microsoft Articles about this. Anyone? -- Regards, Menko den Ouden Netherlands Menko den Ouden schreef in deze nieuwsgroep op 23-6-2005 15:28: > I want users to be able to change password from the Outlook Login Page, but > it does noet seem to work. > > Outlook 2002 Sp1 and Windows Server 2003 (AD) and Exchange 2003 SP1 > ...

non-functioning multiple Array formulas in a workbook
I have a workbook with used for forecasting. I use a template sheet for each forecast (all forecast sheets -54- are the same and contain sheet-level names). On each sheet I have a formula that counts the number of chargeable engagements (where the chargeable engagement is indicated by a "C" in the preceding column) and places the results in a cell with concatenated text. The 'chargeable designation' column is a named range. I can get the formula to work on only two of the worksheets. It can be any two sheets, but it will only work on two sheets maximum. I have tried a numbe...

C function prototype
Is that for Class/Object function prototype, I must define the function in header file or .cpp file. MyClass::functionA(); MyClass::functionB(); but for C function prototype, I don't have to define if it's put before the main() function the following is not needed - void stradd (char *s1, char *s2); void stradd (char *s1, int i); ========= #include <iostream> // cannot be iostream.h?? #include <stdio.h> #include <string.h> #include <comdef.h> #include <conio.h> #include <windows.h> // must need for SYSTEMTIME //must need C/C++ > General...

functions
hi, is there a function that does this if a1=1, if true a2=b1, if false a2 stays as it was (EITHER WITH A ZERO VALUE OR THE VALUE THAT MAY HAVE BEEN ENTER PREVIOUSLY FOR EXAMPLE B2= 10 A1= 0 B1=0 B1 WILL BE INCREMENTED BY A MACRO A1=10 B1=1 A1=10 B1=2 THOUGHT AN IF FUNCTION WITHOUT THE FALSE BIT WOULD WORK BUT IT DIDNT ALL IT GAVE ME WAS THE TEXT "FALSE" Hi Carlos1973! I'm afraid that you can't use functions to do other than return a value to a cell. Closest you'll get is: A2: =IF(A1=1,B1,0) or: A2: =IF(A1=1,B1,C1) You ...

=mid function
Cell A1 contains 1,274 characters. My function is: =mid(a1,xxx,12) Is there a way to determine the position of the first character in cell A1, besides physically counting over 'xxx' characters? Don't really understand your question. The position of the first character in A1 is "1". =Left(A1,12) - BUT - I'm sure that's not what you're looking for. Is it ? Do you want to put the value xxx in a cell B1 and use that ? =MID(A1,B1,12) You want to return 12 characters starting at position xxx. You have to have something to identify or mark that particular sta...

Problem with IF function
I have a puzzling problem when using the IF function. My spreadsheet looks like this: Date Value 1 2 3 4 1/2006 100 4/2006 450 3/2006 235 If the month in the date in column A matches the month number in the top row I want to put the value from column B under the matching month column otherwise I want to insert a zero. So, in the above example, 100 would go in cell C2, 450 in cell F3 etc. Some date cells will be blank and I have found that if I use a simple formula like: =IF(MONTH($A2)=C$1,$B2,"0") in the c...

SUMPRODUCT function
Partner Orpington FRICS Partner Orpington FRICS Partner Orpington BSc FRICS I Eng AMI Struct E Partner Orpington FRICS Partner Orpington BA(Hons) MRICS Partner Orpington MRICS BSc Partner Orpington BSc Dip Arch(Hons) RIBA Partner Orpington ACIOB MAPM Partner Orpington BSc MRICS Partner/ Site Assessor Orpington BSc FRICS I Eng AMI Struct E Partner Associate Orpington BSc FRICS I Eng AMI Struct E Partner Orpington BSc MRICS Partner Orpington MRICS Partner Orpington BSc(Hons) Dip Arch Grad DiplCons (AA) RIBA Partner Orpington BSc MRICS ...

Excell IF Function
I am trying to use the IF function to total a row, if the sum is greater than 40 then the answer given back would be 40. This for a payroll workbook. Any help is appreciated. =IF(SUM(A1:Z1)>40,40,SUM(A1:Z1)) because you requested an IF function. Better is: =MIN(40,SUM(A1:Z1)) For whole row substitute IV1 for Z1. ----- Marc wrote: ----- I am trying to use the IF function to total a row, if the sum is greater than 40 then the answer given back would be 40. This for a payroll workbook. Any help is appreciated. Marc, how about something like this =IF(SUM(A1:J1)>4...

Date Function not recognised
I have Access 2002 SP3 on both my desk computer and my laptop. I also have Access 97 on both machines. On my desktop I have an application which works perfectly in Access 2002. I copied the mdb to my laptop, and when I open the application it will not recognise the DATE () function which is the default on some of the forms. Instead I get #Name displayed where there should be the current date. In the module References I have DAO 2.5/3.5 checked on both machines. Does anyone have any ideas??? You're missing another reference on the errant machine.Here are Doug Steele's instru...

=IF function
Hello All, Using Windows and Excel XP. I have a spreadsheet that contains numbers in A1:B2. What I want to do is to check the numbers in A2:B2 to see if those numbers fall in the range of numbers in A1:B1. If one OR both of the numbers fall in the range I would like for the formula to return "ok", and if it doesn't I would like for it to return "NO". Example #1 A B ----------------------------------- 1 40 37 2 37 12 in this example 37 (in A2) falls in between 40 (A1) and 37 (B1), but ...

Blank Out Function Totals
I have the following function in cell E7 in a spreadsheet: =D7-C7 Now, if there is no data in D7 and C7, then 00:00 appears in cell E7 as it has no data to work against. What I want to do is to blank out the cells with functions in them so that they appear empty, unless initial data is entered. So in the above case, I don't want anything to appear in cell E7 until cells D7 and C7 have been populated. Any ideas? Tools>Options>View, uncheck Zero values -- Kind regards, Niek Otten "Neil Greenough" <scrivomc@hotmail.com> wrote in message news:mFiif.82137$...

VB UML model from function
Hy How can I build an uml model from a VB 6 function? Thanx 4 any help There is no support to create a UML model using a VB6 function It can however reverse-engineer a VB project into a UML diagram -- -ronnie [MS] This posting is provided "AS IS" with no warranties, and confers no rights. "Papp Gy�rgy" <pappgyorgy@digicart.hu> wrote in message news:eEG6bJQeDHA.1716@TK2MSFTNGP10.phx.gbl... > Hy > > How can I build an uml model from a VB 6 function? > > Thanx 4 any help > > ...

Calling a function in excel
I created a function in the Visual Basic Editor in Excel but am unable to call it from an individual cell in Excel. I am using "=function name(argument1, argumaent2, argument3)" when I try to call it. I am getting the error "$NAME?". Any ideas? Hi The function should be i a standard module (insert menu), not in a worksheet module or anywhere else. And it must not be Private as in "Private Function XYZ() as Long" And you are of course entering it without the quotes ? -- HTH. Best wishes Harald Followup to newsgroup only please "Tom" <tfreem1...

If Function Limitation
How can one have more than 7 If Functions nested, I have the need to create more than 7 If Functions, how can I go around this problem, I consulted the Help Section and it states: "Up to seven IF functions can be nested as value_if_true and value_if_false arguments" Your help is most appreciated! In practically every instance I run across where you need more than 7 IF statements, the problem can be solved by constructing a Lookup table of values to be matched and their corresponding return value. Example: Col_A Col_B Apple 1 Berry 100 Cat 11 Date 21 Eel 35 Fruit 106 Grate ...