Hi there,
Is there any way with Calc to create a converter that would allow me to quickly convert any phone number format to 000-000-0000, no matter the format used?
Examples:
1112223333 would become 111-222-3333
444.555.6666 would become 444-555-6666
(777) 888-9999 would become 777-888-9999
555 2226666 would become 555-222-6666
222-4448888 would become 222-444-8888
Is it possible to create this converter using Calc?
Thank you,
David Hayes
You obviously live on the only planet in the universe where all the phone numbers have exactly 10 decimal digits (and not at all leading zeros?).
On earth there is no reasonable format for phone numbers except the plain sequence of digits gioven as text.
See the attachment linked into my answer for more detailed explanation.
Take the problem the other way round. Record your phone number as simple integer numbers, e.g. 1112223333, and use a custom format to display it the way you want.
In your case (obviously, North American phone numbers), define the format as 000-000-0000 or (000)-000-0000. You can also define several format if the subscribers live in areas where the way to write numbers are different or if you need to record the international country code, as in +(34) 987 654 321.
The underlying data remains a simple number and you separate display from value.
To show the community your question has been answered, click the ✓ next to the correct answer, and “upvote” by clicking on the ^ arrow of any helpful answers. These are the mechanisms for communicating the quality of the Q&A on this site. Thanks!
Hello,
if you need to convert existing numbers, you may use the following formulas (assuming your number is in cell A1):
To Text: =TEXT(VALUE(REGEX(A1;"[().-]|\s";"";"g"));"000-000-0000")
To Values (for use with @ajlittoz’ answer): =VALUE(REGEX(A2,"[().-]|\s","","g"))
See the following sample file:
Hope that helps.
If the answer is correct or helped you to solve your problem, please click the check mark (
) next to the answer.
The format code 000-000-0000 displays leading zeros for “numbers” with less than 10 digits.
Yes but phone numbers are supposed to have a fixed number of digits and leading zeroes are significant. If number of digits are different for various subscribers (living in different countries), I suggest creating several formatting codes.
Yes but phone numbers are supposed to have a fixed number of digits
In your country maybe…
Assuming data in A1:
=REGEX(REGEX(A1;"[^0-9]";"";"g");"(...)(....)$";"-$1-$2")
No hyphen-minus is inserted if there are less than 7 digits.
WOW Thank you so much!
With exactly 7 digits it ends up with a leading minus though…
Separating phone numbers in parts based on counting digits is mostly counterfunctional. The only “global” spec I know says that the “international” prefix starts with two zeros. However, there may be local (or whatever) authorities not even implementing this convention.
For a reasonable solution and some additional comments see this attachmed document.