Replace "and" if the number goes on then clear the rest
I'm completely new to playing with macros, but I'm pretty quick to get involved. Here's my problem:
I have a datasheet with descriptions that include special characters that we cannot load into another system when converting to a CSV file. I have a big part to replace the copyright characters with nothing, but quotes and apostrophes are still a problem. Some of my data will look like this:
48 "Display
"P2-Cam" Zoom
With cable "Snagless" 15 '
I need to parse the cells in that column (in my case "C") and replace with "inch" or "feet" if there is a number going on, but remove it completely if it doesn't.
This is what I have recorded so far:
Sub RemoveSpecialCharacters()
' Removes Special Characters from cells
'
Columns("C:C").Select
Selection.Replace What:="™", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("C:C").Select
Selection.Replace What:="®", Replacement:="", LookAt:=xlPart, _
SearchOrder:= xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns("C:C").Select
Selection.Replace What:="©", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
I understand that I can set all the specifications. like an array, but I don't have enough practice at this time. Some online guides were helpful, but I need to do more research, apparently.
Thank you so much!
source to share
According to one of the comments, it is best to use regular expressions. Here's one way to do it, but it won't handle multiple matches on the same line. It will only handle the first match (either "or") - your best bet is to change it to be recursive to get all matches in the string if that's a problem for you. I am assuming all of your rows are in column "A" of the first sheet. At least this serves as an example if you want to extend it:
Sub ReplaceFtAndInches()
Dim regExInFt As New RegExp
regExInFt.Pattern = "\s*(-{0,1}\d*\.{0,1}\d+)\s*(\""|\')\s+"
With Sheets(1):
Dim val As String
Dim i As Integer, pos As Integer
Dim mat As Object
Dim str As String
For i = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row:
val = .Cells(i, "A").Value
Set mat = regExInFt.Execute(val)
If mat.Count = 1 Then
str = mat(0).SubMatches(1)
pos = InStr(1, val, str)
If str = """" Then
.Cells(i, "A") = Mid(val, 1, pos - 1) & " inches" & Mid(val, pos + 1)
Else
.Cells(i, "A") = Mid(val, 1, pos - 1) & " feet" & Mid(val, pos + 1)
End If
End If
Next
End With
End Sub
The regex searches for any number of spaces, followed by a negative or positive decimal / integer, followed by any number of spaces, and then either "or". It converts "to inches" and "feet".
Please note that you must follow the instructions in this post to enable regular expressions in excel .
source to share