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?