Ask Your Question

Formula - SpellNumber

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

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 +0100

Mike Kaganski gravatar image

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

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 +0100 )edit

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

Mike Kaganski gravatar imageMike Kaganski ( 2018-01-13 20:46:24 +0100 )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 +0100 )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 +0100 )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 +0100 )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 +0100 )edit
Mike Kaganski gravatar imageMike Kaganski ( 2018-01-14 21:34:03 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


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

Seen: 16 times

Last updated: Jan 13