Forum Discussion

Copper Contributor

Aug 23, 2019

Separate red and black into two columns

Hi, Can anyone please advise me how can I separate red and black into two columns in excel . I have attached here screen shot. Best wishes, Shikir23

PReagan

Bronze Contributor

Aug 26, 2019

Hello Shikir23,

This could be accomplished with a UDF (User-Defined Function) in VBA. See how here:

https://www.extendoffice.com/documents/excel/2234-excel-extract-cell-color.html

Hope this helps!
PReagan

Shikir23

Copper Contributor

Aug 29, 2019

Hi PReagan,

Thank you very much for your suggestion, I was able to separate red and black using the formula. But I don't know how to delete the remaining red colour text in excel so that I can align both separated data into two columns . I have attached here screenshot. Can you please help me on this matter?

Best wishes,

Shikir23

PReagan
Bronze Contributor
Aug 29, 2019
Hello Shikir23,

I would suggest creating a new UDF with the same code but instead of using vbRed, use vbBlack. Then you can extract all of the black text from the first column and place it into the column to the right of your UDF-vbRed column. Then, if all looks correct, you can delete all columns prior to your UDF-vbRed column.

Hope this helps!
PReagan