Phone Number

[supenguin]

Hello all,

After entering ten digits into a cell and hitting 'Enter', focus moves to another cell.

Is there a way to manipulate the cell so the numbers become a ten-digit telephone number before the focus moves to another cell?

Thanks

-Pengu

[Hagar Delest]

I don't think it's possible before (I've corrected your post, in such case, just hit the edit button top right of the post header to modify it when it's a short time after you've posted).

Why do you need that?

NB: moved the thread to the main Calc forum, it's not specific to formulas.

[littlegeniuz]

You can make a new (User defined) Cell format, which has the format of a telephone number and format all cells, in whitch you wan't to have phone numbers with that format. But OOo then doesn't check the length of your number, so you will have to watch out for that.

A sample number format for a phone number(in this format: (123) 456 7890) would be:

Code: Select all

(???) ??? ????

[magnoliasouth]

Honestly, I cannot figure out why this is not a standard number format pre-installed in OO. Even Excel offers it and who wants to use that?

[Villeroy]

Honestly, I cannot figure out why this is not a standard number format pre-installed in OO. Even Excel offers it and who wants to use that?

You are the one who wants to use that if everything has to be predefined. This is the first time somebody complains about that tiny detail. Why not use this software for what it is made for? Feel free to use your own (default-) templates with predefined styles for anything possible.

Btw: I would use text rather than numbers for phone "numbers".

[Dave]

Honestly, I cannot figure out why this is not a standard number format pre-installed in OO. Even Excel offers it and who wants to use that?

Damn. I just bit my tongue.

David.

[ccornell]

Honestly, I cannot figure out why this is not a standard number format pre-installed in OO.

I can think of a reason... phone numbers outside of North America are not all formatted the same way. My German mobile (cell) phone number for example would be formatted like this (using 1234...) +49 (0)123 4567 8912. My home phone number would be formatted like this +49 (0)12 3456 7890. A mobile phone number in Kenya would be +254 123 456 789. A landline in Nairobi Kenya would be +254 (0)20 12345 or +254 (0)20 123 456 depending on if you have a 5 or 6 digit phone number.

Number grouping for phone numbers is not really all that "standard" from one country to the next.

[Jeepster]

Littlegeniuz, THANK YOU.

I have been using the phone number format in Excel for too long to mention. I find it remarkable that anyone would think it shouldn't be a standard formatting feature in a spreadsheet software intended for widespread use. Just as there are several ways to format numbers and currency, there should be several acceptable ways to format numerals, as phone numbers from any country or as accounts.

[Dave]

I find it remarkable that anyone would think it shouldn't be a standard formatting feature in a spreadsheet software intended for widespread use.

I find it remarkable that not all vehicles have the gearshift in the same place, the same number of gears, the same number of seats, the same ability to haul large loads, ... Where's Henry Ford when you need him?

Let's give it a rest, please! If you prefer MS software, then simply use it.

David.

[Audyossey]

...I find it remarkable that anyone would think it shouldn't be a standard formatting feature in a spreadsheet software intended for widespread use.

As always, there are pros and cons. You've already heard some. I'll add mine.

Personally, I like having uniformity in my spreadsheets, especially if they're going to serve as sources for documents that will be distributed to others. The predefined styles help in achieving that.

On the other hand, I've used the phone number format in Excel and in Word at times, and always lived to regret it. The problem is, you're formatting the cell, not the number in the cell, which remains ten unformatted digits. I've received spreadsheets with formatted phone numbers from other people and had to merge them into another spreadsheet. Quite simply, the formatting doesn't necessarily survive. (123) 456-7890 becomes 1234567890, and there's no easy way to apply the format after the fact. The same problem arises with zip codes that have leading zeros using a predefined format. It's best to define the cells as text and format the content manually as you go, particularly if you'll be sharing your work with others. What the other user will actually see can be unpredictable.

One solution would be to format the range of cells as text and then write a macro to convert a sequence of ten digits to the desired format. You could assign the macro to a function key. In that case, the only difference compared to what you're doing now is that you would press a function key instead of {Enter} to exit the cell.

I do happen to have a solution on my computer in the form of an external spell checker. It's checking this message right now as I type. It can be set to recognize phone numbers and format them as you go (with a prompt to accept the change or not). As with the macro idea you get text in a cell instead of a number with phantom spaces, hyphens, and parentheses.

But, to each his own. The predefined formats are seductive, and if you're not going to be exposed to their limitations, then there's no particular reason not to use them—or in Calc's case to create the style you want. Just be careful about generalizing from your experience. To say that it's remarkable to think "it shouldn't be a standard formatting feature in a spreadsheet" is myopic for all the reasons people here have suggested. With so much worldwide distribution and sharing of data in so many common formats and applications, the complications that arise from this sort of formatting can get in the way.

HB

[Villeroy]

You can add all your favourite styles to your default template (the document that is created when you hit Ctrl+N) and you can copy styles across other templates documents. This is not even restricted to the number format of phone numbers. You may combine your personal phone number format with all kinds of fonts, colours and borders. You can do the same for dates, times, nick names, hyperlinks and currencies.

[Georgia Burrows]

I don't understand. What number do I call???? Cell Phone or regular. I just need help

[acknak]

Sorry, there is no telephone support for OpenOffice--you can use email or a forum.

[Hiakun]

highlight cells to format, Right click, click on Format Cells, select User-defined in Category, and in Format code at the bottom User defined type (#") "###" "#### it will then make all the boxes for your selection phone format. you may need to make some minor adjustments to those settings to set for the international ability.

[jrkrideau]

Honestly, I cannot figure out why this is not a standard number format pre-installed in OO. Even Excel offers it and who wants to use that?

Well, perhaps this is one case where Calc is trying to prevent stupid errors while Excel seems not to care.

Very simply, just because we call something like (123) 456-7890 or 311-234-4455 a phone number does not mean it is numerical. A phone number is an address or code.

Try this: Put (123) 456-7890 in A1 (using (??? ) ???-???? and then put (555) 234-4455 in B1 using the same formatting code. Then in C1 do = A1 + B1. Or even better, =A1/B1.
Ideally one should not be able to add (or multiply, etc) phone numbers.

My line is "I live in fear of the doctor or pharmacy using a spreadsheet and calculating a drug dose by my telephone number instead of my weight".

[RoryOF]

Honestly, I cannot figure out why this is not a standard number format pre-installed in OO. Even Excel offers it and who wants to use that?

There are other countries than USA, who use different formats.

[gerard24]

Honestly, I cannot figure out why this is not a standard number format pre-installed in OO. Even Excel offers it and who wants to use that?

There are other countries than USA, who use different formats.

Are you sure of this ?
If it's right, Microsoft is not aware of this.

[Hiakun]

Standard us ext. Is 1 then area code then number. International numbers are 45 or other ext and not one but the format is still same just add ? In front of format like I stated before "? (?) ??? ????" Should work

[keme]

Standard us ext. Is 1 then area code then number. International numbers are 45 or other ext and not one but the format is still same just add ? In front of format like I stated before "? (?) ??? ????" Should work

Yes indeed, that format code always works, as long as phone number is always 7 digits and area code always just one. With international numbers, that is not the case. Some countries do not use area codes. Some countries use 6 or 8 digits for phone number. I'd still say the best advice (already given above) is to have phone numbers entered as text, and format as you type (with the optional aid of a spell checker with number recognition).

For some purposes it may be best to split the number to 3 cells, for country code, area code and phone number, respectively.