function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
jf317820jf317820 

phone number format

Hi, I don't know if this has been discussed as of yet, but is there a way to get phone fields to format correctly when inserting new records via the excel connector?
Ron HessRon Hess
I believe the phone number fields in the Web App are formated by javascript when you are entering the data and leave the field, therefore there is no way to trigger this from the connector.

one thing to try, insert a phone number in the app, see that it's formated as you prefer, then query this record into the connector, does it keep the format ?

if so, it may be possible to "format" the field in excel before uploading, however i think the field level formating provided by excel is just decoration and does not insert the characters you need "(" "-" " "   , so you would have to format this field using a formula, then upload.
 
bouscalbouscal
I have some VBA code that I wrote several years ago that takes care of all the find/replace and then formats all the numbers.  The only caveat is that the numbers MUST be 10 digits (US/CA area code + phone).  I just select the column(s) that contain phone numbers and run this from a button.
 
 
 
Code:
Sub Format_Phone()
' Tim Bouscal
' converts most default phone columns to US/CAN standard (###) ###-####

Dim TheRg As Range
Dim cCell As Range
On Error Resume Next

    Selection.Replace What:="1", Replacement:="1 ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="2", Replacement:="2 ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="3", Replacement:="3 ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="4", Replacement:="4 ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="5", Replacement:="5 ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="6", Replacement:="6 ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="7", Replacement:="7 ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="8", Replacement:="8 ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="9", Replacement:="9 ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="0", Replacement:="0 ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

Set TheRg = Selection.SpecialCells(xlCellTypeConstants, 2)
If TheRg Is Nothing Then MsgBox "No Text!", vbCritical: Exit Sub
On Error GoTo 0

  TheRg.Select
  
    Selection.Replace What:="=", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=True
  
    Selection.Replace What:="/", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=True
  
    Selection.Replace What:="(", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=True
        
    Selection.Replace What:=".", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=True
        
    Selection.Replace What:=")", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=True
    
    Selection.Replace What:="-", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=True
    
    Selection.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=True
        
    For Each cCell In TheRg
        If IsNumeric(cCell.Value) Then
            cCell.Value = "(" & Left(cCell.Value, 3) & ") " & Mid(cCell.Value, 4, 3) & "-" & Right(cCell.Value, 4)
        End If
    Next
    
    Selection.Replace What:="() -", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=True
        
End Sub

 
jf317820jf317820
awesome, this is good stuff

thanks again
dulleadullea

I had a list of phone numbers in raw 10-digit format (6035551212) in Excel and wanted to get it into salesforce with the connecter. To get the get it into salesforce's "pretty" formatting, (603) 555-1212, I copied each value to a new cell using the following formula:

="("&LEFT(A1,3)&") "&MID(A1,4,3)&"-"&RIGHT(A1,4)

I also had some in hyphenated format (603-555-1212) and used this formula:

="("&LEFT(A1,3)&") "&MID(A1,5,3)&"-"&RIGHT(A1,4)

Then I used the copied values to push to salesforce. Hope this helps someone along the way. It's my first post here. :-)

-Tim

P. S. I've added a screen shot as it appears the html editor converted some of my text to smileys...



Message Edited by dullea on 09-19-2008 02:51 PM

Message Edited by dullea on 09-19-2008 02:53 PM
TheBootTheBoot
Thank you so much for posting this.  I have been using the Excel Connector for years, and I was completely stumped that my phone numbers were being formatted as text.  I could have sworn I had done updates to phone numbers before and that they were automatically formatted by Salesforce.
 
I feel like a total dork, but I thank you for putting this here!