Custom Number in Phone Number Format

[muhammad susanto]

hi all...

how to make in custom number format phone number like this (insert mark "-)
e.g

0812-5265-9999
0817-6325-8743
0878-6986-410

thanks in advance..

m.susanto

 

[AlanY]

you mean like this?

Excel 2013/2016
	A	B
1	012345678901	0123-4567-8901
2	01234567890	0123-4567-890
Sheet4

Cell Formulas
Range	Formula
B1	=LEFT(A1,4)&"-"&MID(A1,5,4)&"-"&MID(A1,9,4)

 

[Scott Huish]

If those are entered as numbers without the leading 0, that won't work for the 3rd sample.

Perhaps:
=REPLACE(REPLACE(TEXT(A1,REPT(0,LEN(A1)+1)),5,0,"-"),10,0,"-")

Excel 2010
	A	B
1	81252659999	0812-5265-9999
2	81763258743	0817-6325-8743
3	8786986410	0878-6986-410
Sheet1

Cell Formulas
Range	Formula
B1	=REPLACE(REPLACE(TEXT(A1,REPT(0,LEN(A1)+1)),5,0,"-"),10,0,"-")
B2	=REPLACE(REPLACE(TEXT(A2,REPT(0,LEN(A2)+1)),5,0,"-"),10,0,"-")
B3	=REPLACE(REPLACE(TEXT(A3,REPT(0,LEN(A3)+1)),5,0,"-"),10,0,"-")

 

[jtakw]

Hi,

If you mean Custom Format of the Cell itself.

Select your cell or range of cells you want formatted, right click, Format Cells, Custom, enter 0000-0000-0000

Now when you enter numbers in those cells, the "dash" will be inserted every 4th digits, if the number you entered is less than 12 digits, it will be preceded by leading zeros, keep in mind, this is formatting, it does Not change the Actual value you entered into the cell, Only the appearance (display).

 

[Scott Huish]

For a custom number format, perhaps:

[<10000000000]0000-0000-000;0000-0000-0000