0

TL;DR: Is it possible to remove specific text in a cell, based on its color without losing remaining formatting other cell contents. Thus far any working solution can remove specifically colored text but strips remaining formatting.


I have recently inherited a large spreadsheet and in one section there are cells that have line breaks with multiple lines of text in each box. The previous person managing the spreadsheet colored each line according to the status of each item. An example is below (square brackets denotes the color each line is formatted in, please also note there should be a line break after each item).

31029 - Control [RED - bolded]
67934 - Control [BLUE]
41235 - Control [BLACK]
64304 - Action [GREEN - bolded]
69056 - Control [BLACK]

There are several columns (and many rows) of the data similar to above (i.e one column for the team member assigned to each action, one for description and one for status) however each follows the same color coding format.

What I need to do is delete all the blue (RGB(0,0,139)) items from every cell but retain the remaining contents and the formatting.

Is this possible?

Prior to submitting this question I have tried a number of solutions, such as this one on superuser where the information is copied into word and then back to excel. It works for me except when I remove the pilcrows in excel, all the remaining formatting is stripped (although I am able to use Word to remove the Blue text).

I have also tried several VBA solutions from Stackoverflow however I can't seem to get it to work. I have also tried removing the Pilcrow using the SUBSTITUTE function in excel, but it still strips the remaining formatting.

I had some luck with the 'Characters' Delete method' detailed in this MrExcel reply https://www.mrexcel.com/forum/excel-questions/677646-delete-text-within-cell-specific-color-retain-formatting-remaining-text.html However for some reason after I changed the code from colorindex, to the color RGB format and entered my ranges, it deleted some of the blue text, but not all. It appeared to leave the blue text when the cells contained many characters (perhaps more than those vba variables can deal with?

  • According to this answer: stackoverflow.com/a/31949941/2610837, Character.Delete only works in cells less than 255 characters. – fitch496 May 4 '18 at 12:51
  • One way is to collect the lines you wish to keep, along with their formatting information, and then recreate the new string (with the original formatting). Since each line has a single set of formatting (bold, color), shouldn't be too much trouble. But, of course, you will need VBA – Ron Rosenfeld May 4 '18 at 21:10
  • thanks fitch and Ron. Do either of you have any links to some VBA code where splitting and recombining cells has been performed? – Graphene May 5 '18 at 10:19
0

This assumes that

  • each line in the cell has the same color and boldness characteristics
  • Data to be processed is in Column A

Algorithm:

  • Get the range to be tested
  • Split the cell contents with vbLf as the delimiter
  • Create a class including the text string, boldness and color
  • save each instance of the class into a collection, except if it has the color to be deleted.
  • recreate the cell text without the relevant line and format the cell data on a line by line basis as the original

Class Module Rename: cLineData

Public pText As String
Public pBold As Boolean
Public pColor As Long
Public pLength As Long

Regular Module

Option Explicit
Sub DeleteColoredLine()
    Dim cLD As cLineData, Coll As Collection
    Dim wsSrc As Worksheet
    Dim R As Range, C As Range, V As Variant, W As Variant
    Dim lineNum As Long, charPos As Long, I As Long

Set wsSrc = Worksheets("sheet4")
With wsSrc
    Set R = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
End With

For Each C In R
    Set Coll = New Collection
    V = Split(C.Text, vbLf)
    For lineNum = 0 To UBound(V)
        Set cLD = New cLineData
            charPos = charPos + Len(V(lineNum)) + 1 'include newline character
        cLD.pText = V(lineNum)
        cLD.pLength = Len(cLD.pText)
        With C.Characters(charPos - 1, 1).Font 'last printed character in line
            cLD.pBold = .Bold
            cLD.pColor = .Color
        End With

        'Check for color to be removed
        If Not cLD.pColor = RGB(0, 112, 192) Then _
            Coll.Add cLD
    Next lineNum

'Create the new string
    I = 0
    ReDim V(0 To Coll.Count - 1)
    For Each cLD In Coll
        V(I) = cLD.pText
        I = I + 1
    Next cLD
    C.Offset(0, 1).Value = Join(V, vbLf)

'Format the lines
    charPos = 1
    With C.Offset(0, 1)
        For Each cLD In Coll
            With .Characters(charPos, cLD.pLength).Font
                .Bold = cLD.pBold
                .Color = cLD.pColor
            End With
            charPos = charPos + Len(cLD.pText) + 1 '+1 to include newline character
        Next cLD
    End With
Next C

End Sub

Note that,

  • other formatting characteristics can be added to the class test to test for and/or reproduce
  • my blue (RGB code) is a bit different than your blue
  • This macro puts the results in the adjacent column. You could also choose to either overwrite the original column, or hide it.

In the screenshot below, Column A is the original, Column B is the result of running the above macro.

enter image description here

  • Thanks Ron! For some reason the code seems to run slightly differently for me. In some instances the blue lines are still there, just no longer colored blue. – Graphene May 7 '18 at 4:24
  • Imgur screenshot – Graphene May 7 '18 at 4:40
  • @Graphene A screenshot is not at all useful to trouble shoot. You'll need to upload a copy of the malfunctioning workbook, and post a link so I can download and examine it. – Ron Rosenfeld May 7 '18 at 12:52

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.