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


How to extract text based on font color from a cell in Excel?
If 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.

Function GetColorText(pRange As Range) As String
Dim xOut As String
Dim xValue As String
Dim i As Long
xValue = pRange.Text
xOut = xOut & VBA.Mid(xValue, i, 1)
End If
End Function

Now you can see all red text are extracted.

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
For i = 1 To VBA.Len(xValue)
If pRange.Characters(i, 1).Font.Color = vbRed Then
GetColorText = xOut

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 in the range you want.
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.

No comments:

Post a Comment

Thank you for your comment!

Blog Archive