#1
|
|||
|
|||
Concatenate function for displaying numbers in words
I have this function for cell A12 as you can see from the attachment. But it just won't display the proper figures in English.
|
#2
|
||||
|
||||
What is the SPELLNUMBER function supposed to do?
Working with a picture is not possible, so please post a sample sheet
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
||||
|
||||
Pecoflyer, on a hunch I did a little googling. As you might expect, SpellNumber is a function that's supposed to turn a value such as 39 into a character string "thirty-nine".
From what I see out there, it's claimed it comes with Excel 2010 and also that it has to be written in VBA. I don't know which is correct; I've never had need of such a thing and never had to look it up. Anyone else know the real story? Officeboy, when Excel says "#NAME?", it usually means you misspelled a function name. In this case I'm betting SpellNumber simply doesn't exist, at least not where Excel can find it. If it's part of an Excel Add-In, you need to add it in; either that or you just need to write the function yourself, or go fetch the code for it from the web somewhere. |
#4
|
||||
|
||||
SPELLNUMBER is probably a UDF, not native to 2010, much less to XL2003 as the OP seems to be using
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#5
|
|||
|
|||
Hi both
I don't know what's causing excel not to display the character string. I uploaded the original xls file here. But I think Excel can at least understand what SPELLNUMBER is as someone already used it in excel and successfully converted from numbers to a character string. |
#6
|
||||
|
||||
It cannot work because it is not there. You have to find the code somewhere and copy paste it to a VBA module ( Alt+F11)
The function will then be available
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#7
|
|||
|
|||
Quote:
I have the original xls file from the person who made it, but where can I find his code for "SpellNumber"? |
#8
|
||||
|
||||
In the VBA editor you can open with ALt+F11 as I indicated. Also Google around and look for User Defined Functions in Excel
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post Last edited by Pecoflyer; 10-24-2013 at 09:45 AM. |
#9
|
||||
|
||||
<Alt-F11>? I never knew that; I've been using <Alt-L><V> all this time. Thanks, Peco.
|
#10
|
|||
|
|||
Quote:
I browsed the VBA editor but there is no code I could see linking to that SpellNumber function. I uploaded the xls file where that UDF is used (cell A28) here. Can anyone help? |
#11
|
||||
|
||||
Open the editor as suggested
In the VBA Project window you have a file VBAProject(UDF_A28.xls) One of it's sub files is called Modules There you will find a Module1 Double click on it and a window with the UDF code opens
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#12
|
||||
|
||||
Quote:
See attachment. |
#13
|
|||
|
|||
Thx! It's quite a complicate function. I have to take sometime to figure out why the previous user made it that way.
|
#14
|
|||
|
|||
The most common way is a user-defined function. This works by taking advantage of the "formatting codes" that Google Sheets offers for numbers (more on formatting in this help article).
To make your own formatting code, follow these steps: Go to Cell > Alter Cell Format > Define New Format... Fill out the name and then put the format code below in "Expression" (see screenshot): This concatenate function could be expanded by adding an additional number input field. Make sure to include this changes when constructing cells for each column. The evaluated expression would still remain as shown above. Alternatively, the user could merge cells to create a wider input range. You might be also interested in reading about how formatting columns of numbers from Excel or Google Sheets automatically turns the number into something more readable like Euros or Dollars (check out Ankit's post here) |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Using the sum function to display number as well as words in a single cell | officeboy09 | Excel | 8 | 10-30-2013 05:38 AM |
Words in my document were converted to numbers???? | MikeD23 | Word | 1 | 08-26-2012 11:09 AM |
can't activate remove page numbers function | gsjackson | Word | 5 | 01-16-2012 05:30 PM |
Numbers in Words | janak | Excel | 2 | 12-19-2010 08:53 PM |
displaying start and finish times as absolute numbers instead of dates | benson.wallace | Project | 2 | 12-05-2010 07:22 AM |