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.

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

--
Gary''s Student
gsnu200711

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

--
Tim Williams
Palo Alto, CA


wrote in message oups.com...
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.

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"

On Mar 22, 1:17 pm, Helmut Weber wrote:
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.

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

--
Regards,
Tom Ogilvy

" wrote:

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.

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"

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"