VBA Help with replacing multiple values from a string in a range

[decadence]

Hi, Is it possible to find multiple words in a string then replace them with nothing. When I say multiple words I mean like 1000+ because each word I want to remove is varied with a variation which may or may not include all or some of the following characters : "/", "+", "-"," ". Also case sensitive is not an issue and may or may not have a space before or after the word in a string.

Examples:

1- PPM
1000/PPM
+ 25 TO - 80 C Temp
+ 125 TO-55CTEMP
+40 TO -85 C
100 - ppm
0.063 W
0.125W
200 w

From what I have read while trawling the internet for clues on the best way to do this and from what i can tell the best way us to store the values that I want to remove and delete them in one fell swoop. But I don't know how to do this.

 

[SteveO59L]

you are on the right lines.

assuming that your original phrases are listed, say in a1:a1000,

have your list of word to replace in (say) z1:znn

then use code to cycle through list a replacing words from list z

 

[MickG]

Perhaps something the below:-
NB:- Add you characters for deletion in code array "Ray".

[COLOR="Navy"]Sub[/COLOR] MG07Dec09
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
Ray = Array("+", "-", "/")
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]For[/COLOR] n = 0 To UBound(Ray)
        Dn.Value = Replace(Replace(Dn.Value, Ray(n), " "), "  ", " ")
    [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[decadence]

Hi MickG, This code works a treat, much appreciated, Thank you

 

[MickG]

You're welcome