VB String Comparison In Excel
 

I want to program code in VB in Excel that eliminates
certain characters from a particular cell. The characters
a

- \ / [ ] ( ) * P/N (ALT) (OLD) (NEW)

There are some others, but these are the
characters/substrings I wish to eliminate. How would
something like that start out? If anyone has any tips,
please let me know. Thank you...

Craig

VB String Comparison In Excel
 

Craig,

Try something like the following:

Dim S As String
Dim ReplaceTokens As Variant
Dim Ndx As Long
ReplaceTokens = Array("P/N", "'", "\", "/", "[", "]", "(", ")", _
"*", "(ALT)", "(OLD)", "(NEW)")
S = "\abc[]def/*" '<<<< STRING TO PROCESS
For Ndx = LBound(ReplaceTokens) To UBound(ReplaceTokens)
S = Replace(S, ReplaceTokens(Ndx), "")
Next Ndx
Debug.Print S


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Craig" wrote in message
...
I want to program code in VB in Excel that eliminates
certain characters from a particular cell. The characters
a

- \ / [ ] ( ) * P/N (ALT) (OLD) (NEW)

There are some others, but these are the
characters/substrings I wish to eliminate. How would
something like that start out? If anyone has any tips,
please let me know. Thank you...

Craig

VB String Comparison In Excel
 

One cell???

then I'd do something like:

Option Explicit
Sub testme()

Dim myWords As Variant
Dim iCtr As Long
Dim rng As Range
Dim myStr As String

myWords = Array("P/N", "(ALT)", "(OLD)", "(NEW)", _
"-", "\", "/", "[", "]", "(", ")", "~*")

Set rng = Worksheets("sheet1").Range("a1")

myStr = rng.Value
For iCtr = LBound(myWords) To UBound(myWords)
myStr = Application.Substitute(myStr, myWords(iCtr), "")
Next iCtr

rng.Value = myStr

End Sub

Notice that the asterisk became ~* and I rearranged the strings so that I did
P/N before the slash character and the same with the parentheses!

If you add the question mark, you'll need ~? (and same for the tilde itself ~
becomes ~~).

If you have multiple cells, it might be quicker to do a bunch of Edit|replaces
against those cells--instead of looping though each of the cells.



Craig wrote:

I want to program code in VB in Excel that eliminates
certain characters from a particular cell. The characters
a

- \ / [ ] ( ) * P/N (ALT) (OLD) (NEW)

There are some others, but these are the
characters/substrings I wish to eliminate. How would
something like that start out? If anyone has any tips,
please let me know. Thank you...

Craig

--

Dave Peterson