Batch remove spaces, brackets and -'s from cells?

[zep]

Hi there,

I have a sheet with only 1 column which lists a whole lot of businesses in this format:

1 Company Name
2 Address
3 Category
4 Phone Number
5 Company Name
6 Address
7 Category
8 Phone Number
...and so on up to around 6000

In the phone number cells (Every 4th stating from A4) the number have been taken from various sources so they are all in different formats:
098776990
(09)877 6990
09-8776990
and other variations with brakects, dashes adn spaces in different places.

What I would like to do is run a script that looks at every 4th cell and removes anything that is not numeric, pushing all the numbers together as is seen in the first number example.

Any help would be greatly appreciated.

Cheers
Leon

 

[Datsmart]

This code will do what you want.

Sub RemoveChars()
Dim arrChars As Variant, i As Long
    arrChars = Array("\", "/", "-", " ", "(", ")", ":")
    For i = LBound(arrChars) To UBound(arrChars)
        Columns(1).Replace what:=arrChars(i), replacement:="", lookat:=xlPart
    Next i
End Sub

The code does not effect spaces in regular text, it removed unwanted spaces and characters from numbers.
Coded to work in column A.

 

[jindon]

try

Sub test()
Dim a, i As Long
With Range("a1", Range("a" & Rows.Count).End(xlUp)
     .Value = a
     With CreateObject("VBScript.RegExp")
          .Pattern = "\D+"
          .Global = True
          For i = 4 To UBound(a,1) Step 4
               a(i,1) = .replace(a(i,1), "")
          Next
     End With
     .NumberFormat = "@"
     .Value = a
End With
End Sub

 

[zep]

Oh no!!! I used Datsmart's code but it didn't do every fourth! It did all rows and now all my data is squashed together and I saved it with out realising.

Any idea on how I can rectify this easily?