Removing parentheses in a phone number

[tcfowler]

I have a row (M2) of phone number that are mismatched with the
following:

555-555-5555
(555)555-5555
(555) 555-5555
5555555555

I just want to simply have all numbers:

555-555-5555

I have read endless pages in the group but nothing addresses my
issue. I have changed the format to 000-000-0000, but it does not
remove parentheses.

 

[Guest]

very easy:

Sub sphone()
Set r = Selection
v = r.Text
v = Replace(v, "(", "")
v = Replace(v, ")", "")
v = Replace(v, " ", "")
v = Replace(v, "-", "")
v = Left(v, 3) & "-" & Mid(v, 4, 3) & "-" & Right(v, 4)
r.Value = v
End Sub

 

[Tim Williams]

Function CleanUp(v)
dim s as string

s=replace(v,"(","")
s=replace(s,")","")
s=replace(s,"-","")
s=replace(s," ","")

If len(s)=10 then
CleanUp=left(s,3) & "-" & mid(s,4,3) & "-" & right(s,4)
Else
CleanUp="?Number?"
End if

End function

 

[Guest]

sub FixData()
Dim cell as Range, s as String
Dim s1 as String, s2 as String
for each cell in selection
s = ""
s1 = cell.Text
for i = 1 to len(s1)
s2 = mid(s1,i,1)
if isnumeric(s2) then
s = s & s2
end if
Next
s = Format(clng(s),"000-000-0000")
cell.Value = s
Next
End sub

 

[Helmut Weber]

Hi,

I have no idea about the structure of supposedly american
phone numbers, but if all your phone numbers follow
this rule: 3 digits dash 3 digits dash 4 digits,
then it's easy. Too easy.

E.g.

Public Function Phone(s As String) As String
Dim t As String
Dim l As Long
For l = 1 To Len(s)
If IsNumeric(Mid(s, l, 1)) Then
t = t & (Mid(s, l, 1))
End If
Next
Phone = Format(t, "###-###-####")
End Function


However, as IMHO this will hardly be the case,
we, if I may say so, need a bit more of information.
If the rule above does not apply,
and there is no set of rules which covers all
possible variations, then for
e.g. 5555555555 there is no solution.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Helmut Weber]

Hi Tom,

nice to see a common appraoch.

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[Helmut Weber]

Hi everybody,

the question is: What is the question?

The perfect solution would be "555-555-5555".

As simple as that. ;-)

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[tcfowler]

Hi everybody,

the question is: What is the question?

The perfect solution would be "555-555-5555".

As simple as that. ;-)

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

Yes this is what I was looking for, 3 digits dash 3 digits dash 4
digits.
Tim's solution worked for me. Thanks for everyones input.

It seems with coding eveyone has a different approach, but the results
are the same.