Page 1 of 1

[Solved] Calc cell phone numbers

Posted: Thu Mar 06, 2014 10:34 pm
by wallroad
I thought I had set the cell format correctly for phone numbers, doing User Defined and (000) 000-0000. But after I type in the number and leave the cell, it shows only ###. When I return to the cell and press F2, the number I typed in shows up--straight numbers without the (), space, or dash. How can I have the formatted number appear in the cell?

Re: calc cell phone numbers

Posted: Thu Mar 06, 2014 10:54 pm
by Villeroy
Cell phone numbers are no numbers at all. Enter them as text. Apply number format "@" of category "Text" to the cells in question before you enter those "numbers".

Re: calc cell phone numbers

Posted: Thu Mar 06, 2014 10:55 pm
by RusselB
Sounds like your column isn't wide enough.
You can put the mouse on the row with the column letters, positioning it between the column you want to resize and the one to the right.
With the mouse in this position, press & hold the left mouse button, then drag the mouse to the right.

Alternatively, you can highlight the column, then right click in the column and select Optimum Column Width

Re: Calc cell phone numbers

Posted: Mon Mar 10, 2014 3:41 pm
by wallroad
Thanks for both of your replies, but neither helps me. Let me restate: I want a row that consists of phone numbers. I want to type in the numbers like this: 6666661234. Then I want the program to format it for me so that they appear on the spreadsheet like this: (666) 666-1234. That is, I want OpenOffice to insert the parentheses, the spaces, and the dash. Maybe that is not possible with OpenOffice.

Re: Calc cell phone numbers

Posted: Mon Mar 10, 2014 3:48 pm
by RusselB
Highlight the cell(s)/row(s)/column(s) that you want formatted in that manner.
Right click and select Format Cells
Under the Numbers tab, select User-Defined and enter

Code: Select all

(###) ###-####

Re: Calc cell phone numbers

Posted: Mon Mar 10, 2014 5:00 pm
by Villeroy
phonenum.ods
Number format (000)000-0000 vs (###)###-####
(9.37 KiB) Downloaded 408 times
phonenum.odb
Input form with pattern control for text (000)000-0000
(11.2 KiB) Downloaded 313 times

Re: Calc cell phone numbers

Posted: Tue Mar 11, 2014 2:47 am
by wallroad
Russell! YES! Thank you very much! It would have been nice if OpenOffice would have simply provided the phone format as an option in the drop-down list.

Re: Calc cell phone numbers

Posted: Tue Mar 11, 2014 3:01 am
by mgroenescheij
That format is only valid in the US

Re: Calc cell phone numbers

Posted: Tue Oct 14, 2014 7:24 am
by BadBoyBill
RusselB wrote:Highlight the cell(s)/row(s)/column(s) that you want formatted in that manner.
Right click and select Format Cells
Under the Numbers tab, select User-Defined and enter

Code: Select all

(###) ###-####
I have a huge data base where people entered their phone numbers and there were no parameters so some are 123-456-7890, some are (123) 456-7890, some are 123 456 7890 and some are all various combinations including 123/456-7890. Is there any way to change them all to (123) 456-7890?

Re: Calc cell phone numbers

Posted: Tue Oct 14, 2014 7:54 am
by RusselB
Not easily, as you have a lot of different scenarios presented, and I don't know if all of the possibilities are mentioned in your post.
In theory, you're best bet is to go along the length of the entry, and copy each digit found to another cell, with that cell already formatted as you want.
I can't think of a way to do this in Calc.

Re: Calc cell phone numbers

Posted: Tue Oct 14, 2014 8:45 am
by acknak
Select the cells (or column) you want to change

Edit > Find & Replace
Search for: ^\D*(\d{3})\D+(\d{3})\D+(\d{4})\D*$
Replace with: ($1) $2-$3
Match case: YES
Options/Regular expressions: ON
Options/Current selection only: ON

Click "Replace All"

This works with all your examples; it should cover most other cases as well.

Re: Calc cell phone numbers

Posted: Tue Oct 14, 2014 5:53 pm
by BadBoyBill
acknak wrote:Select the cells (or column) you want to change

Edit > Find & Replace
Search for: ^\D*(\d{3})\D+(\d{3})\D+(\d{4})\D*$
Replace with: ($1) $2-$3
Match case: YES
Options/Regular expressions: ON
Options/Current selection only: ON

Click "Replace All"

This works with all your examples; it should cover most other cases as well.
Thank-you!!!

Re: [Solved] Calc cell phone numbers

Posted: Tue Apr 23, 2019 1:12 pm
by AxterMaisonave
For find and replace, I recommend using a more improved regex string.
The below version will catch and find more phone formats, to include whole number (1234567890) and dotted format (123.456.7890).
Edit > Find & Replace
Search for: \(*(\d{3})\)*\s*\-*(\d{3})\s*\-*(\d{4})
Replace with: ($1) $2-$3

Here are some additional example replacement formats:
Replace with: $1-$2-$3
Replace with: $1.$2.$3
Replace with: ($1)$2-$3

Re: [Solved] Calc cell phone numbers

Posted: Thu Jul 11, 2019 7:26 pm
by auntiebk
Thank you RusselB for cell input format for phone numbers. Much appreciated.

Re: [Solved] Calc cell phone numbers

Posted: Wed Nov 20, 2019 10:44 pm
by appletreasures
Here is something similar.
I wanted Phone Numbers entered as xxxxxxxxxx to to be formatted and appear as xxx-xxx-xxx (ie 1234567890 to appear as 123-456-7890).
I did the following:
Highlight column/row/cell
Format>cells>number>Format code = xxx-xxx-xx

Re: [Solved] Calc cell phone numbers

Posted: Wed Nov 20, 2019 11:00 pm
by Zizi64
Note:
If you handle the telephone numbers as formatted numeric values (instead of handling them as strings), you will not able to search the parts of the telephone numbers easily. You will make more jobs for yourself...

Re: [Solved] Calc cell phone numbers

Posted: Thu Nov 21, 2019 9:08 pm
by jrkrideau
I tend to live in fear that some spreadsheet calculating a drug dose for me will use my telephone number rather than my weight.