VB String Comparison In Excel

  • Thread starter Thread starter Craig
  • Start date Start date
C

Craig

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

- \ / [ ] ( ) * 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
 
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
 
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.


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

- \ / [ ] ( ) * 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
Click to expand...
 
Back
Top