Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Format Phone Number in VBA

  1. #1
    Join Date
    30th December 2011
    Posts
    13

    Format Phone Number in VBA

    Hello:

    I'm looking to see is if there is a way to modify this code to format a phone number as (999) 999-9999. Right now it formats phone numbers as 999-999-9999.

    Thank you for your help!


    VB:
    Sub FormatPhoneNumber() 
         'Purpose: Formats a telephone number as 999-999-9999.
         'In cases where more than one phone number is enter in
         'a cell only the first number is returned. Extensions
         'are not formatted and are truncated.
         'Developed specifically for use in the spreadsheet
         'DataCollectionForm.xlsm where phone and fax number
         'are in the columns AB and AC. Conditional execution
         'only allows processing in those cells.
         'Tested against the following formats:
         '   (999)999-9999
         '   999 999 9999
         '   9999999999
         '   (999)999-9999 Ext 165
         '   (999)999-9999 OR (999)999-9999
         'Author: Marvin R. Reinhart
         'Date Created: 06/11/2007
         'Change History:
         '
        Dim sRawNumber As String 'Phone number as it was originally typed in the cell
        Dim sJustNumber As String 'Phone number with all non-numerics stripped out
        Dim sPhoneNumber As String 'Phone number formatted as 999-999-9999
        Dim iLen As Integer 'Len of value originally typed in the cell
        Dim iCtr As Integer 'Counter for processing loops
        Dim sActiveColumn As String 'Identifies the column the cell is in when the macro was executed
         
         'Determine the active column containing the cell when the macro was executed
        sActiveColumn = Mid$(Application.ActiveWindow.ActiveCell.Address, 2, InStr(2, Application.ActiveWindow.ActiveCell.Address, "$") - 2) 
         
         'If the macro was not executed in column AB or AC then do not attempt to format the value
        If sActiveColumn <> "AB" And sActiveColumn <> "AC" Then 
            Exit Sub 'End here
        End If 
         
         'Get the current data entered in the active cell
        sRawNumber = Application.ActiveWindow.ActiveCell.Value 
         
         'Get the length of the entry and use to control looping
        iLen = Len(Trim(sRawNumber)) 
         
         'Strip out all non-numeric characters
        For iCtr = 1 To iLen 
            If IsNumeric(Mid(sRawNumber, iCtr, 1)) Then 
                sJustNumber = sJustNumber & Mid(sRawNumber, iCtr, 1) 
            End If 
        Next iCtr 
         
         'If there are less than 10 digits then issue a warning to the user
         'that they should manually edit this entry because it does not appear
         'to be a telephone number with full area code.
        If Len(Trim(sJustNumber)) < 10 Then 
            MsgBox "The value in this cell does not appear to be " & _ 
            "a full valid phone number that includes the Area Code. " & _ 
            Chr(13) & "Please edit manually.", vbApplicationModal + vbInformation + vbOKOnly, "Manual Editing Required" 
            Exit Sub 'Exit without changing cell value
        End If 
         
         'Format as 999-999-9999
        For iCtr = 1 To Len(Trim(sJustNumber)) 
             'A dash is placed in the 4th and 7th positions otherwise just append the numeric charater
            If iCtr = 4 Or iCtr = 7 Then 
                sPhoneNumber = sPhoneNumber & "-" & Mid(sJustNumber, iCtr, 1) 
            Else 
                sPhoneNumber = sPhoneNumber & Mid(sJustNumber, iCtr, 1) 
            End If 
        Next 
         
         'Set the value of the cell from where the macro was executed to the
         'first 12 positions of the formatted phone number.
        Application.ActiveWindow.ActiveCell.Value = Left$(sPhoneNumber, 12) 
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    26th September 2012
    Posts
    128

    Re: Format Phone Number in VBA

    try adding htis as your last line:

    VB:
    Selection.NumberFormat = "[<=9999999]###-####;(###) ###-####" 
    
    
    also, you need to wrap you code. put [ c o d e ] (no spaces) at the beginning of your code and [ / c o d e ] (no spaces) at the end of your code.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    30th December 2011
    Posts
    13

    Re: Format Phone Number in VBA

    I tried as suggested and still came up with the 999-999-9999.

    VB:
     
     'Format as 999-999-9999
    For iCtr = 1 To Len(Trim(sJustNumber)) 
         'A dash is placed in the 4th and 7th positions otherwise just append the numeric charater
        If iCtr = 4 Or iCtr = 7 Then 
             
            sPhoneNumber = sPhoneNumber & "-" & Mid(sJustNumber, iCtr, 1) 
            Selection.NumberFormat = "[<=9999999]###-####;(###) ###-####" 
        Else 
            sPhoneNumber = sPhoneNumber & Mid(sJustNumber, iCtr, 1) 
            Selection.NumberFormat = "[<=9999999]###-####;(###) ###-####" 
        End If 
    Next 
     
     'Set the value of the cell from where the macro was executed to the
     'first 12 positions of the formatted phone number.
    Application.ActiveWindow.ActiveCell.Value = Left$(sPhoneNumber, 12) 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    26th September 2012
    Posts
    128

    Re: Format Phone Number in VBA

    I am sorry, i didn't make that clear, try putting it right after this line:

    VB:
    Application.ActiveWindow.ActiveCell.Value = Left$(sPhoneNumber, 12) 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    30th December 2011
    Posts
    13

    Re: Format Phone Number in VBA

    Hello:

    That is where I placed it in the beginning.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    26th September 2012
    Posts
    128

    Re: Format Phone Number in VBA

    could you attach a file to look at?

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Format Phone Number In TextBox
    By FRIEL in forum EXCEL HELP
    Replies: 7
    Last Post: May 31st, 2008, 01:04
  2. Custom Format For Phone Numbers
    By jeffh@medlabdx. in forum EXCEL HELP
    Replies: 4
    Last Post: October 23rd, 2007, 06:59
  3. Dial Phone Number
    By EXCEL_VBA_LOVER in forum EXCEL HELP
    Replies: 17
    Last Post: July 21st, 2007, 02:10
  4. Format Phone number as it is being entered
    By Dave Godfrey in forum EXCEL HELP
    Replies: 5
    Last Post: March 8th, 2006, 15:01
  5. Spliting a phone number
    By Darren in forum EXCEL HELP
    Replies: 3
    Last Post: February 17th, 2005, 03:54

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno