excel - How to extract text based on font color from a cell with text of multiple colors

Question

I have a column of data (A). The data in each cell in column (A) is half one color and half another color. For example, let's say the first portion of the character string is red and the second portion of the character string is black. The length of the red and black character strings within each cell varies with no pattern. The type of characters that are red and black vary with no pattern. There is no space or special character that separates the red characters from the black characters within each cell. I would like to extract and copy the red characters from each cell into a new column (B) using a formula or function. Suggestions?

(A) Original..........(B) Red

abjksglkjaf..........abjk

kjd3kdn9j............kjd3kd

2hn89dslkjh..........2hn

Answer 1

You can use this user defined function:

Function redPart(x As Range) As String
    Dim res As String
    With x
        For i = 1 To Len(.Value)
            ' red = RGB(255, 0, 0)
            If .Characters(i, 1).Font.Color = RGB(255, 0, 0) Then
                res = res & .Characters(i, 1).Text
            End If
        Next
    End With
    redPart = res
End Function

just write in cell B1 formula =redPart(A1) and drag it down.

Result: