Phone number formatting

[kriedel858]

Using Excel for customer relationship management (CRM). Would use the company's CRM system, but it's too unstable. Trying to paste phone numbers into a field and have them automatically formatted in the (###)###-### format. Basic custom formatting works except when the data includes special characters, which I'd like to have removed and replaced with the ones in the custom formatting. So whether I paste in 1234567890, or (123)-456 7890, I'd like to have the cell show (123)456-7890 after the data is pasted in. Is this doable?

 

[Dave Patton]

try the following custom number format [<=9999999]###-####;(###) ###-####

or look at custom format special telephone number

T202105a.xlsm
	A	B
1
2	(123) 456-7890
2e

 

[kriedel858]

Tried this. If the data I'm pasting in includes non-numeric characters, it keeps them and doesn't format anything. Ultimately, want to be able to post in data that may contain special characters (ex.123.456.7890. or 123-456-7890, have the special characters stripped out, and format it in the cell in the preferred way. Maybe my initial examples could have been better.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version & platform (Don’t forget to scroll down & ‘Save’)

automatically formatted in the (###)###-### format. ..... So whether I paste in 1234567890, or (123)-456 7890, I'd like to have the cell show (123)456-7890 after the data is pasted in. Is this doable?

A little unclear as the # sample above indicates 9 digits whereas numerical examples indicate 10 digits.
Assuming that you expect 10 digits for each number you could try this Worksheet_Change event code with a copy of your workbook. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

I have assumed ..

• Numbers will be entered in column D. Edit where indicated in code if different
• You are expecting 10 digits to be entered (with or without other characters). If more or less than 10 digits this code will leave the entry unformatted.
• You are on a Windows platform

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim RX As Object
  Dim Changed As Range, c As Range
  Dim s As String
  
  Const Pat1 As String = "\D"
  Const Pat2 As String = "^(\d{3})(\d{3})(\d{4})$"
  
  Set Changed = Intersect(Target, Columns("D")) '<- Check column
  If Not Changed Is Nothing Then
    Set RX = CreateObject("VBScript.RegExp")
    RX.Global = True
    Application.EnableEvents = False
    For Each c In Changed
      RX.Pattern = Pat1
      s = RX.Replace(c.Value, "")
      RX.Pattern = Pat2
      If RX.test(s) Then c.Value = RX.Replace(s, "($1)$2-$3")
    Next c
    Application.EnableEvents = True
  End If
End Sub

 

[kriedel858]

Tried using the VBA code you provided. Didn't work. I'm using Office 365, but thinking my employer may have disabled macros on our company laptops. My home computers run office on Macs. Will the same code work there? Haven't had time to try it there yet.

 

[Peter_SSs]

my employer may have disabled macros on our company laptops.

If that is the case then you will not be able to achieve this: ".. special characters, which I'd like to have removed and replaced with the ones in the custom formatting."

My home computers run office on Macs. Will the same code work there?

I don't use a Mac but I believe this code will not run on one.

BTW, "didn't work" doesn't give helpers much to go on. In future, try to be more descriptive. eg
- Did it give an error message? (What message on what line of code?)
- Did it crash Excel?
- Did it run but produce the wrong result? (What result?)
- Did it do nothing?
- etc