[Solved] Phone number formatting

[Jokersloose]

Hello all,

I am putting together a spread sheet (Open Office Cal.) with phone numbers. I am looking for a way to make them all look the same. Right now they look like
(XXX) XXX-XXXX
XXX-XXX-XXXX
XXX.XXX.XXXX

I would like to make them all look like (XXX) XXX-XXXX.

I did find this tread: https://forum.openoffice.org/en/forum/v ... one+number

But when I put either in the "Numbers" "Format Code" neither code worked. I highlight the row when I do this.

Am I putting it in the wrong spot? Wrong code?

TIA

James

[MrProgrammer]

I am putting together a spread sheet (Open Office Cal.) with phone numbers

Numbers are used for arithmetic, and more generally, for mathematics. You are not going to be doing any math with these values. You won't multiply them, or take square roots, or find their average. You do not have numbers. You have codes. Codes should always be stored in Calc cells as text: Format → Cells → Numbers → Category → Text → OK (and preferably the equivalent operation with a style).
Read sections 1. Types of data and 5. Understanding data entry in Ten concepts that every Calc user should know.

(XXX) XXX-XXXX
XXX-XXX-XXXX
XXX.XXX.XXXX
I would like to make them all look like (XXX) XXX-XXXX.

Step A: Mark all those cells as text
Step B: Select the cells, Edit → Find & Replace → More options, select Current selection only and Regular Expressions, Search for [^0-9], Leave the Replace with field empty, Replace All. This removes any punctuation. You are left with 10-character text cells. Now we will use a regular expression to add punctuation.
Step C: Select the cells, Edit → Find & Replace → More options, select Current selection only and Regular expressions, Search for (...)(...)(....), Replace with ($1) $2-$3, Replace All. I recommend unchecking Current selection only and Regular expressions, before clicking Close. This adds desired punctuation.

The following attachment shows the process. All the cells in yellow are formatted as text. Column A shows the original values. Column B shows the values after Step B was performed. Column C shows the values after Step C was performed.

202209261415.ods

(11.54 KiB) Downloaded 58 times

If you need any additional assistance attach a document demonstrating the difficulty (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself).

But when I put either in the "Numbers" "Format Code" neither code worked.

Calc does not recognize values like 123-456-7890 as numbers. Number format codes only apply to numbers.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.