excel - Replace special characters within a string in selection - Stack Overflow

admin管理员组

文章数量:1400533

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

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

• excel
• vba
• string
• replace

Share Improve this question Follow edited Mar 24 at 17:40 braX 11.8k55 gold badges2222 silver badges3737 bronze badges asked Mar 24 at 17:39 Mohamad BachroucheMohamad Bachrouche 1511212 bronze badges 3

• 1 Could you share a list (a screenshot or text using a markdown generator) of all the possibilities of the before and after? Please explain why you're checking a single cell and replacing all cells of the selection. Also, share the complete code. – VBasic2008 Commented Mar 24 at 18:42
• Yes sir great catch. This code will not work if the selections are not the same as the active cell. Will only work when individually selecting an array of like cells. Would it be too cumbersome to loop through each cell in a selection and apply the code respectively to each cell independent of the active cell? – Mohamad Bachrouche Commented Mar 25 at 18:10
• All the possibilities before and after: "" to "!" to "!!" back to "" "shift" to "shift!" to "shift!!" back to "shift" "shift<" to "shift!<" to "shift!!<" back to "shift<" "shift<^" to "shift!<^" to "shift!!<^" back to "shift<^" "shift^" to "shift!^" to "shift!!^" back to "shift^" – Mohamad Bachrouche Commented Mar 25 at 18:13

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 3

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
            

You didn't declare your "c". After declaring your "c', you will be able to fill in your data.
Other than that, i would put the condition test separately as function for better presentation.

the code is shown below, hope this can help you.
-------------------
I saw that you asked "indefinite "!"", in other answer, it can be handle in condition 4 below, replacing all "!" before adding it if all other condition did not meet.

---------------------

Sub ABC()

Dim CellA As Range

        For Each CellA In Selection.Cells
                CellA = ABC1(CellA)
        Next CellA

End Sub

Function ABC1(StringA As Variant)

        If InStr(1, StringA, "!!<") <> 0 Then
                StringA = Replace(StringA, "!!<", "<")
        ElseIf InStr(1, StringA, "!<") <> 0 Then
                StringA = Replace(StringA, "!<", "!!<")
        ElseIf InStr(1, StringA, "<") > 0 And InStr(1, StringA, "!<") = 0 Then
                StringA = Replace(StringA, "<", "!<")
        ElseIf InStr(1, StringA, "<") = 0 Then
                StringA = Replace(StringA, "!", "")
                StringA = StringA & "!"
        End If
        
        ABC1 = StringA

End Function

Be careful about mixing ActiveCell and Selection.Cells the way you have in your question and in @cybernetic.nomad's answer. Changing values in the whole selection based on the value of one cell could easily lead to wrong results. Consider these cells where A1 is the ActiveCell, and A1:C1 are the Selection.Cells. The result is shown in row 2; Bar!!< becomes Bar!!!< because of the initial value of Foo<.

	A	B	C
1 before	Foo<	Bar!!<	Baz!<
2 after	Foo!<	Bar!!!<	Baz!!<

You can however, use this to your advantage, and solve your problem with this two-statement subroutine.

Public Sub Rotate()
    Selection.Cells.Replace "<", "!<"
    Selection.Cells.Replace "!!!<", "<"
End Sub

本文标签： excelReplace special characters within a string in selectionStack Overflow