[Solved] TEXT() function: text delimiter release character?

Discuss the spreadsheet application
Post Reply
Adhocrat
Posts: 5
Joined: Sun Feb 05, 2012 2:54 am

[Solved] TEXT() function: text delimiter release character?

Post by Adhocrat »

Hi all,

I'm trying to write a formula which uses the TEXT() function to format a number with the format 000-000-0000.

I can use that format in a cell by writing

Code: Select all

000"-"000"-"0000
in the format field, but this doesn't work in the TEXT() formula. If I write

Code: Select all

=text(A1;"000-000-0000")
I get an error. How do I put the intermediate quote marks (text delimiters) into another line of text?

If it was C++, the string

Code: Select all

"this is a quote mark: \" Isn't it nice?"
would be interpreted as

Code: Select all

this is a quote mark " Isn't it nice?
The \ character before the " means that the " is regarded as a character and not as the end of the text. What is the OpenOffice equivalent?

Thanks,
the Adhocrat
Last edited by Adhocrat on Sun Mar 11, 2012 4:56 am, edited 1 time in total.
OpenOffice 3.3.0 on corporate WinXP
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: TEXT() function: what is text delimiter release characte

Post by Villeroy »

=TEXT(A1;"000""-""000""-""0000")
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
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: TEXT() function: what is text delimiter release characte

Post by MrProgrammer »

Adhocrat wrote:The \ character [in a C++ string means it] is regarded as a character and not as the end of the text. What is the OpenOffice equivalent?
In a number format (or the second operand of the TEXT function) you can use the backslash → \
OOo Help wrote:To include text in a number format that is applied to a cell containing numbers, place a double quotation mark (") in front of and behind the text, or a backslash (\) before a single character.
So you could also use =TEXT(A1;"000\-000\-0000").
Adhocrat wrote:If I write =text(A1;"000-000-0000") I get an error.
This may be undocumented, but a few characters, including the dash, don't need to be escaped so I can use =TEXT(A1;"000-000-0000") as well. It works for me (using OOo 3.2). I suspect this behavior is needed for compatibility with Excel. I don't know why you get an error.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
kingfisher
Volunteer
Posts: 2123
Joined: Tue Nov 20, 2007 10:53 am

Re: TEXT() function: what is text delimiter release characte

Post by kingfisher »

What error do you get Adhocrat? What is the value you wish to convert?
Apache OpenOffice 4.1.9 on Linux
User avatar
qaz1qaz1qa
Posts: 103
Joined: Fri Jan 27, 2012 1:36 am

Re: TEXT() function: what is text delimiter release characte

Post by qaz1qaz1qa »

If your just trying to format Phone Numbers then try creating a Userdefined Number
###-###-#### Or (###)###-####
OpenOffice 3.3 XP If I am never wrong then I know everything and cannot improve. From the looks of things I have a lots to do.
Adhocrat
Posts: 5
Joined: Sun Feb 05, 2012 2:54 am

Re: TEXT() function: what is text delimiter release characte

Post by Adhocrat »

Thanks for the help. The object was to format patient's National Health Service numbers, which come in the format 000-000-0000. I don't understand why =text(a1;"000-000-0000") didn't work before. At the time I got round it by using the cell's number formatting with the code "NHS: "000"-"000"-"0000. Didn't realise I don't need the quotes. :knock:

Mildly related question: how do I include a speechmark into a string? ="This is a speechmark: \" " doesn't work. Calc insists on adding an extra speechmark to the end without asking and then gives me error 509, operator missing.

Actually Calc generally has an absolutely infuriating habit of changing my input without asking and refusing to supply a convenient way to undo its unwelcome changes. In certain other office applications, if the software autocorrects something and you don't like it, you can reverse the autocorrect with the 'undo' function and then hunt through the menus to turn off the unwanted feature. OOo's automatic input-fiddling doesn't get entered into the undoable-actions queue, frustratingly.
OpenOffice 3.3.0 on corporate WinXP
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: TEXT() function: what is text delimiter release characte

Post by Villeroy »

Actually Calc generally has an absolutely infuriating habit of changing my input without asking and refusing to supply a convenient way to undo its unwelcome changes.
It is a calculator. It can only calculate if it evaluates all numeric input and all formula expressions.
Of course this can be annoying when 90% of todays users misuse it as poor man's database with lots of text fields.

000-000-0000 is a text anyway and TEXT(A1;"000-000-0000") returns the unaltered text of A1. IMHO, it should raise an error if the first argument is not a number.

It would be important to know which exact value you have in A1 and which value you want get. Is A1 a number or a text. Which number? Which text? =ISNUMBER(A1)
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
Adhocrat
Posts: 5
Joined: Sun Feb 05, 2012 2:54 am

Re: TEXT() function: what is text delimiter release characte

Post by Adhocrat »

A1 is a 10-digit number, ie 1234567890. Perhaps I wasn't clear enough - =text(a1;"000-000-0000") works fine (as does ="NHS: "&text(a1;"000-000-0000") for that matter) so I don't understand where I went wrong before.

The auto-reformat comment was for something else, but relating to release characters. If I write in:

Code: Select all

="This is a speechmark: \" "
I would expect to see

Code: Select all

This is a speechmark: " 
if \ is the release character. (This is how C++ works.)
Instead, OpenOffice rewrites my input:

Code: Select all

="This is a speechmark: \" ""
Note the extra speechmark at the end. Then it gives a 509 error saying the operator's missing. I don't want Calc to decide to put an extra speechmark into my input without giving me the easy option to undo its changes.

If I omit the space between the last two " in my input,

Code: Select all

="This is a speechmark: \""
Calc removes the last speechmark, which displays

Code: Select all

This is a speechmark: \
Is there a release character allowing me to put a speechmark into the middle of text strings?

In the past I've had to do hideous things like

Code: Select all

A1: "
A2: ="This is a speechmark: "&$A$1
I use Calc as a calculator. And also as a tool for displaying the results of the calculations. I write formulas which, for instance, turn a user's shift times into a detailed breakdown of overtime, weekend, antisocial hours, and bank holiday working hours. Then I need to format the resulting numbers to print over the required timesheets. I would be interested to know if this type of task could be done better using databases, but the suggestion appears bizarre to me.
OpenOffice 3.3.0 on corporate WinXP
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: TEXT() function: what is text delimiter release characte

Post by Villeroy »

Adhocrat wrote:A1 is a 10-digit number, ie 1234567890. Perhaps I wasn't clear enough - =text(a1;"000-000-0000") works fine (as does ="NHS: "&text(a1;"000-000-0000") for that matter) so I don't understand where I went wrong before.
Many spreadsheet users, even fairly experienced ones, have difficulties understanding the difference between text value "1234567890" and number 1234567890. As a text it is just a sequence of characters that happen to be digits and no number format has any effect on a text. As a number there are thousands of number formats to display the exact same value in different ways.
Text "1" is a different value than text "01" whereas number 1 is exactly the same value as formatted numbers 01, 1899-12-31, 24:00:00, 100% and $1.00.

The escape character for a speechmark in formula context is the speechmark.

Code: Select all

="This is one speechmark: "" with some additional text"
The first one opens the literal string, the second and third give one literal speechmark and the last one closes the literal string.
Same with the 4 speechmarks in

Code: Select all

=""""
which is a formula returning a single speechmark
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
coray80
Volunteer
Posts: 357
Joined: Thu Mar 01, 2012 6:41 am

Re: TEXT() function: what is text delimiter release characte

Post by coray80 »

perhaps

Code: Select all

="This is a speechmark: "&CHAR(34)
OpenOffice 4.1.2 on Windows 7
If your question has been answered please add [solved] to the title by using the edit button at your first post
Adhocrat
Posts: 5
Joined: Sun Feb 05, 2012 2:54 am

Re: TEXT() function: what is text delimiter release characte

Post by Adhocrat »

coray80 wrote:perhaps

Code: Select all

="This is a speechmark: "&CHAR(34)
Excellent. Thanks a lot!
OpenOffice 3.3.0 on corporate WinXP
Adhocrat
Posts: 5
Joined: Sun Feb 05, 2012 2:54 am

Re: TEXT() function: what is text delimiter release characte

Post by Adhocrat »

Villeroy wrote:
Adhocrat wrote:A1 is a 10-digit number, ie 1234567890. Perhaps I wasn't clear enough - =text(a1;"000-000-0000") works fine (as does ="NHS: "&text(a1;"000-000-0000") for that matter) so I don't understand where I went wrong before.
Many spreadsheet users, even fairly experienced ones, have difficulties understanding the difference between text value "1234567890" and number 1234567890. As a text it is just a sequence of characters that happen to be digits and no number format has any effect on a text. As a number there are thousands of number formats to display the exact same value in different ways.
Text "1" is a different value than text "01" whereas number 1 is exactly the same value as formatted numbers 01, 1899-12-31, 24:00:00, 100% and $1.00.

The escape character for a speechmark in formula context is the speechmark.

Code: Select all

="This is one speechmark: "" with some additional text"
The first one opens the literal string, the second and third give one literal speechmark and the last one closes the literal string.
Same with the 4 speechmarks in

Code: Select all

=""""
which is a formula returning a single speechmark
Thanks very much Villeroy, I missed this last time. This is exactly what I wanted to know. Very peculiar that the string delimiter is the string release character but nevermind!
OpenOffice 3.3.0 on corporate WinXP
Post Reply