Page 1 of 1
[Solved] TEXT() function: text delimiter release character?
Posted: Sun Feb 05, 2012 3:47 am
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
in the format field, but this doesn't work in the TEXT() formula. If I write
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
Re: TEXT() function: what is text delimiter release characte
Posted: Sun Feb 05, 2012 4:17 am
by Villeroy
=TEXT(A1;"000""-""000""-""0000")
Re: TEXT() function: what is text delimiter release characte
Posted: Sun Feb 05, 2012 8:31 am
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.
Re: TEXT() function: what is text delimiter release characte
Posted: Sun Feb 05, 2012 12:29 pm
by kingfisher
What error do you get Adhocrat? What is the value you wish to convert?
Re: TEXT() function: what is text delimiter release characte
Posted: Fri Feb 10, 2012 6:20 am
by qaz1qaz1qa
If your just trying to format Phone Numbers then try creating a Userdefined Number
###-###-#### Or (###)###-####
Re: TEXT() function: what is text delimiter release characte
Posted: Fri Mar 09, 2012 11:19 pm
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.
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.
Re: TEXT() function: what is text delimiter release characte
Posted: Sat Mar 10, 2012 12:05 am
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)
Re: TEXT() function: what is text delimiter release characte
Posted: Sat Mar 10, 2012 11:07 pm
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:
I would expect to see
if \ is the release character. (This is how C++ works.)
Instead, OpenOffice rewrites my input:
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,
Calc removes the last speechmark, which displays
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.
Re: TEXT() function: what is text delimiter release characte
Posted: Sun Mar 11, 2012 12:20 am
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
which is a formula returning a single speechmark
Re: TEXT() function: what is text delimiter release characte
Posted: Sun Mar 11, 2012 12:21 am
by coray80
perhaps
Code: Select all
="This is a speechmark: "&CHAR(34)
Re: TEXT() function: what is text delimiter release characte
Posted: Sun Mar 11, 2012 4:55 am
by Adhocrat
coray80 wrote:perhaps
Code: Select all
="This is a speechmark: "&CHAR(34)
Excellent. Thanks a lot!
Re: TEXT() function: what is text delimiter release characte
Posted: Wed Mar 14, 2012 7:25 pm
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
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!