You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openoffice.apache.org by "Vince@Verizon" <wa...@verizon.net.INVALID> on 2022/02/24 02:33:32 UTC
Formatting for USA Telephone Numbers
Hi:
How do I format cells for entry of USA Telephone Numbers, as: 1
(###) ###*-*####
whenever, for example,I have typed a ten-digit number, e.g., 1234567890
(no spaces, no negative/minus sign, no parenthesis, by use of the number
keypad (NumLk is enabled).
Yes, I want the "1" prefix, the area code to appear within parenthesis,
and a negative/minus sign between the 3rd digit and the 4th digit. A
space follows the prefix "1" and a space follows the closing parenthesis.
I have tried to set a user defined number-format, but am getting strange
results with apostrophes and/or double quote markings mixed in when
viewing the Format Cells dialogue, e.g., (#") "###"*-*"####. And, I
don;t understand why that is. I was hoping to find a pre-defined/default
format for use when entering USA telephone numbers. I am flexible; I can
probably do without the "1" prefix and/or the parenthesis.
Should I be formatting these cells as "text" (left-justified) rather
than as numbers (right-justified)?
Regards,
VinceB.
Re: Formatting for USA Telephone Numbers
Posted by Brian Barker <b....@btinternet.com.INVALID>.
At 10:17 24/02/2022 -0600, Steven Ahlers wrote:
>It does drop a leading zero in the area code. It doesn't drop
>leading zeros from the prefix series or the exchange series. In the
>USA no area code starts with a zero so this would be a nonissue.
Ho, ho: indeed so!
But look what happens if the user types a string of zeroes - perhaps
to represent that no phone number is provided. In any case, it is odd
to choose to use the character (hash) that specifically drops leading
zeroes instead of the obvious one (zero) that faithfully reproduces
the input digits when one knows that dropping zeroes will never be needed.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: Formatting for USA Telephone Numbers
Posted by Steven Ahlers <sa...@yahoo.com.INVALID>.
Brian,
It does drop a leading zero in the area code. It doesn’t drop leading zeros from the prefix series or the exchange series. In the USA no area code starts with a zero so this would be a nonissue.
Steve
Sent from my iPhone
> On Feb 24, 2022, at 9:44 AM, Brian Barker <b....@btinternet.com.invalid> wrote:
>
> At 09:18 24/02/2022 -0600, Steven Ahlers wrote:
>> If you want the format of 1 (123) 456-7890 simply type in the format definition box (near the bottom of the pop up menu for Format Cell under the Numbers tab) 1^(###)^###-#### (where ^ is a space).
>
> But that format will (inappropriately) lose leading zeroes in any of the three groups - which can certainly occur in the final group.
>
> Brian Barker
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
> For additional commands, e-mail: users-help@openoffice.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: Formatting for USA Telephone Numbers
Posted by Brian Barker <b....@btinternet.com.INVALID>.
At 09:18 24/02/2022 -0600, Steven Ahlers wrote:
>If you want the format of 1 (123) 456-7890 simply type in the format
>definition box (near the bottom of the pop up menu for Format Cell
>under the Numbers tab) 1^(###)^###-#### (where ^ is a space).
But that format will (inappropriately) lose leading zeroes in any of
the three groups - which can certainly occur in the final group.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: Formatting for USA Telephone Numbers
Posted by Steven Ahlers <sa...@yahoo.com.INVALID>.
Vince,
If you want the format of 1 (123) 456-7890 simply type in the format definition box (near the bottom of the pop up menu for Format Cell under the Numbers tab) 1^(###)^###-#### (where ^ is a space). There is no need for quotation marks. You can put any text you want when you are defining the format of a number.
Make sure the width of the column is wide enough for all of the characters otherwise you’ll get the error code ### which signifies a number too wide to display.
Steve
Sent from my iPhone
> On Feb 23, 2022, at 10:14 PM, PCS <pe...@bigpond.com> wrote:
>
>
> Setting the cell format to “Text” and entering the number exactly as you want it to look should do the trick, though the use of brackets (and maybe some other characters?) will give an invalid formula correction warning to which you will have to respond “No”. The resultant text does not have to be left justified, you can select the justification you want in the top menu bar, as normal. However the number entered as text will be text, you will not be able to do any numerical operations with it.
>
> You can also enter a number as text without setting the cell format to text by typing ="123 456” (with your number of course, not 123 446)
>
> PCS
>
>
>> On 24 Feb 2022, at 12:33 pm, Vince@Verizon <wa...@verizon.net.INVALID> wrote:
>>
>> Hi:
>>
>> How do I format cells for entry of USA Telephone Numbers, as: 1 (###) ###*-*####
>>
>> whenever, for example,I have typed a ten-digit number, e.g., 1234567890 (no spaces, no negative/minus sign, no parenthesis, by use of the number keypad (NumLk is enabled).
>>
>> Yes, I want the "1" prefix, the area code to appear within parenthesis, and a negative/minus sign between the 3rd digit and the 4th digit. A space follows the prefix "1" and a space follows the closing parenthesis.
>>
>> I have tried to set a user defined number-format, but am getting strange results with apostrophes and/or double quote markings mixed in when viewing the Format Cells dialogue, e.g., (#") "###"*-*"####. And, I don;t understand why that is. I was hoping to find a pre-defined/default format for use when entering USA telephone numbers. I am flexible; I can probably do without the "1" prefix and/or the parenthesis.
>>
>> Should I be formatting these cells as "text" (left-justified) rather than as numbers (right-justified)?
>>
>> Regards,
>>
>> VinceB.
>>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: Formatting for USA Telephone Numbers
Posted by PCS <pe...@bigpond.com>.
Setting the cell format to “Text” and entering the number exactly as you want it to look should do the trick, though the use of brackets (and maybe some other characters?) will give an invalid formula correction warning to which you will have to respond “No”. The resultant text does not have to be left justified, you can select the justification you want in the top menu bar, as normal. However the number entered as text will be text, you will not be able to do any numerical operations with it.
You can also enter a number as text without setting the cell format to text by typing ="123 456” (with your number of course, not 123 446)
PCS
> On 24 Feb 2022, at 12:33 pm, Vince@Verizon <wa...@verizon.net.INVALID> wrote:
>
> Hi:
>
> How do I format cells for entry of USA Telephone Numbers, as: 1 (###) ###*-*####
>
> whenever, for example,I have typed a ten-digit number, e.g., 1234567890 (no spaces, no negative/minus sign, no parenthesis, by use of the number keypad (NumLk is enabled).
>
> Yes, I want the "1" prefix, the area code to appear within parenthesis, and a negative/minus sign between the 3rd digit and the 4th digit. A space follows the prefix "1" and a space follows the closing parenthesis.
>
> I have tried to set a user defined number-format, but am getting strange results with apostrophes and/or double quote markings mixed in when viewing the Format Cells dialogue, e.g., (#") "###"*-*"####. And, I don;t understand why that is. I was hoping to find a pre-defined/default format for use when entering USA telephone numbers. I am flexible; I can probably do without the "1" prefix and/or the parenthesis.
>
> Should I be formatting these cells as "text" (left-justified) rather than as numbers (right-justified)?
>
> Regards,
>
> VinceB.
>
Re: Formatting for USA Telephone Numbers
Posted by Steven Ahlers <sa...@yahoo.com.INVALID>.
Jonathan,
Actually Vince’s (the OP) original question was about 1+ (###) ###-#### not +1 (###) ###-####.
He also asked about (###) ###-#### as an alternative. Both were addressed several responses ago including an incorrection in regards to those solutions.
Steve
Sent from my iPhone
> On Feb 26, 2022, at 9:39 PM, toki <to...@gmail.com> wrote:
>
>
>> On 25/02/2022 19:58, Alan B wrote:
>> Correction
>> After applying the recommended format, (###) ###-####, and entering
>> 1234567890 the displayed value will be (123) 456-7890.
>
> Your original question was asking about +1 (###) ###-####, not (###) ###-####.
>
> jonathon
Re: Formatting for USA Telephone Numbers
Posted by toki <to...@gmail.com>.
On 25/02/2022 19:58, Alan B wrote:
> Correction
> After applying the recommended format, (###) ###-####, and entering
> 1234567890 the displayed value will be (123) 456-7890.
Your original question was asking about +1 (###) ###-####, not (###)
###-####.
jonathon
Re: Formatting for USA Telephone Numbers
Posted by Alan B <ab...@gmail.com>.
Correction
After applying the recommended format, (###) ###-####, and entering
1234567890 the displayed value will be (123) 456-7890.
No idea how/why I typed what's in my original answer.
My apology for any confusion I may have caused.
On Fri, Feb 25, 2022, 8:20 AM Alan B <ab...@gmail.com> wrote:
> To format a ten digit number as (555) 555-1212 use the following format
> code
>
> (###) ###-####
>
> Enter the ten digits into the cell, e.g. 1234567890, and the number will
> be displayed as (120) 456-7890
> NOTE: area code must not start with zero. Zero in any other position will
> maintain the format. There is a space between the right paren ) and the
> next #.
>
> To enter only ten digits and have the US dialing prefix, +1 displayed use
> the following format code
>
> "+1" (###) ###-####
>
> Enter the ten digits into the cell, e.g. 1234567890, and the number will
> be displayed as +1 (123) 456-7890
> NOTE: there must be a space between the second quote " and left paren (.
> If there is no space then the formatted result will be +1(123) 456-7890
>
> On Wed, Feb 23, 2022 at 9:33 PM Vince@Verizon
> <wa...@verizon.net.invalid> wrote:
>
>> Hi:
>>
>> How do I format cells for entry of USA Telephone Numbers, as: 1
>> (###) ###*-*####
>>
>> whenever, for example,I have typed a ten-digit number, e.g., 1234567890
>> (no spaces, no negative/minus sign, no parenthesis, by use of the number
>> keypad (NumLk is enabled).
>>
>> Yes, I want the "1" prefix, the area code to appear within parenthesis,
>> and a negative/minus sign between the 3rd digit and the 4th digit. A
>> space follows the prefix "1" and a space follows the closing parenthesis.
>>
>> I have tried to set a user defined number-format, but am getting strange
>> results with apostrophes and/or double quote markings mixed in when
>> viewing the Format Cells dialogue, e.g., (#") "###"*-*"####. And, I
>> don;t understand why that is. I was hoping to find a pre-defined/default
>> format for use when entering USA telephone numbers. I am flexible; I can
>> probably do without the "1" prefix and/or the parenthesis.
>>
>> Should I be formatting these cells as "text" (left-justified) rather
>> than as numbers (right-justified)?
>>
>> Regards,
>>
>> VinceB.
>>
>>
>
> --
> Alan Boba
> CISSP, CCENT, ITIL v3 Foundations 2011
>
>
Re: Formatting for USA Telephone Numbers
Posted by Alan B <ab...@gmail.com>.
To format a ten digit number as (555) 555-1212 use the following format code
(###) ###-####
Enter the ten digits into the cell, e.g. 1234567890, and the number will be
displayed as (120) 456-7890
NOTE: area code must not start with zero. Zero in any other position will
maintain the format. There is a space between the right paren ) and the
next #.
To enter only ten digits and have the US dialing prefix, +1 displayed use
the following format code
"+1" (###) ###-####
Enter the ten digits into the cell, e.g. 1234567890, and the number will be
displayed as +1 (123) 456-7890
NOTE: there must be a space between the second quote " and left paren (. If
there is no space then the formatted result will be +1(123) 456-7890
On Wed, Feb 23, 2022 at 9:33 PM Vince@Verizon <wa...@verizon.net.invalid>
wrote:
> Hi:
>
> How do I format cells for entry of USA Telephone Numbers, as: 1
> (###) ###*-*####
>
> whenever, for example,I have typed a ten-digit number, e.g., 1234567890
> (no spaces, no negative/minus sign, no parenthesis, by use of the number
> keypad (NumLk is enabled).
>
> Yes, I want the "1" prefix, the area code to appear within parenthesis,
> and a negative/minus sign between the 3rd digit and the 4th digit. A
> space follows the prefix "1" and a space follows the closing parenthesis.
>
> I have tried to set a user defined number-format, but am getting strange
> results with apostrophes and/or double quote markings mixed in when
> viewing the Format Cells dialogue, e.g., (#") "###"*-*"####. And, I
> don;t understand why that is. I was hoping to find a pre-defined/default
> format for use when entering USA telephone numbers. I am flexible; I can
> probably do without the "1" prefix and/or the parenthesis.
>
> Should I be formatting these cells as "text" (left-justified) rather
> than as numbers (right-justified)?
>
> Regards,
>
> VinceB.
>
>
--
Alan Boba
CISSP, CCENT, ITIL v3 Foundations 2011
Re: Formatting for USA Telephone Numbers
Posted by Brian Barker <b....@btinternet.com.INVALID>.
At 21:33 23/02/2022 -0500, Vince Bonly wrote:
>How do I format cells for entry of USA Telephone Numbers, as: 1
>(###) ###*-*####
I don't understand those asterisks; I'm assuming you don't mean them.
>Yes, I want the "1" prefix, the area code to appear within
>parenthesis, and a negative/minus sign between the 3rd digit and the
>4th digit. A space follows the prefix "1" and a space follows the
>closing parenthesis.
What's wrong with "1 (000) 000-0000" (no quotes, of course)?
>Should I be formatting these cells as "text" (left-justified) rather
>than as numbers (right-justified)?
You don't have control over this if you set a user-defined format in
this way - and OpenOffice will think they are numbers. But telephone
numbers are definitely really text, not numeric values. If you don't
believe this, ask yourself the significance of someone else having
exactly twice your phone number - or of your number being even and
your friend's odd. You can set alignment independently of this anyway.
If you really wanted those peculiar asterisks, you would have to
escape each of them with a backslash, as \*
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: Formatting for USA Telephone Numbers
Posted by Steven Ahlers <sa...@yahoo.com.INVALID>.
Keith,
The formatting that I shared works fine for USA phone numbers. I’ve used it for many years for tracking a list of contact information of a fantasy football league.
Steve
Sent from my iPhone
> On Feb 24, 2022, at 6:53 PM, Keith N. McKenna <ke...@comcast.net> wrote:
>
> In article <64a75984-4f03-422e-7ccb-0358112cb1f6
> @verizon.net>, wa.tworsx@verizon.net.INVALID says...
>>
>> Hi:
>>
>> How do I format cells for entry of USA Telephone Numbers, as: Â Â Â 1
>> (###) ###*-*####
>>
>> whenever, for example,I have typed a ten-digit number, e.g., 1234567890
>> (no spaces, no negative/minus sign, no parenthesis, by use of the number
>> keypad (NumLk is enabled).
>>
>> Yes, I want the "1" prefix, the area code to appear within parenthesis,
>> and a negative/minus sign between the 3rd digit and the 4th digit. A
>> space follows the prefix "1" and a space follows the closing parenthesis.
>>
>> I have tried to set a user defined number-format, but am getting strange
>> results with apostrophes and/or double quote markings mixed in when
>> viewing the Format Cells dialogue, e.g., (#") "###"*-*"####. And, I
>> don;t understand why that is. I was hoping to find a pre-defined/default
>> format for use when entering USA telephone numbers. I am flexible; I can
>> probably do without the "1" prefix and/or the parenthesis.
>>
>> Should I be formatting these cells as "text" (left-justified) rather
>> than as numbers (right-justified)?
>>
>> Regards,
>>
>> VinceB.
>
> Format the cell as text and enter the characters as you
> want them to be displayed. trying to enter it as a number
> format will not get you to where you want. As Brian
> stated, Entering the way you want are not numeric values,
> but text.
>
> Keith N. McKenna
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
> For additional commands, e-mail: users-help@openoffice.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: Formatting for USA Telephone Numbers
Posted by Brian Barker <b....@btinternet.com.INVALID>.
At 19:53 24/02/2022 -0500, Keith N. McKenna wrote:
>Format the cell as text and enter the characters as you want them to
>be displayed.
But that doesn't address the problem I understood the questioner to
be posing: he wants, I think, to be able to enter just the ten digits
and have the punctuation appear as if by magic. A suitable cell
format does that - and entering data that way is less prone to error
in the formatting. Isn't that what cell formatting is all about?
>As Brian stated, Entering the way you want are not numeric values, but text.
An alternative way that creates text values would be to enter the
plain ten-digit values into one column and to create a suitable
formula in another column to extract the appropriate parts and add
the punctuation. The unwanted entry column could then be hidden or
have the reformatted values pasted over it, or it could have been
placed elsewhere, perhaps even on another sheet.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org
Re: Formatting for USA Telephone Numbers
Posted by "Keith N. McKenna" <ke...@comcast.net>.
In article <64a75984-4f03-422e-7ccb-0358112cb1f6
@verizon.net>, wa.tworsx@verizon.net.INVALID says...
>
> Hi:
>
> How do I format cells for entry of USA Telephone Numbers, as: Â Â Â 1
> (###) ###*-*####
>
> whenever, for example,I have typed a ten-digit number, e.g., 1234567890
> (no spaces, no negative/minus sign, no parenthesis, by use of the number
> keypad (NumLk is enabled).
>
> Yes, I want the "1" prefix, the area code to appear within parenthesis,
> and a negative/minus sign between the 3rd digit and the 4th digit. A
> space follows the prefix "1" and a space follows the closing parenthesis.
>
> I have tried to set a user defined number-format, but am getting strange
> results with apostrophes and/or double quote markings mixed in when
> viewing the Format Cells dialogue, e.g., (#") "###"*-*"####. And, I
> don;t understand why that is. I was hoping to find a pre-defined/default
> format for use when entering USA telephone numbers. I am flexible; I can
> probably do without the "1" prefix and/or the parenthesis.
>
> Should I be formatting these cells as "text" (left-justified) rather
> than as numbers (right-justified)?
>
> Regards,
>
> VinceB.
Format the cell as text and enter the characters as you
want them to be displayed. trying to enter it as a number
format will not get you to where you want. As Brian
stated, Entering the way you want are not numeric values,
but text.
Keith N. McKenna
---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@openoffice.apache.org
For additional commands, e-mail: users-help@openoffice.apache.org