[chris_uk_lad]

Hi, i've been looking for some code that will remove unneccesary characters from an excel document, making it ripe for migration!

Characters in question are + signs and comma's but a universal one would be great!. Ive used a code before but my hard disk died a while back so lost forever

Any help would be greatly appreciated

[OBP]

Won't Excel's "Find & Replace" do it for you?

[chris_uk_lad]

yes but its time consuming as im wanting it to run through hundreds of records possibly which contains things like telephone numbers of foreign countrys (remove first +00) and ahost of other characters. Just thought there mightbe an easier way through a macro.

[OBP]

chris, if this is a long term job then yes it is worth building a Macro, if it just the one off then you will spend far more time on the macro than doing the removal manually.
You can in actual fact record what you are doing manually and that will give you the code, it will just need some refinement after that.
If you can't get that to work you really need to post an Example (Not real data) of what characters you want removed to get more help from us.

[slurpee55]

Chris, are you wanting to remove all commas and plus signs on the worksheet, or just in certain columns or what?

[slurpee55]

And after you do manage to get rid of them, you will probably have a lot of the numbers formatted as text. Take a look at Zack's code here (post 16) to convert them:
http://forums.techguy.org/business-a...ogether-2.html

[jimr381]

I think you were looking for the word erroneous and not erogenous(irrogenous).

[slurpee55]

aw jim, maybe in the UK....

[Zack Barresse]

I'm with OBP here. For continued use, a macro would probably work well for you. You could setup your characters into an array, then loop through that array checking the current worksheet (or whatever targeted worksheet you had) and 'clean' your data.

Here is an example...

Public Sub RemoveCharacters(Optional wks As Worksheet)
    Dim arrChars() As Variant, i As Long
    arrChars = Array("+", ",", "+00", "#", "%")
    If wks Is Nothing Then
        If ActiveSheet Is Nothing Then Exit Sub
        Set wks = ActiveSheet
    End If
    For i = LBound(arrChars) To UBound(arrChars)
        wks.Cells.Replace arrChars(i), vbNullString
    Next i
End Sub

Sub CallMySubToCleanThisWorksheetUp()
    Call RemoveCharacters
End Sub

Sub CallMySubToCleanThisWorksheetUp2()
    Call RemoveCharacters(ActiveSheet)
End Sub

HTH