Removing Special Characters with an "IF" statement

[Mattie445]

If column B contains "Ref.", then remove all special characters from columns D and Y.


Here is an example of Column B, C D and Y: Any assistance much appreciated!



[TABLE="width: 753"]
<TBODY>[TR]
[TD]Material Number</SPAN>[/TD]
[TD]2nd Mat Number</SPAN>[/TD]
[TD]Description</SPAN>[/TD]
[TD]RTA Description</SPAN>[/TD]
[/TR]
[TR]
[TD]Ref.XXXXXXXXX[/TD]
[TD]XXXXXXXXXXX[/TD]
[TD]NE(tm) "NE-SLIM" Valve Model, 4 1/2 in., 3.75 in. Seal Bore ID-Min, RPT , 5.965 in. Max OD, Equalizing Type, 64.5 in.Approximate Length, 4 1/2-13.50# TSH Blue Box x Pin, '13% CHROME' /410 STAINLESS STEEL/17-4 PH STAINLESS STEEL/NICKEL ALLOY 925/NICKEL ALLOY 718 Matl, 7500 psi Pressure Rating, H2S Service, API 14A/ISO 10432, 1,3s API/ISO Level</SPAN>[/TD]
[TD]NE(tm) Tubing-Retrievable Safety Valve, NE-SLIM Valve Model, 4 1/2 in., 3.75 in. Seal Bore ID-Min, RPT , 5.965 in. Max OD, Equalizing Type, 64.5 in.Approximate Length, 4 1/2-13.50# TSH Blue Box x Pin, 13% CHROME/410 STAINLESS STEEL/17-4 PH STAINLESS STEEL/NICKEL ALLOY 925/NICKEL ALLOY 718 Matl, 7500 psi Pressure Rating, H2S Service, API 14A/ISO 10432, 1,3s API/ISO Level</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL></COLGROUP>[/TABLE]

 

[Mattie445]

Thanks, Rick - I'll try them both this afternoon. Will give a progress report!

 

[Rick Rothstein]

Thanks, Rick - I'll try them both this afternoon. Will give a progress report!

@Mattie445,

I just noticed that this forum's message processor chopped off some characters in the Orignals constant (the Const statement) between the less than symbol and the greater than symbol in my previous posting (interpretting them as HTML tags no doubt). Below is the code you should use (if I did it right, all the characters in the Originals constant should show up - Edit Note - Yes, I see they are all there now)...

Sub ReplacePunctuationSymbols()
  Dim X As Long, Char As String, Rng As Range, Cell As Range, Replacements As Variant
  Const Originals As String = ":""=;@&]}[{#$!%*?\|‘+>®<"
  Replacements = Array(" ", ".", "=", ",", "at", "and", ")", ")", "(", "(", "lb/ft", "USD")
  ReDim Preserve Replacements(1 To Len(Originals))
  Set Rng = Range("C2:D" & Cells(Rows.Count, "D").End(xlUp).Row)
  For X = 1 To Len(Originals)
    Char = Mid(Originals, X, 1)
    If Char Like "[*?]" Then Char = "~" & Char
    Rng.Replace Char, Replacements(X), xlPart
  Next
End Sub