[Solved] Calc cell phone numbers

Discuss the spreadsheet application

[Solved] Calc cell phone numbers

Postby wallroad » Thu Mar 06, 2014 10:34 pm

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?
Last edited by Hagar Delest on Sat Oct 18, 2014 6:04 pm, edited 1 time in total.
Reason: tagged solved
OpenOffice 4.0 on Windows 7 professional
wallroad
 
Posts: 4
Joined: Thu Mar 06, 2014 10:27 pm

Re: calc cell phone numbers

Postby Villeroy » Thu Mar 06, 2014 10:54 pm

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".
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26983
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: calc cell phone numbers

Postby RusselB » Thu Mar 06, 2014 10:55 pm

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
OpenOffice 4.1.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5301
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Calc cell phone numbers

Postby wallroad » Mon Mar 10, 2014 3:41 pm

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.
OpenOffice 4.0 on Windows 7 professional
wallroad
 
Posts: 4
Joined: Thu Mar 06, 2014 10:27 pm

Re: Calc cell phone numbers

Postby RusselB » Mon Mar 10, 2014 3:48 pm

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   Expand viewCollapse view
(###) ###-####
OpenOffice 4.1.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5301
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Calc cell phone numbers

Postby Villeroy » Mon Mar 10, 2014 5:00 pm

phonenum.ods
Number format (000)000-0000 vs (###)###-####
(9.37 KiB) Downloaded 226 times

phonenum.odb
Input form with pattern control for text (000)000-0000
(11.2 KiB) Downloaded 82 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26983
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calc cell phone numbers

Postby wallroad » Tue Mar 11, 2014 2:47 am

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.
OpenOffice 4.0 on Windows 7 professional
wallroad
 
Posts: 4
Joined: Thu Mar 06, 2014 10:27 pm

Re: Calc cell phone numbers

Postby mgroenescheij » Tue Mar 11, 2014 3:01 am

That format is only valid in the US
AOO 4.1.5 on MS Windows 10 Professional & MacOS High Sierra 10.13.5
Please add '[Solved]' at beginning of your first post title (edit button) if your issue has been fixed.
mgroenescheij
Volunteer
 
Posts: 300
Joined: Thu Apr 23, 2009 10:19 pm
Location: Sydney Australia

Re: Calc cell phone numbers

Postby BadBoyBill » Tue Oct 14, 2014 7:24 am

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   Expand viewCollapse view
(###) ###-####

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?
OpenOffice 4 on MacOS 10.9.5
BadBoyBill
 
Posts: 4
Joined: Tue Oct 14, 2014 7:19 am

Re: Calc cell phone numbers

Postby RusselB » Tue Oct 14, 2014 7:54 am

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.
OpenOffice 4.1.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5301
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Calc cell phone numbers

Postby acknak » Tue Oct 14, 2014 8:45 am

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.
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Calc cell phone numbers

Postby BadBoyBill » Tue Oct 14, 2014 5:53 pm

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!!!
OpenOffice 4 on MacOS 10.9.5
BadBoyBill
 
Posts: 4
Joined: Tue Oct 14, 2014 7:19 am

Re: [Solved] Calc cell phone numbers

Postby AxterMaisonave » Tue Apr 23, 2019 1:12 pm

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
OpenOffice 3.1
AxterMaisonave
 
Posts: 1
Joined: Tue Apr 23, 2019 12:54 pm

Re: [Solved] Calc cell phone numbers

Postby auntiebk » Thu Jul 11, 2019 7:26 pm

Thank you RusselB for cell input format for phone numbers. Much appreciated.
Open Office 4.1.2 on Windows 7 build 7601 SP1
auntiebk
 
Posts: 1
Joined: Thu Jul 11, 2019 7:20 pm


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 17 guests