Ask Your Question

Calc cell number format: Fill space with unicode character? [closed]

asked 2019-04-15 03:04:09 +0200

AndyNoCo gravatar image

I want to use the Calc number format option "asterisk+character" to fill the space in a cell with a special character between the dollar sign on the left and the number on the right.

To fill with a space, the cell number format would be: "$"* #,##0.00

To fill with a period, the cell number format would be: "$"*.#,##0.00

What would the cell number format be to replace the space or the period after the asterisk with a unicode character? In my case, I want to use the "hair space" (U+200A). Do I need a backslash and/or parentheses or square brackets? Or something else? Thanks in advance.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by erAck
close date 2019-04-17 22:31:00.237951


As a side note, in this example using "$" produces just a number format with a literal string. To have the format code recognized as a currency format and thus appear under the currency category, use the [$...] syntax as in other currency formats, at least [$$] (yes, double $$ as [$ introduces the currency modifier), so that would be [$$]* #,##0.00 for your example. To tie it to a locale as well specify the MS-LCID as well, e.g. [$$-409]* #,##0.00 for the en-US locale (see other "USD $ English (USA)" formats).

erAck gravatar imageerAck ( 2019-04-15 19:33:03 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2019-04-15 10:45:50 +0200

Opaque gravatar image

Hello - The answer is: "$"* #,##0.00 - but as you can see, you can't recognize the code U+200A. To get it into the format code, insert the character into an empty cell using Insert -> Special Character and copy the invisible cell content into the format description (Attention: The Insert button only gets active when you click the character you want to insert)

image description

Just one comment: I personally would avoid the use of invisible characters, if possible.

edit flag offensive delete link more


Thanks, Opaque. That works great. But is there a way to enter the hex code for the hair space (U+200A) in the number format? Instead of creating a hair space in a cell and then copying/pasting it into the number format?

Re: using invisible characters, if you set the optimal column width to "Add 0.00" for the cell with longest number in a column, then using a hair space as a fill character allows for dollar signs to all be aligned on the left, but with no visible space between the dollar sign and the longest number. Accountants like that. Please see my example.

erAck, I'll try to work in [$...] after I get the hex-code v. copy/paste thing worked out. Thanks.

C:\fakepath\Hair Space.ods

AndyNoCo gravatar imageAndyNoCo ( 2019-04-15 22:28:20 +0200 )edit

I'm not aware of any other way to get that directly as format using a special format code. At least I didn't find anything in LibreOffice Help and that's why I put it this way (maybe you understand better than myself what's in Help - search for "Number Format Codes"). Depending on your OS there may be a keyboard sequence with allows for input of utf-8 codes.

And please mark the question as "answered" by clicking the check mark ✓ next to answer, if the answer solved your problem. Thanks in advance.

Opaque gravatar imageOpaque ( 2019-04-16 14:40:48 +0200 )edit

Opaque's ingenious answer works perfectly. But I am embarrassed to say that my entire question was totally unnecessary to begin with.

If you go to the cell with the longest number, and set the optimal column width to Add: 0.0", there will be no space between the dollar sign and the longest number, and dollar signs for shorter numbers in the column will all be left-aligned. If you want space to the left or right, add padding in the Borders tab.

I had mistakenly thought that the fill character after the asterisk would be inserted at least once. It is not. And the number format I need is already built into LO Calc - it's the last one in the USA currency formats list.

Nevertheless, Opaque has taught me how to add some other non-standard character to a number format if I want to. Nicely done!

In defense ...(more)

AndyNoCo gravatar imageAndyNoCo ( 2019-04-16 20:42:51 +0200 )edit

Question Tools

1 follower


Asked: 2019-04-15 03:04:09 +0200

Seen: 19 times

Last updated: Apr 15