Ask Your Question
0

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

2 Answers

Sort by » oldest newest most voted
1

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

Comments

@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
0

answered 2018-08-26 16:14:22 +0200

EshBoo gravatar image
  • This is what I mean by first letter Capitalized: "Three thousand one hundred and fifty U.S. dollars"

  • Just Dollars will look better - I don't care how many countries have a dollar currency. In each country when you say dollar, it's clear you're talking about that country's dollar. So when I send an invoice to my US customers, I don't need to clarify that it's a US dollar. They know. However, when it says US dollars, clients think "What? Of course it's US dollars, what else will it be?"

  • 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?

* Play with user code ? ? ? If i had a clue about that would I be here asking questions? I am a simple, humble user - Not a programmer...

edit flag offensive delete link more

Comments

This is what I mean by first letter Capitalized: "Three thousand one hundred and fifty U.S. dollars"

Afaict, you have been asked what specifically do words "at least" mean in your "at least first letter"; anf the two words have been emphasized...

Mike Kaganski gravatar imageMike Kaganski ( 2018-08-26 17:09:44 +0200 )edit

Since 6.1 (specifically, 6.1.1, because of a bug tdf#118261 in 6.1.0 release preventing that feature there), there is a new [NatNum12] formatting mode introduced in tdf#115007. It may be used in cell formatting, of in StarBasic Format function...

Editing en.sor in LibreOffice's shared/numbertext could already provide you necessary options; but requires getting familiar with the language of the files. StarBasic also allows you to make necessary user-defined function.

Mike Kaganski gravatar imageMike Kaganski ( 2018-08-26 17:11:26 +0200 )edit

For example, provided you use 6.1.1: formatting a cell with 100.50 with a format string like [NatNum12 USD]0.00 with English (US) cell locale will give you the old one hundred U.S. dollars and fifty cents.

Replacing USD:(\D+) $(\1: U.S. dollar, U.S. dollars, cent, cents) in the mentioned us.sor with USD:(\D+) $(\1: Dollar, Dollars, Cent, Cents) will give you one hundred Dollars and fifty Cents. No first letter capitalization though - it would require more tweaking.

Mike Kaganski gravatar imageMike Kaganski ( 2018-08-26 17:17:20 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 457 times

Last updated: Aug 26