I want to enter a product code, but it converts to currency, how do I stop that?

English
,
#1

Hi!

I’m from South Africa, our currency is Rands, e.g. R150.50.
A few of our products have the suffix ‘R’, e.g. 22R, 212R, etc. When I enter ‘22R’ into a cell it automatically converts it to currency as, R22.00. If I then click the Currency button it changes it to simply, ‘22’. No ‘R’. (Also, instead of a ‘.’ separator, ‘R22.00’ as per my system settings, it uses a ‘,’ ‘R22,00’)

I’m sure I’m missing a simple step here, I have looked through the options, tried turning off AutoInput, etc. I’ll admit, I’m fairly new to LO, I’ve used it for a little over a month now. As a side note, the program could be a little more idiot proof (me proof). It took me until yesterday to learn how to stop ‘-’ automatically becoming a long dash.

While I’m here, as I think the following issue is similar; if I enter the date as 2022/06/14 it auto’s to 2022-06-14. How do I set ‘/’ separator as default?

I need these settings to be default.

Thanks
James.

#3

Re the decimal separator, South Africa officially uses the comma as the separator, see Decimal separator - Wikipedia . LibreOffice looks at your locale and uses the correct separator. As Mike Kaganski wrote you need to change your locale.

Can I make a plea for you not to confuse another date format? YYYY-MM-DD is ISO 8601 and is well interpreted by spreadsheets; other formats come with interoperability issues.

#5

I had my terminology incorrect in the OP, when I said separator, I meant decimal symbol (I hope I have it right now).

Shucks, so you’re suggesting I give up my preferred slashes for dashes? :astonished:

I used Excel for years and I never had these issues. The one that bothers me most is what got me to post. I understand R22 converting to currency, but not 22R.

#2

Just format your cells (whole column?) as Text prior to entering the data. You might want to use a dedicated cell style for that.

Dates use the default format defined in locale. So if you need a different default, you need to select a different locale.

Then, in addition to a different locale, you might need to set up a template with wanted settings (say, required cell style having Text format), and set that template as default.

#4

I was hoping to flick some switches or something :stuck_out_tongue: Which I wish was the case, this is going to double the time of these actions.

When you say locale, you mean in Windows? If so, it uses ‘/’ with dates, and ‘.’ with currency (’,’ as list separator. I got the terminology wrong in my OP).

Out of curiosity I went through the LO locale settings for date formats in English, none of them default to YYYY/MM/DD. (I had a similar problem testing out WPS Office, it also claimed to follow my system settings, it did not.)

Out of further curiosity, I temporarily installed Excel. All the issues I’m having with LO are automatically synced to my locale settings on Windows; 22R remains text, R22.50 converts to currency (with a ‘.’ not a ‘,’), and 2022/06/14 converts to date (retaining system/locale format; ‘/’ not ‘-’).

I thank you for your response, I will find ways to work with it. It’ll probably become second nature. That said, it would be nice if it just worked, or if their were options to settle what, to me, seems to be conflicts between Windows locale settings and the way LO interprets them.

Thanks again
James

#6

You might want to follow the settings example given in the original question in Decimal separator key
Cheers, Al

#7

Simply enter your product code as text. A product code as a numeric value makes no sense anyway.
As with any spreadsheet program of the past 30 years, you can prepare the cells in question with number format code @ (category “Text”). This will prevent all evaluation of your input. 001234 or =SUM(A1:A8) will be entered as literal text with no numeric value.

1 Like
#8

Up till now I’ve only used Excel. Out the box it handles the issues I’m having effortlessly. Is there some reason, I dunno, like proprietary code or something, which prevents LO from analyzing what is entered into a cell the same way that Excel does?

It seems silly to me, and time consuming, that every time I want to type ‘22R’, or anything with the suffix ‘R’, I have to first right-click, format, change to text. Similar story for if I want the date separator to be ‘/’ and not ‘-’.

Opening a new workbook in Excel, every cell is in a ‘General’ format, my issues in my OP are non-issues, all are converted correctly (per my locale settings, I imagine, and the program settings).

#9

LibreOffice does not take system regional settings into account (tdf#46448 and tdf#73242). Thus it uses hard-coded locale settings built into itself: when you select English (South Africa), it uses the date format defined in LibreOffice for that locale. Hence dashes instead of slashes (indeed, I would love those all silly slashes/dots with unpredictable part order date formats to be dropped finally, and I envy locales that adopted the unambiguous ISO format, so it’s astonishing that people enjoying living in those locales prefer ambiguous formats … but I digress; implementing taking into account system settings would be nice in any case. For en-ZA, there was tdf#119613 that changed semi-standard older defaults to truly standard current ones, but as people use ones or the others, no single solution is satisfactory for everyone).

As for trailing currency symbol detection, possibly it makes sense to file a bug (likely it simply tries to detect it in both positions, knowing that there are locales with leading currency symbols (e.g., en-*) and with trailing (like ru)); but when you enter text into a column, it’s always good to pre-format the whole column as text, to not have to care about it cell-by-cell.

#10

Spreadsheets are somewhat excentric in what they interprete as number and what not. Excel behaves in the same way as Calc but not in your particular use case.
There are other use cases where nobody would even expect that the behaviour of Excel could be wrong in any way. This is what happens instead: Scientists Rename Genes So Excel Won't Reformat Them as Dates - ExtremeTech

2 Likes

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.