[Solved] What is the syntax for the "User Defined" number format

Discuss the spreadsheet application
Post Reply
CrimsonShroud
Posts: 3
Joined: Sat Sep 24, 2022 12:40 pm

[Solved] What is the syntax for the "User Defined" number format

Post by CrimsonShroud »

I am trying to create a table in Calc that will have a column dealing with weights. The data that will be entered will be in lbs but I want the cell to display the weight in either lbs or tons depending on the weight entered.

So for example:

If the number entered was 205 the displayed figure would show "205 lbs."
however if the number entered was 4000 then the display would show "2 tons"

I started working with the "User Defined" format and got a start on this based on some examples I found out on the web, but not sure how to continue. At present I have...

[>=2000]#,##0.000" tons";[<2000]#,##0.000" lbs";"";""

Which will append on the "lbs." or "ton" appropriately but displays "4,000 tons" when 4000 is entered. Obviously one piece I need is to get it to divide the number by 2000 when it is >= 2000.

Can anyone please help with this? AND/OR give me a link to documentation that where I can learn/reference the syntax. It looks a little like regex but I am not a regex expert. Is it regex? If so I can just look for documentation on regex.

I am going to be doing similar columns on this spreadsheet for TIME (Hours, Days, Months, Years, etc), DISTANCE (Inches, Feet, Yards, Miles, etc).
Last edited by CrimsonShroud on Sat Sep 24, 2022 7:04 pm, edited 1 time in total.
OpenOffice 4.1.6 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: What is the syntax for the "User Defined" number format

Post by Villeroy »

Look up "number formats;codes" in the help index.
It makes no sense to make a number look like a different number.
Has nothing to do with regex.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
karolus
Volunteer
Posts: 1158
Joined: Sat Jul 02, 2011 9:47 am

Re: What is the syntax for the "User Defined" number format

Post by karolus »

None of the NumberFormats changes the Value itself, except an explizit %format, which divide the manual input by 100
AOO4, Libreoffice 6.1 on Rasbian OS (on ARM)
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: What is the syntax for the "User Defined" number format

Post by keme »

Change to metric. Then you can append a thousands separator character to the format code to go from kg to metric tonne, similarly to the percent conversion mentioned by karolus. There is no signifying character like the percent format enforces, so you need to supply unit separately in the format string, like you did in your own example.

I know: that change to metric is not going to happen any time soon.

Seriously: Any conversion of units which is not intimately tied to the number system in use, needs to happen in another cell, by way of formula. There is no such conversion by format. This is by design, and helps to prevent some accidents.

The thousands stepping I mentioned above is actually quite useful sometimes, but care needs to be taken to avoid misunderstanding.
CrimsonShroud
Posts: 3
Joined: Sat Sep 24, 2022 12:40 pm

Re: What is the syntax for the "User Defined" number format

Post by CrimsonShroud »

Thanx Everyone. Unfortunately the data is in pounds and the audience for the spreadsheet is only going to understand pounds and tons. The problem is some of the data is pretty large, going into the ktons. I was hoping to be able to reformat it at least into tons instead of displaying 2,000,000 lbs.
Does not look like the solution to this problem is here. I am looking into alternatives. Thanx again every one.
OpenOffice 4.1.6 on Windows 10
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: What is the syntax for the "User Defined" number format

Post by keme »

CrimsonShroud wrote: Sat Sep 24, 2022 7:03 pm Thanx Everyone. Unfortunately the data is in pounds and the audience for the spreadsheet is only going to understand pounds and tons. The problem is some of the data is pretty large, going into the ktons. I was hoping to be able to reformat it at least into tons instead of displaying 2,000,000 lbs.
Does not look like the solution to this problem is here. I am looking into alternatives. Thanx again every one.
The solution I can see is to create a formula in a separate cell. If it is tabular data, the formula can be copied down a separate column. You can then choose when to display the input data and when to display the converted data, either by using column gouping or by having separate "rendering sheets" with extracted/recalculated data.

How to solve this in practice depends on how your data gets into your files. Do you enter them manually, get a CSV file exported from some other entity, or what?
CrimsonShroud
Posts: 3
Joined: Sat Sep 24, 2022 12:40 pm

Re: [Solved] What is the syntax for the "User Defined" number format

Post by CrimsonShroud »

Currently the data is being entered manually but I am just really working on the UI and at some point in the future would want o be able to populate the data from a DB procedure. I am assuming one that would create a CSV file
OpenOffice 4.1.6 on Windows 10
User avatar
karolus
Volunteer
Posts: 1158
Joined: Sat Jul 02, 2011 9:47 am

Re: [Solved] What is the syntax for the "User Defined" number format

Post by karolus »

The whole "dataprocessing" should be done by exactly one unit the conversion should only happen on the user-interface ( for both input and output)
Regarding .csv: the worse thing you can do would be different units in one column on csv-export.
AOO4, Libreoffice 6.1 on Rasbian OS (on ARM)
Libreoffice 7.4 on Debian 12 (Bookworm) (on RaspberryPI4)
Libreoffice 7.6 flatpak on Debian 12 (Bookworm) (on RaspberryPI4)
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] What is the syntax for the "User Defined" number format

Post by Villeroy »

CrimsonShroud wrote: Sun Sep 25, 2022 6:47 pm Currently the data is being entered manually but I am just really working on the UI
A spreadsheet has not such thing as a UI for user input. Each cell can take anything.
The attached database has one table with a constraint and 2 separate forms (the UI) for user input.
The table constraint allows you to enter either lbs or tons but not both nor none.
Attachments
lbs_tons.odb
(21.14 KiB) Downloaded 55 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply