Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple Logical Conditions With Date and String Comparison Not wo | Excel Worksheet Functions | |||
Comparison of alphanumeric string | Excel Discussion (Misc queries) | |||
Text string comparison - closest match | Excel Discussion (Misc queries) | |||
String Comparison & Conditional Formatting | Excel Discussion (Misc queries) | |||
comparison string VBA excell | Excel Discussion (Misc queries) |