Phone cell format with or without "+"

[josemoya]

Hi!
I would like to add a phone format to calc cells.
If the number inside the cell is “912345678”, the cell would display “912 34 56 78” (a regular Spanish nine digit phone number)
(format “### ## ## ##”)
BUT if the number inside the cell is “+34912345678” (an international number), the cell would display either “+34 912 34 56 78”, or the number as is.

I don’t want to use the REGEXP function since I don’t want an entry cell paired with a formula cell. I just want an entry cell.

I don’t want a forced plus sign, since in phone numbers a plus sign means “number with international prefix added”.

[eeigor]

Read about formatting in Excel. Everything is very similar in Calc.

[josemoya]

Thanks, elegor.

As LibreOffice conditional formatting overwrites cell number format, I’m thinking about leaving my standard phone formatting (### ## ## ##) and apply an alternative “@” format via conditional formatting.

Or, better, do it the reverse way, so cell is always treated as text (plus sign is not discarded) and a number formatting is applied if it starts with “0” to “9”

Regards,
José G. Moya Y.

[erAck]

You can try a number format code like
[<=999999999]### ## ## ##;[>999999999]+## ### ## ## ##;General
but be advised that any leading 0 zero will vanish if the cell is numeric and not text.

[Villeroy]

If you would simply store your data in a database…
https://forum.openoffice.org/en/forum/viewtopic.php?f=61&t=10132&p=47422

[josemoya]

No, I can’t use a database.
The data should be accesible in a private cloud, and cloud software do not allow databases (in spite of most cloud software using databases internally).

I miss those times where office software proficiency meaned some proficiency with at least one database solution (be it dbase, filemaker, access, foxpro, or libreoffice base).
Regards,
José G. Moya Y.

[erAck]

@josemoya

The data should be accesible in a private cloud, and cloud software do not allow databases

Telephone numbers can be stored in any CardDAV server compliant application, which for cloud based access is much better suited than any spreadsheet or database could be. You can even sync among devices using anything that speaks CardDAV. Nextcloud comes with the Contacts app that does exactly that in a private cloud.

[Villeroy]

Base can use dBase files.

[Villeroy]

@Hrbrgr

@Villeroy , please write comments, your answer is not a solution to the initial question. See also: This is the guide - How to use the Ask site? - #3 by Hrbrgr

I just uploaded 20 MB dBase to a cloud server, connected a local Base document to that database and executed some queries. It looks a lot more promising than any calculator sheet on clouds. If the cloud service provides some locking mechanism, that would be a viable solution.
CardDAV suggested by erAck might be the best solution though.

[josemoya]

Hi all:

I’m working with a document that will be shared with a person who does not have background in databases. I want to edit my document from both desktop and mobile devices without installing any app or giving away any database.

I should use my Owncloud/Nextcloud solution since data policy commits me to store data in the enterprise server, located not only in my country, but in my region — 100 meters away of me, indeed.

Also, my tables are not just phone listings. They are loan listings. Phone number is just one of the fields (being the others name, surname, id card or passport number, lent item, loan start, loan renewal, loan end). Most “contacts” solutions can’t manage such “cards”.

That leaves me with a Collabora Online spreadsheet (as a Owncloud/Nextcloud module.

If I was doing a loan system just for myself and I was sure I would use it only from a desktop computer, I would take LibreOffice Base into consideration (though Access has a more comprehensive programming help, and an interactive VB prompt). But that is not the case.

Yours,
José G. Moya Y.

[Villeroy]

Solution 1: Any address book solution provided by your cloud hoster such as Card DAV.
Solution 2: Well, then try the Collabora spreadsheet. However, a spreadsheet is a program for arithmetic calculation. The focus is on numbers and formulas rather than text. Phone “numbers” and similar IDs should be strored as text with leading zeroes. plus signs, braces. Any number format applies to numbers. Number formats lie about the true content of a cell. Spreadsheets do not support any form controls because spreadsheets do not support fields nor records.
Solution 3: A database server with a Base frontend. This is not availlable on the usual cloud hosters.

All 3 solutions are viable without programming, however you won’t get away without some configuration.