Ask Your Question

Formula - SpellNumber

asked 2018-01-13 18:59:33 +0200

EshBoo gravatar image

In Excel there's a "SpellNumber" formula.

Cell A1 contains a number - $100.50

Cell A2 will have the formula: =spellnumber=A1

Cell A2 will show this result: "One hundred dollars and fifty cents"

Now that I converted to Libre - It is not recognizing the formula and I can't find anything similar. Help anyone?

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2018-01-13 20:07:01 +0200

Mike Kaganski gravatar image

updated 2018-01-13 20:09:50 +0200

There is no "spellnumber formula in Excel". There is a macro that is described here.

There is a Numbertext extension that does the same for Calc, which is superior to Excel's primitive macro, because it allows this for multiple languages, some of them having much more gifficult rules for spelling numbers.

edit flag offensive delete link more


@Mike Kaganski BTW:
Do you know for what reason the language to apply in NumberText is retrieved from the document instead of going with the cell's language?
Avoid too much RAM usage?
Not having access from the function body to the calling cell object?

Lupp gravatar imageLupp ( 2018-01-13 20:43:10 +0200 )edit

Why would I? :) I'm not the extension author...

Mike Kaganski gravatar imageMike Kaganski ( 2018-01-13 20:46:24 +0200 )edit

...but you are clever and curious. Even lacking definite knowledge you are likely to guess right at least.

Lupp gravatar imageLupp ( 2018-01-13 20:48:15 +0200 )edit

As far as I can tell, the cell's format is unknown at the moment of formula evaluation. Consider conditional formatting. It is evaluated after the cell's value is already decided, and the format's style includes locale/language information. So, applying it could change the cell language and potentially trigger infinite loop.

Mike Kaganski gravatar imageMike Kaganski ( 2018-01-14 11:18:05 +0200 )edit

I wouldn't expect an endless loop in that case. The conditionally overlaid format would not trigger a recalculatioon, would it? A recalculation triggered otherwise might change the display, however. This might also occur if the function ISLOGICAL() is used in the condition. (There are flaws insofar.)
The process working on a cell's or range's formula must know the cell or range. After all the output must be directed somewhere. In addition there is automatic formatting sometimes.

Lupp gravatar imageLupp ( 2018-01-14 20:37:13 +0200 )edit

I didn't say that cell or range is unknown; only that format is unknown. Well, OK, endless loop is unlikely (impossible). But still, function result dependent on style isn't good (unless it returns the style :))

It would be better if we could use some number format codes for number spelling, then it would be natural to depend on language of the cell...

Mike Kaganski gravatar imageMike Kaganski ( 2018-01-14 20:46:02 +0200 )edit
Mike Kaganski gravatar imageMike Kaganski ( 2018-01-14 21:34:03 +0200 )edit

Not sure how "superior" this is over Excel. 1. I need at least first letter Capitalized. Excel does it. This extension didn't. 2. The result gave me "three thousand one hundred and fifty U.S. dollars" - U.S is not needed and makes it ridicules. Just Dollars will look better.

Looking for a result like this - "Three thousand, one hundred Dollars and fifty Cents" Or better yet - "Thirty one hundred Dollars and fifty Cents" Is that doable?

EshBoo gravatar imageEshBoo ( 2018-01-24 03:14:10 +0200 )edit

Use simple formulas over the result, that just removes U.S. You know, U.S. dollars are just one of possible dollar kinds, so if you need "simple" - it's in your hands.

Mike Kaganski gravatar imageMike Kaganski ( 2018-01-24 05:57:46 +0200 )edit

@EshBoo: The standardized international abbreviation for your currency is neither "$" nor "U. S. $" but "USD" and there are more than 20 currencies in the world named "Dollar". In addition there is no standard for "the cents" except the decimal hundredth of a dollar.
If the actual functionality isn't in the center of your intentions, you may better play with user code.
And: Leave funny pseudo-standards to Excel.
What do you mean by "at least first letter"?

Lupp gravatar imageLupp ( 2018-01-24 13:57:34 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-01-13 18:59:33 +0200

Seen: 211 times

Last updated: Jan 13