Phone number format

tonydeimone1942

New Member
Joined
Sep 11, 2014
Messages
14
How do I move this value in phone number format xxx-xxx-xxxx

ActiveCell.Offset(0, 5).Value = txtPhone.Value
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

BiocideJ

Well-known Member
Joined
Jan 23, 2012
Messages
1,733
Welcome to the board!
You could do it like this:

Code:
    If txtPhone.Value < 9999999 Then
        ActiveCell.Offset(0, 5).Value = Format(txtPhone.Value, "###-####")
    Else
        ActiveCell.Offset(0, 5).Value = Format(txtPhone.Value, "(###) ###-####")
    End if

Note that this method will convert a number into a string.

If you wish to keep the value as a number and simply FORMAT it as a Phone Number you could use:
Code:
ActiveCell.Offset(0, 5).Value = txtPhone.Value
ActiveCell.Offset(0, 5).NumberFormat = "[<=9999999]###-####;(###) ###-####"

Note that the second option will change cell formatting in the active sheet.

Use whichever method is preferable to you.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,043
Messages
5,526,423
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top