excel - How to extract text based on font color from a cell with text of multiple colors and separate multiple words by Delimiter? -

how extract text based on font color cell text of multiple colors

i have column of data (a). data in each cell in column (a) half 1 color , half color. have extract each word separated delimiter if @ different places. tried solution in above link unable make changes suit purpose beginner vba. please suggest methods sole problem.

(a) original..........(b) red

abcdefgh..........abc, gh

a user defined function (aka udf) should through this.

function udf_whats_colored(rtxt range, optional iclrndx long = 3)     dim c long, str string      c = 1 len(rtxt.text)         rtxt.characters(start:=c, length:=1)             if .font.colorindex = iclrndx                 if not cbool(len(str)) or _                    rtxt.characters(start:=c + (c > 1), length:=1).font.colorindex = iclrndx                     str = str & mid(rtxt.text, c, 1)                 else                     str = str & ", " & mid(rtxt.text, c, 1)                 end if             end if         end     next c     udf_whats_colored = str  end function 

after considering of shades of red available, opted simpler solution of .colorindex = 3. there optional parameter can set own colorindex property number. if need more colors, should small matter swap code on .font.color property.

        

the syntax used in above image in b2 is,

=udf_whats_colored(a2) 

---

        
                     ^{colorindex palette table}