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.
Extract text based on font color from each cell
Recommended Productivity Tools for Excel/Office
Office Tab: Bring tabbed editing to Excel and other Office software, just like Chrome, Firefox and Safari.30-day Unlimited Free Trial
Kutools for Excel: 300 powerful features make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
Extract text based on font color from each cell
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
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.
Relative articles:
Recommended Productivity Tools
Office Tab
Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.
Kutools for Excel
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
300 New Features for Excel, Make Excel Much Easy and Powerful:
- Merge Cell/Rows/Columns without Losing Data.
- Combine and Consolidate Multiple Sheets and Workbooks.
- Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
- Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
- More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...