Removing Special Characters with an "IF" statement

M

Mattie445

New Member
Joined
Sep 15, 2009
Messages
7
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]
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
What exactly do you consider to be "special characters"? Also, if you showed us what your two example text values would look like after the "special characters" were removed, that might help us to get you an answer quicker.
 
Upvote 0
Special Characters: The text would look like text without punctuation in a cell - word word word word word word
[TABLE="width: 64"]
<TBODY>[TR]
[TD]"</SPAN>[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]*</SPAN>[/TD]
[/TR]
[TR]
[TD]#</SPAN>[/TD]
[/TR]
[TR]
[TD]&</SPAN>[/TD]
[/TR]
[TR]
[TD]\</SPAN>[/TD]
[/TR]
[TR]
[TD]®</SPAN>[/TD]
[/TR]
[TR]
[TD]%</SPAN>[/TD]
[/TR]
[TR]
[TD]:</SPAN>[/TD]
[/TR]
[TR]
[TD];</SPAN>[/TD]
[/TR]
[TR]
[TD]<</SPAN>[/TD]
[/TR]
[TR]
[TD] [/TD]
[/TR]
[TR]
[TD]</SPAN>[/TD]
[/TR]
[TR]
[TD]+</SPAN>[/TD]
[/TR]
[TR]
[TD]?</SPAN>[/TD]
[/TR]
[TR]
[TD][</SPAN>[/TD]
[/TR]
[TR]
[TD]]</SPAN>[/TD]
[/TR]
[TR]
[TD]{</SPAN>[/TD]
[/TR]
[TR]
[TD]}</SPAN>[/TD]
[/TR]
[TR]
[TD]$</SPAN>[/TD]
[/TR]
[TR]
[TD]!</SPAN>[/TD]
[/TR]
[TR]
[TD]|</SPAN>[/TD]
[/TR]
[TR]
[TD]@


</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]
 
Upvote 0
Mattie445 said:
Special Characters: The text would look like text without punctuation in a cell - word word word word word word
Click to expand...
Please clarify the word "punctuation" for us. Your list does not contain commas and periods which most people would consider to be punctuation whereas the list includes characters such as $, @, and so forth which most people would not call punctuation. Also, I see you have [, ], { and } on your list, but not ( and )... was that deliberate? I guess the question would be... was the list you posted all-inclusive as to the characters you want removed? Or as the end of your statement above suggests, did you only want spaces, letters and digits (I'm guessing at that one because you did not show us what you examples would look like after the "special characters" were removed) to remain and all other characters removed?
 
Upvote 0
Sorry for the confusion. We added an additional element to this. This is to work in conjunction with a program, so not all "special characters" (below) are affected by the program. What they want now is to substitute these characters with ones the program will accept. The list below shows the substitutions. Items that are "delete", then we would want these removed, otherwise the substitution should replace existing special character. Thanks again for your input.

" - replace with in.
‘ - delete
* - delete
# - lb/ft
& - replace with and
\ - delete
® - delete
% - delete
: - replace with space
; - replace with comma
<- delete
> - delete
= - replace with equal
+ - delete
? - delete
[ - replace with (
] - replace with )
{ - replace with (
} - replace with )
$- replace with USD
! - delete
| - delete
@ - replace with at</SPAN></SPAN>
 
Upvote 0
Couldn't you use VBA to make the appropriate replacements for the selected range of cells (you could replace "Selection" with a name or range of your choice) - you'd obviously need to add the remaining conditions, or do all the replaces in a number of separate steps.
Code: 
Sub ReplaceInvalidCharacters()


    Dim WF As WorksheetFunction
    Dim MyCount As Long
    Dim MyCell As Range
    Set WF = WorksheetFunction
    
    For Each MyCell In Selection
         MyCell.Value = WF.Substitute(WF.Substitute(WF.Substitute(MyCell.Value, """", "in"), "#", "lb/ft"), "*", "and")
    Next


End Sub
 
Upvote 0
Think this would do the trick (not neat, but functional). Not sure about (R), so I have left a space for it as the first replace in the third line until I figure out how to do it.

Code: 
Sub ReplaceInvalidCharacters()


    Dim WF As WorksheetFunction
    Dim MyCount As Long
    Dim MyCell As Range
    Set WF = WorksheetFunction
    
    For Each MyCell In Selection
         MyCell.Value = WF.Substitute(WF.Substitute(WF.Substitute(MyCell.Value, """", "in"), "`", ""), "*", "")
         MyCell.Value = WF.Substitute(WF.Substitute(WF.Substitute(MyCell.Value, "#", "lb/ft"), "&", "and"), "\", "")
         MyCell.Value = WF.Substitute(WF.Substitute(WF.Substitute(MyCell.Value, """", "in"), "%", ""), ":", " ")
         MyCell.Value = WF.Substitute(WF.Substitute(WF.Substitute(MyCell.Value, ";", ","), "<", ""), ">", "")
         MyCell.Value = WF.Substitute(WF.Substitute(WF.Substitute(MyCell.Value, "=", "equal"), "+", ""), "?", "")
         MyCell.Value = WF.Substitute(WF.Substitute(WF.Substitute(MyCell.Value, "[", "("), "]", ")"), "{", "(")
         MyCell.Value = WF.Substitute(WF.Substitute(WF.Substitute(MyCell.Value, "}", ")"), "$", "USD"), "!", "")
         MyCell.Value = WF.Substitute(WF.Substitute(MyCell.Value, "|", ""), "@", "at")
    Next


End Sub
 
Upvote 0
Here you go. Seems CHR(210) and CHR(226) can result in a (R), so I have accounted for them both.
Note the use of """" (four double quote characters) in the first "Substitute" line to replace a " with "in".
You can obviously split the replaces over as many lines as you want - having three to a line, as in my example cuts down the length of your code.

Code: 
Sub ReplaceInvalidCharacters()


    Dim WF As WorksheetFunction
    Dim MyCount As Long
    Dim MyCell As Range
    Set WF = WorksheetFunction
    
    For Each MyCell In Selection
         MyCell.Value = WF.Substitute(WF.Substitute(WF.Substitute(MyCell.Value, """", "in"), "`", ""), "*", "")
         MyCell.Value = WF.Substitute(WF.Substitute(WF.Substitute(MyCell.Value, "#", "lb/ft"), "&", "and"), "\", "")
         MyCell.Value = WF.Substitute(WF.Substitute(WF.Substitute(MyCell.Value, Chr(210), ""), "%", ""), ":", " ")
         MyCell.Value = WF.Substitute(WF.Substitute(WF.Substitute(MyCell.Value, ";", ","), "<", ""), ">", "")
         MyCell.Value = WF.Substitute(WF.Substitute(WF.Substitute(MyCell.Value, "=", "equal"), "+", ""), "?", "")
         MyCell.Value = WF.Substitute(WF.Substitute(WF.Substitute(MyCell.Value, "[", "("), "]", ")"), "{", "(")
         MyCell.Value = WF.Substitute(WF.Substitute(WF.Substitute(MyCell.Value, "}", ")"), "$", "USD"), "!", "")
         MyCell.Value = WF.Substitute(WF.Substitute(WF.Substitute(MyCell.Value, Chr(226), ""), "|", ""), "@", "at")
    Next


End Sub
 
Last edited:
Upvote 0
Thanks, Pete. I'll give it go and report back my test findings. I sincerely appreciate your assistance.
 
Upvote 0
Here is the macro I came up with - it does not involve looping through each cell, so it should be faster (untested) than the other code submitted so far...
Rich (BB code): 
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
Note: I assumed your two description columns were Columns C and D. If that is wrong, then change the letter designation I highlighted in red above.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,248
Messages
6,171,011
Members
452,374
Latest member
keccles

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top