Delete red text inside cell using VBA

[PC_Meister]

Hello,

I am wondering if it is possible to delete red text inside a cell using VBA. Please note that the cell can contain red text and text with other color. So for example if the cell has "John, Joe". Only "John," is deleted from the cell. Thanks in advance.

 

[Momentman]

Here's a small UDF i wrote which can be improved

Function RemoveRedText(rng As Range)
    Dim I As Integer
    If rng.Cells.Count = 1 Then
        For I = 1 To Len(rng)
            If rng.Characters(I, 1).Font.Color <> vbRed Then
               RemoveRedText = RemoveRedText & Mid(rng, I, 1)
            End If
        Next I
    End If
End Function

and this is what it looks like on the worksheet

Excel 2010
	A	B
1	John Doe	Doe
2	Jane diatmen	diatmen
3	James Callagher	James
orkut

Cell Formulas
Range	Formula
B1	=RemoveRedText(A1)

I noticed that the red font isn't shown(surprisingly) in the sheet displayed. In the first, "John" is red, second, "Jane" is red and in the Third "Callagher" is Red

 

[PC_Meister]

Here's a small UDF i wrote which can be improved

Function RemoveRedText(rng As Range)
    Dim I As Integer
    If rng.Cells.Count = 1 Then
        For I = 1 To Len(rng)
            If rng.Characters(I, 1).Font.Color <> vbRed Then
               RemoveRedText = RemoveRedText & Mid(rng, I, 1)
            End If
        Next I
    End If
End Function

and this is what it looks like on the worksheet
Excel 2010

	A	B
1	John Doe	Doe
2	Jane diatmen	diatmen
3	James Callagher	James

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

orkut​

Worksheet Formulas Cell Formula B1 =RemoveRedText(A1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

I noticed that the red font isn't shown(surprisingly) in the sheet displayed. In the first, "John" is red, second, "Jane" is red and in the Third "Callagher" is Red

Works great, I have learned something new about range.characters

 

[Momentman]

You are welcome. Thanks for the feedback

 

[Rick Rothstein]

Works great

Does it really? You said in you first message "I am wondering if it is possible to delete red text inside a cell using <acronym title="visual basic for applications">VBA</acronym>" which sounded like you wanted to modify the text directly within the cell. Also, you said "Please note that the cell can contain red text and text with other color" and Momentman's function will not preserve other colors. If you really do want to modify the text directly within its own cell, then consider the following macro. Select the cell or cells you want to process and then run this macro...

Sub RemoveTheRedText()
  Dim X As Long, Cell As Range
  For Each Cell In Selection
    For X = Len(Cell.Text) To 1 Step -1
      If Cell.Characters(X, 1).Font.Color = vbRed Then Cell.Characters(X, 1).Text = ""
      If X > 1 Then
        If Cell.Characters(X - 1, 2).Text = "  " Then
          Cell.Characters(X, 1).Text = ""
        End If
      ElseIf Cell.Characters(X, 1).Text = " " Then
        Cell.Characters(X, 1).Text = ""
      End If
    Next
  Next
End Sub

Note: I added code to make sure leading spaces are removed and double spaces collapsed down to single spaces.

 

[Momentman]

Does it really? .

I guess I was right when i said

Here's a small UDF i wrote which can be improved

Thanks Rick