We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

Number format code that multiplies by powers of 1000 [closed]

asked 2012-10-30 00:07:07 +0200

James Haigh gravatar image

updated 2012-11-08 14:16:42 +0200

I want to keep the values in the cells in plain metric units such that all formulas are the basic relations as would be found in a textbook, with no unnecessary, confusing, and mistake-prone constants in the formulas.

However, I want to display the values scaled to an easier to read order of magitude.

For division by powers of 1000, I can use commas, so I can use the format codes:


Or I could even construct something more generic like this:


(There seems to be a bug that prevents me from using more than 3 conditions, so for now G is omitted.)

But for m, μ, n, etc., I cannot find a way.

I found this clever looking hack but the percents don't seem to work properly:


edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-10-26 22:19:47.606072


Could you possibly give explicit examples here using just characters available for answers of what you want to achieve and what you get or not get?

ROSt52 gravatar imageROSt52 ( 2012-10-30 02:14:20 +0200 )edit

@ROSt52: For example, I want to format 0.00001234567 as 12.346μ.

James Haigh gravatar imageJames Haigh ( 2012-10-30 19:03:04 +0200 )edit

@James: What I would do is using the link of chandoo and experiment. I don't have enough time to do it myself but I would give it try. It seems very feasilble. % creates mutlipes of 10.

ROSt52 gravatar imageROSt52 ( 2012-10-31 02:15:50 +0200 )edit

@James: could stop to make a test % and , behave in LibO differently. Thus for me the question should be what are the multiplier and divider symbols in Calc. Would raise question in users@global.libreoffice.org

ROSt52 gravatar imageROSt52 ( 2012-10-31 02:27:49 +0200 )edit

@ROSt52: Did you find anything? I don't have the proprietary alternative to actually try the test you suggest.

James Haigh gravatar imageJames Haigh ( 2012-11-06 13:04:42 +0200 )edit

@James: What do you mean with "proprietary alternative"? Microsoft Office/EXCEL? I don't have it on my PC anymore. Rely fully on LibO . What do you mean with "trainling point"?

ROSt52 gravatar imageROSt52 ( 2012-11-06 15:46:31 +0200 )edit

@James:made a test: entered 1 and formated cell with 0.### and 1 appeared as 1. I don't understand what you mean with the bug mentioned above.

ROSt52 gravatar imageROSt52 ( 2012-11-06 15:50:06 +0200 )edit

@ROSt52: Sorry - as I was correcting myself this website went offline. If 1000 is formatted with 0.###,"k" it is displayed as '1.k' rather than '1k'. Also try formatting an integer with 0.###"text"; you should get something like '1.text' rather than '1text'.

James Haigh gravatar imageJames Haigh ( 2012-11-08 13:40:41 +0200 )edit

@ROSt52: Btw, % multiplies by 100, not 10. According to the chandoo article, multiplying by 10 is achieved by 2 percents and a comma, since x *100 *100 /1000 = x *10. And dividing by 10 can be done with a percent and a comma, since x *100 /1000 = x /10.

James Haigh gravatar imageJames Haigh ( 2012-11-08 13:52:11 +0200 )edit

@ROSt52: And yes, I meant m$'s spreadsheet program, which I got rid of years ago and switched to OOo, long before Oracle bought Sun and LibO had forked off OOo.

James Haigh gravatar imageJames Haigh ( 2012-11-08 14:06:57 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2013-03-01 05:42:43 +0200

qubit gravatar image

Hi @James Haigh,

Did you ever find a solution to your formatting question?

If you think that changes to the LibreOffice code could help you implement your desired formatting, please file an enhancement bug and provide as much information about this new feature as possible. Don't forget to mark your bug as an 'enhancement'. The QA team will be happy to help you triage your feature request in the bugtracker.

Please post a link to any bugs you file in a comment below using the format "fdo#123456".


edit flag offensive delete link more

answered 2012-11-09 02:12:03 +0200

ROSt52 gravatar image

updated 2013-03-14 01:58:09 +0200

qubit gravatar image


0.### format on a 1 shows correctly the result 1. Reason ### displays a maximum of 3 digits after the decimal point. Try to enter 1, 1.1, 1.11, 1.111, 1.11111 and you will see.

It is important to understand the 0 and #. I would neglect this first and work only with a 0. Your major problem is to adjust the display of a value smaller than 1 as as figure larger then 1 and not having the % visible.

I did a row of tests and found that - Calc does not accept the <crt j> or <crtlj> as a format. I don't know why. - "k" is not need just k is enough - comma and % work in the way as indicated in the link you provided when you raised your question

Problem I could not solve was the % shows up. Possible solution - ask a new question on how the % sign can be made disappearing in Calc or if there is another multiplier by 100 which does not show up like the comma for a division by 1000 - read through all the additional links given in the link you provided at the beginning

If you would like to have my test sheet, let me know.

Please understand that I cannot do more testing and searching for you. I feel I brought you on the track, good luck.

edit flag offensive delete link more


I do understand 0 and #. As I explained in a previous comment, the site crashed when I was trying to correct myself. Grrr! When the site came back online, I was no longer able to edit the comment. To avoid further confusion, I've just deleted the comment.

James Haigh gravatar imageJames Haigh ( 2012-11-09 13:10:14 +0200 )edit

However, I do not understand what you mean by "<crt j="">". The double quotes around k may indeed not be necessary in this case, but without knowing with certainty every single format code character, it is a good idea to quote any text characters just in case they get interpreted as format code.

James Haigh gravatar imageJames Haigh ( 2012-11-09 13:18:51 +0200 )edit

If you could attach your test spreadsheet to this answer, that would be great, thanks.

James Haigh gravatar imageJames Haigh ( 2012-11-09 13:23:17 +0200 )edit

<crt j=""> sorry but this is rubbish. I don't know what happened, it should be <ctrl j> ( a space between ctrl and j. I don't see a possibility to attach a file here. Ask your question in users@global.libreoffice.org. I see a chance to mail you the file there

ROSt52 gravatar imageROSt52 ( 2012-11-09 15:42:46 +0200 )edit

Additional, could you possibly create an example showing - the data as you get them - as you want to enter them - as you want to display them and explain why you want to have it that way. Reason: I MIGHT have a kind of workaround if the % problem cannot be solved.

ROSt52 gravatar imageROSt52 ( 2012-11-09 15:43:21 +0200 )edit

Question Tools

1 follower


Asked: 2012-10-30 00:07:07 +0200

Seen: 5,542 times

Last updated: Mar 14 '13