Replace " and ' if there is a number proceeding, then clean the rest

Question

I'm completely new to the macros game but I'm a pretty quick learner. Here is my problem:

I have a data sheet with descriptions that include special characters that we can't upload into another system when converted to a .csv file. I've got most of it down to replace copyright symbols with nothing, but quotes and apostrophes are still an issue. Some of my data will look like this:

48" Display

"P2-Cam" Zoom

With "Snagless" 15' cable

What I need is to parse the cells in this column (in my case "C"), and replace " with inch or ' with feet if there is a number proceeding it, but remove it completely if there isn't.

This is what I have written down 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 realize I could set all spec. characters as an array, but I don't have enough practice with it currently. Some online guides have been helpful, but I need to research more, apparently.

Thanks much!

Oh man, thank you, I clearly don't know what I'm doing really, I apologize. — Karingan, Apr 06 '17 at 22:46
You will probably still need to post some code showing you have made an **attempt** at solving the problem, but someone **may** give you a "write some code for me" piece of code without it. — YowE3K, Apr 06 '17 at 22:48
You will need the code to be smart enough to replace `48"` to `48inch` and not `"P2-Cam"` to `inchP2-Caminch`. Research on making loops in VBA, then `Array(,,)` and also `Chr()` and `Asc()` that you may need. — PatricK, Apr 06 '17 at 23:14
There are several different approaches you could use. There is an excellent introduction to using Regular Expressions in Excel here on Stack Overflow. Check it out: http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops — Rich Holton, Apr 07 '17 at 04:08

score 0 · Answer 1 · edited May 23 '17 at 12:17

As per one of the comments your best bet is to use regular expressions. Here's one way to do it but it won't handle multiple matches in the same string. It'll only process the first match (either " or ') - best to modify it so it's recursive to get all matches in the string if that's an issue for you. I'm assuming all of your strings are in column "A" of the first worksheet. At the very least this serves as an example should you wish 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 regular expression looks for any number of spaces followed by a negative or positive decimal/integer number followed by any number of blank characters and then either " or '. It converts " to inches and ' to feet.

Note that to enable regular expressions in excel you should follow the instructions in this post.

Replace " and ' if there is a number proceeding, then clean the rest

1 Answers1