NewDeal Hot Tip 1307

[Hot Tips for...] NewFile

Telephone Numbers in NewFile and NewCalc

Problem

I'm importing phone listings into NewCalc and then into NewFile, where I'm having fun with the design options. The phone numbers are 10 digit numbers without any commas or any other dividers. I have been trying to split the 10 digit number up into a typical phone number format. I've been trying to use the custom number format, but that doesn't seem to work. Have you any advice?

Solution

One way would be to use the LEFT(), MID(), and RIGHT() functions in a calculated cell to split up the phone numbers for example: 
="(" & LEFT(B2, 3) & ") " & MID(B2, 3, 3) & "-" & RIGHT(B2, 4)
If the original numbers are in numeric fields, then you'd have to use "STRING(number,0)" in place of each instance of a number above, like this: 
="(" & LEFT(STRING(B1, 0), 3) & ") " & MID(STRING(B1, 0), 4, 3) & "-" & RIGHT(STRING(B1, 0), 4)
This will take the number 1234567890 and make it look like (123) 456-7890.
Return to Index

Last Modified 2 Mar 1999