Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-22-2013, 01:38 AM
officeboy09 officeboy09 is offline Concatenate function for displaying numbers in words Windows XP Concatenate function for displaying numbers in words Office 2003
Advanced Beginner
Concatenate function for displaying numbers in words
 
Join Date: Oct 2013
Location: Australia
Posts: 40
officeboy09 is on a distinguished road
Default 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.
Attached Images
File Type: jpg 未命名.JPG (52.4 KB, 23 views)
Reply With Quote
  #2  
Old 10-22-2013, 03:17 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Concatenate function for displaying numbers in words Windows 7 64bit Concatenate function for displaying numbers in words Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,759
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #3  
Old 10-22-2013, 11:29 PM
BobBridges's Avatar
BobBridges BobBridges is offline Concatenate function for displaying numbers in words Windows 7 64bit Concatenate function for displaying numbers in words Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

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.
Reply With Quote
  #4  
Old 10-23-2013, 12:31 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Concatenate function for displaying numbers in words Windows 7 64bit Concatenate function for displaying numbers in words Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,759
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #5  
Old 10-24-2013, 12:17 AM
officeboy09 officeboy09 is offline Concatenate function for displaying numbers in words Windows XP Concatenate function for displaying numbers in words Office 2003
Advanced Beginner
Concatenate function for displaying numbers in words
 
Join Date: Oct 2013
Location: Australia
Posts: 40
officeboy09 is on a distinguished road
Default

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.
Attached Images
File Type: jpg 未命名.JPG (84.7 KB, 15 views)
Attached Files
File Type: xls Example JHC.xls (15.0 KB, 9 views)
Reply With Quote
  #6  
Old 10-24-2013, 12:41 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Concatenate function for displaying numbers in words Windows 7 64bit Concatenate function for displaying numbers in words Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,759
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #7  
Old 10-24-2013, 12:45 AM
officeboy09 officeboy09 is offline Concatenate function for displaying numbers in words Windows XP Concatenate function for displaying numbers in words Office 2003
Advanced Beginner
Concatenate function for displaying numbers in words
 
Join Date: Oct 2013
Location: Australia
Posts: 40
officeboy09 is on a distinguished road
Default

Quote:
Originally Posted by Pecoflyer View Post
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
So, "SpellNumber" is not a standard function in excel. It has to be defined and imported to excel before it can work.

I have the original xls file from the person who made it, but where can I find his code for "SpellNumber"?
Reply With Quote
  #8  
Old 10-24-2013, 12:46 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Concatenate function for displaying numbers in words Windows 7 64bit Concatenate function for displaying numbers in words Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,759
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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.
Reply With Quote
  #9  
Old 10-24-2013, 07:35 AM
BobBridges's Avatar
BobBridges BobBridges is offline Concatenate function for displaying numbers in words Windows 7 64bit Concatenate function for displaying numbers in words Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

<Alt-F11>? I never knew that; I've been using <Alt-L><V> all this time. Thanks, Peco.
Reply With Quote
  #10  
Old 10-30-2013, 02:53 AM
officeboy09 officeboy09 is offline Concatenate function for displaying numbers in words Windows XP Concatenate function for displaying numbers in words Office 2003
Advanced Beginner
Concatenate function for displaying numbers in words
 
Join Date: Oct 2013
Location: Australia
Posts: 40
officeboy09 is on a distinguished road
Default

Quote:
Originally Posted by Pecoflyer View Post
In the VBA editor you can open with ALt+F11 as I indicated. Also Google around and look for User Defined Functions in Excel

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?
Attached Files
File Type: xls UDF_A28.xls (36.5 KB, 9 views)
Reply With Quote
  #11  
Old 10-30-2013, 05:43 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Concatenate function for displaying numbers in words Windows 7 64bit Concatenate function for displaying numbers in words Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,759
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #12  
Old 10-30-2013, 09:48 AM
BobBridges's Avatar
BobBridges BobBridges is offline Concatenate function for displaying numbers in words Windows 7 64bit Concatenate function for displaying numbers in words Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

Quote:
Originally Posted by Pecoflyer
Open the editor as suggested
In the VBA Project window you have a file VBAProject(UDF_A28.xls) {A}
One of it's sub files is called Modules
There you will find a Module1 {B}
Double click on it and a window with the UDF code opens
There find SpellNumber {C}.

See attachment.
Attached Images
File Type: png x.png (53.9 KB, 16 views)
Reply With Quote
  #13  
Old 10-30-2013, 08:08 PM
officeboy09 officeboy09 is offline Concatenate function for displaying numbers in words Windows XP Concatenate function for displaying numbers in words Office 2003
Advanced Beginner
Concatenate function for displaying numbers in words
 
Join Date: Oct 2013
Location: Australia
Posts: 40
officeboy09 is on a distinguished road
Default

Quote:
Originally Posted by Pecoflyer View Post
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
Thx! It's quite a complicate function. I have to take sometime to figure out why the previous user made it that way.
Reply With Quote
  #14  
Old 08-09-2021, 12:59 PM
duncanweishaar826 duncanweishaar826 is offline Concatenate function for displaying numbers in words Windows 10 Concatenate function for displaying numbers in words Office 2016
Novice
 
Join Date: Aug 2021
Posts: 1
duncanweishaar826 is on a distinguished road
Default

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)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Concatenate function for displaying numbers in words 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
Concatenate function for displaying numbers in words 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
Concatenate function for displaying numbers in words displaying start and finish times as absolute numbers instead of dates benson.wallace Project 2 12-05-2010 07:22 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 08:48 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft