vba challenge

[pnuckle]

i have a long list of names and i have to validate each. the criteria is that there must no name containing characters such as (, < > . / : ; " = + _ ` ~ ! * - + @ # $ % ^ & *) all these characters are invalid in any name.

the problem is that i don't know how can i use vba to remove any of those characters whenever they appear in any name.

i count on you members!

all the best.

thanx in advance.

 

[Ruddles]

Check out the Replace function which can be used to strip characters out of a string.

Incidentally, is that a hyphen in your list?

 

[MickG]

Try this for data in column "A".

[COLOR="Navy"]Sub[/COLOR] MG01Mar23
[COLOR="Navy"]Dim[/COLOR] oCh [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
    oCh = Array(")", "(", ",", "<", ">", ".", "<", "/", "<", ":", ";", ",""", "=", "+", "_", "`", "~", "!", "*", "-", "+", "@", "#", "$", "%", "^", "&", "*")
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]For[/COLOR] n = 0 To UBound(oCh)
        [COLOR="Navy"]If[/COLOR] InStr(Dn, oCh(n)) [COLOR="Navy"]Then[/COLOR]
            Dn = Replace(Dn, oCh(n), "")
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]Next[/COLOR] D
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[machopicho]

Another solutions reported range A1:

Sub test()
n = Len(Range("A1"))
For i = 1 To n
On Error Resume Next
If Asc(Mid(Range("A1"), i, 1)) > 32 And Asc(Mid(Range("A1"), i, 1)) < 39 Or _
Asc(Mid(Range("A1"), i, 1)) > 41 And Asc(Mid(Range("A1"), i, 1)) < 48 Or _
Asc(Mid(Range("A1"), i, 1)) > 57 And Asc(Mid(Range("A1"), i, 1)) < 63 Or _
Asc(Mid(Range("A1"), i, 1)) > 93 And Asc(Mid(Range("A1"), i, 1)) < 97 Or _
Asc(Mid(Range("A1"), i, 1)) = 64 Or Asc(Mid(Range("A1"), i, 1)) = 126 Then
Range("A1") = Replace(Range("A1"), Mid(Range("A1"), i, 1), "")
End If
Next
End Sub

 

[MickG]

Just Notices !!!.
Change array for below, and add as required !!

    oCh = Array(")", "(", ",", "<", ">", ".", "<", "/", "<", ":", ";", ",", Chr(34), "=", "+", "_", "`", "~", "!", "*", "-", "+", "@", "#", "$", "%", "^", "&", "*")