how do I create a telephone number format in calc

[merlinof2]

I am creating a database for printing labels. I am inputting telephone numbers and need them to format as telephone numbers

[2 hours later]

In Calc, I am scanning in 10 character strings to a cell in a column. I would like to be able to apply a “telephone number” format to each cell in the column, ie’ (XXX) XXX-XXXX. I need the format in order to print out a list of labels with the telephone numbers correctly displayed on the label. I’ve tried a number of combinations under the “user format” but have not yet hit the right one. Does anyone have a solution? Thanks in Advance for your Time.

[EarnestAl]

You can retain leading zeroes by selecting the column and clicking Format > Cells, selecting the Numbers tab in the dialog and clicking Text then OK.

If you are importing from a csv or other text document then make sure you define the telephone number column as Text in the import text dialog. Rule of thumb: if you could sensibly perform a mathematical operation on a “number” then format it as a number; if it makes no sense to perform a mathematical operation, then format it as text.

If you mean something more complex, e.g. [00123] 123 456, then I will leave it to someone more expert after you provide more detail on the format.

[EarnestAl]

To add to your question, click the edit link just below the question.

[EarnestAl]

Assuming the number is in B2 then in C3 you could add ="("&left(B2;3)&") "&mid(B2;4;3)&"-"&right(B2;4). It is not formatting however, just concatenation.

Normally, the prefix is a separate field (column) because sometimes the prefix changes and if it is a separate field then it is easy to bulk change.

[sokol92]

Try (000)\ 000-0000

[merlinof2]

Most Excellent. Thank You. That solved the issue. Many Thanks again…

[EarnestAl]

@sokol92 do you want to put that as an answer? Cheers, Al

[sokol92]

Thank you, Earnest Al, I think this is no longer necessary.