How to extract text on font color from a cell in Excel?

You have a data list with some red text in each cell in Excel as shown as below screenshot, and do you know how to extract the red text only? Now I will introduce a quick way to solve the puzzle that extracts text based on the font color from a cell in Excel.

In Excel, you only can use the defined function to extract the text based on font color.

1. Press Alt + F11 keys together to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module and copy the follow VBA code to the popped out window.

VBA: Extract text based on font color

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

Function GetColorText(pRange As Range) As String     'Updateby20141105     Dim xOut As String     Dim xValue As String     Dim i As Long     xValue = pRange.Text     For i = 1 To VBA.Len(xValue)         If pRange.Characters(i, 1).Font.Color = vbRed Then             xOut = xOut & VBA.Mid(xValue, i, 1)         End If     Next     GetColorText = xOut End Function

3. Then save and close the dialog, and select a blank cell next to the data list, type this formula =GetColorText(A1) (A1 indicates the cell you want to extract text from), press Enter key to get the needed text, then drag autofill handle to fill the formula to the range you want.

Now you can see all red text are extracted

Tip: In the above VBA code, if you need to extract the black text from cells, you can change the color name Color = vbRed to Color=vbBlack.

Refer:

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

- http://namdt5.blogspot.com/2015/11/create-custom-functions-in-excel-2007.html