Phone number insanity

Dragonflight

New Member
Joined
Nov 26, 2002
Messages
11
I'm working on a thousand-line database, and we need to streamline the data to a uniform format. The data was provided from all over the place, so the phone number data is formatted in just about every way there is. For example:

(123) 456-7890 <--- this is the desired result
1-234-567-8900
123-4567
123-456-7890
123- 456-7890
123 456 7890

The thought of having to go through this list manually horrifies me. It's got two phone numbers, cell, pager, fax, and alternate contact numbers, all done up in this jumble. Manual formatting would drive me insane. Is there a way to fix this problem faster?
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Theadish

New Member
Joined
Jan 21, 2005
Messages
34
You should just be able to format one of them, copy it, then do a paste special - Formats on all the other numbers.
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
This may be a start:
Code:
Sub ConvertPhoneNumbers()
    With Range([A1], [A65536].End(xlUp))
        .Replace What:="-", Replacement:="", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False
        .Replace What:=" ", Replacement:="", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False
        .NumberFormat = "[<=9999999]###-####;(###) ###-####"
    End With
End Sub
Hope that helps,

Smitty
 

chiello

Well-known Member
Joined
Jan 18, 2005
Messages
848
Dragonflight said:
(123) 456-7890 <--- this is the desired result
1-234-567-8900
123-4567
123-456-7890
123- 456-7890
123 456 7890

Your "desired result" is made by 10 digits, while not all raw data have the same lenght.
What doing in this case?
 

Dragonflight

New Member
Joined
Nov 26, 2002
Messages
11

ADVERTISEMENT

The data was compiled from a number of different departments. Each department has a varying degree of enthusiasm for this project, and a varying degree of interest in providing the information we require in the format we require. It's been like pulling teeth to get this much. To turn around and demand they format it properly first means we'll probably never get it all. So formatting this mess was dropped on me. @_@

The code above worked very well, except for one detail. Any entry formatted in the correct manner was altered. For instance:

(123) 456-7890
became
(123)456-7890

Also, someone mentioned above doing a copy->paste special as Format. I tried that first thing with cells, and the whole column. Nothing. I guess it's because although they're a mess, they're all also technically different accepted phone number formats, and the computer can't tell them apart. Or something. I'm guessing here. :) But it didn't change the contents of the cell.
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
Using PennySaver's
This should do most formats?

Sub ConvertPhoneNumbers()
'Sheet module code, like: Sheet1.
Dim c As Range
Dim myLen

With Range([A1], [A65536].End(xlUp))
.Replace What:="-", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
.Replace What:=" ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End With
For Each c In Range([A1], [A65536].End(xlUp))
myLen = Len(c)
If myLen = 11 Then
c.NumberFormat = "#(###) ###-####"
End If
If myLen = 10 Then
c.NumberFormat = "(###) ###-####"
End If
If myLen = 7 Then
c.NumberFormat = "###-####"
End If
Next c
End Sub
 

Dragonflight

New Member
Joined
Nov 26, 2002
Messages
11

ADVERTISEMENT

The code works extremely well, which means I gotta be doing *something* wrong on my end. :)

The data has some numbers already properly formatted. But when the macro runs, it changes that by stripping out the spaces and dashes.

I go from:
(123) 456-7890
to
(123)4567890

What am I missing?

Thanks for this btw. Nothing more frustrating than the clueless. Which is why it's so frustrating to BE clueless in the field. :LOL:
 

Seti

Well-known Member
Joined
May 19, 2002
Messages
2,916
Try this one with a couple small adjustments. Kudos to Joe though.

Added replacing "(" and ")" with nothing and getting rid of leading 1 on long distance numbers.

Code:
Sub ConvertPhoneNumbers()
'Sheet module code, like: Sheet1.
Dim c As Range
Dim myLen

With Range([A1], [A65536].End(xlUp))
    .Replace What:="-", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
    .Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
    .Replace What:="(", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
    .Replace What:=")", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End With
For Each c In Range([A1], [A65536].End(xlUp))
    myLen = Len(c)
    If myLen = 11 Then
        c = Right(c, 10)
        myLen = 10
    End If
    If myLen = 10 Then
        c.NumberFormat = "(###) ###-####"
    End If
    If myLen = 7 Then
        c.NumberFormat = "###-####"
    End If
Next c
End Sub
 

PA HS Teacher

Well-known Member
Joined
Jul 17, 2004
Messages
2,838
Assuming you only care about the characters that are numbers, and moving from right to left, I worked out this User Defined Function.
Here's to variety:

Code:
Function Phone(Num As String) As String
' *****  Formats Number/Text of any format to Desired Phone Format ***************
' This UDF looks from the right side of the string and accepts the first 10 digits
' as the numbers to be included in the phone number.  All non numeric numbers are ignored
' Each Digit is stored in sNumArr Starting from the right and moving leftward until all
' 10 digits have been accepted.

Dim sNumArr(1 To 14) As String, C As Integer, Position As Integer
Position = 14
sNumArr(1) = "("             'Position of desired Formatting Characters
sNumArr(5) = ")"
sNumArr(6) = " "
sNumArr(10) = "-"

For i = Len(Num) To 1 Step -1
If Position > 1 Then
  C = Asc(Mid(Num, i, 1))          ' ASCII Number Corresponding to this Character
  If C< 48 Or C > 57 Then
                                   'Don't includ this character in Phone Number
  Else
    sNumArr(Position) = Chr(C)
    Position = Position - 1
    If Position = 10 Then Position = 9     'Makes sure digits do not overwrite
    If Position = 6 Then Position = 4      'Desired Formatting Characters
    If Position = 5 Then Position = 4
  End If
End If
Next i
Num = ""
For i = 1 To 14
  Phone = Phone & sNumArr(i)
Next i
End Function
Phone Number formatter.xls
ABCD
1=Phone()UDFAsEntered
2(123)456-7890(123)456-7890<---thisisthedesiredresult
3(234)567-89001-234-567-8900
4(896)354-9698A1-896BC354*96U98
5(745)783-7647UI^7)45+78&t3X76E4@7
6()123-4567123-4567
7(123)456-7890123-456-7890
8(123)456-7890123-456-7890
9(123)456-78901234567890
10(208)740-198912345-193208740198-9
Sheet1
 

Forum statistics

Threads
1,148,525
Messages
5,747,188
Members
424,068
Latest member
Salim khamis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top