excelvbastringreplace

Replace special characters within a string in selection


Below is code that works to add one ! to one or more selection of cells but how do I add one subsequent ! and then remove !! after?

There are two sequences:

  1. shift1< to shift1!< to shift1!!< back to shift1<
  2. shift1<^ to shift1!<^ to shift1!!<^ back to shift1<^
    'successfully adds ! to any selection of blank cells or after any string
    'example: from "blank cell" to "!" or from "shift1" to "shift1!"
        If InStr(ActiveCell.Value, "!") = 0 And InStr(ActiveCell.Value, "^") = 0 Then
            If InStr(ActiveCell.Value, "<") = 0 And InStr(ActiveCell.Value, ">") = 0 _
            And InStr(ActiveCell.Value, "^") = 0 Then
                For Each c In Selection.Cells
                    c.Value = c.Value & "!"
                Next c
                
        'successfully adds ! in between shift string and < if ! is not present
        'example: from "shift1<" to "shift1!<"
            ElseIf InStr(ActiveCell.Value, "!") = 0 Then
                Selection.Cells.Replace "<", "!<"
                
        'does not add another ! in between shift string and < if one ! is present
        'example: from "shift1!<" to "shift1!!<"
            ElseIf InStr(ActiveCell.Value, "!") = 1 Then
                Selection.Cells.Replace "!<", "!!<"
                
        'does not remove !! in between shift string and < if two ! are present
        'example: from "shift1!!<" to "shift1<"
            ElseIf InStr(ActiveCell.Value, "!") = 2 Then
                Selection.Cells.Replace "!!<", "<"
            End If
            
    'successfully adds ! in between shift string and <^
    'example: from "shift1<^" to "shift1!<^"
        ElseIf InStr(ActiveCell.Value, "<") > 0 And InStr(ActiveCell.Value, "^") > 0 _
        And InStr(ActiveCell.Value, "!") = 0 Then
            Selection.Cells.Replace "<^", "!<^"
            
    'does not add another ! in between shift string and <^
    'example: from "shift1!<^" to "shift1!!<^"
        ElseIf InStr(ActiveCell.Value, "<") > 0 And InStr(ActiveCell.Value, "^") > 0 _
        And InStr(ActiveCell.Value, "!") = 1 Then
            Selection.Cells.Replace "!<^", "!!<^"
            
    'does not remove !! in between shift string and < if two ! are present
    'example: from "shift1!!<^" to "shift1<^"
        ElseIf InStr(ActiveCell.Value, "<") > 0 And InStr(ActiveCell.Value, "^") > 0 _
        And InStr(ActiveCell.Value, "!") = 2 Then
            Selection.Cells.Replace "!!<^", "<^"
        End If

Solution

  • Your two cases are essentially the same. It doesn't matter if there is a ^ after < if you only change things before the <.

    Try:

            If InStr(ActiveCell.Value, "!") = 0 Then
                    Selection.Cells.Replace "<", "!<"
            ElseIf InStr(ActiveCell.Value, "!!<") > 0 Then
                    Selection.Cells.Replace "!!<", "<"
            ElseIf InStr(ActiveCell.Value, "!<") > 0 Then
                    Selection.Cells.Replace "!<", "!!<"
            End If