How to extract text based on font color from a cell in Excel?
In Excel, you may occasionally encounter datasets where cells contain mixed text colors, such as some characters or words highlighted in red for emphasis or categorization purposes. For example, a cell may display a combination of regular and red-colored text to mark important items. Extracting only the text of a specific font color (such as red) can be highly valuable for data analysis, reporting, or further processing—especially when you need to isolate key highlights or annotations made by font color.
This task cannot be accomplished directly using Excel’s built-in functions, as standard formulas do not detect or act on font color. As a result, if you have a data list similar to the one in the screenshot below, and your goal is to extract only the red text from each cell, you may wonder what practical methods exist to achieve this efficiently. The following solution details how to extract text based on font color in Excel, making it easier to manage, filter, and utilize such data as needed.
Extract text based on font color from each cell
Extract text based on font color from each cell
By default, Excel does not provide a ready-made function to extract text based on font color within a cell. Therefore, custom VBA code (user-defined function) is required to handle this task. This approach is suitable for users who are comfortable working with VBA and need to extract colored text programmatically from numerous or complex cells. The advantage of this method is its flexibility in specifying any color you want to target; its limitation is that it does not dynamically update if color formatting changes unless you recalculate or reapply the function.
Follow these steps to create and use a function for extracting colored text:
1. Press the Alt + F11 keys at the same time to launch the Microsoft Visual Basic for Applications (VBA) window. If you have not enabled the Developer tab, you can also right-click any tab on the ribbon, choose Customize the Ribbon, and then add the Developer tab for easier future access.
2. In the VBA window, click Insert > Module. This will create a new module where you can paste the following VBA code.
VBA: Extract text based on font color
Function GetColorText(pRange As Range) As String
'UpdatebyExtendoffice20220621
Dim xOut As String
Dim xValue As String
Dim i As Long
Dim TextColor
TextColor = RGB(255, 0, 0) 'colorindex RGB
xValue = pRange.Text
For i = 1 To VBA.Len(xValue)
If pRange.Characters(i, 1).Font.Color = TextColor Then
xOut = xOut & VBA.Mid(xValue, i, 1)
End If
Next
GetColorText = xOut
End Function
3. After pasting the code, save your changes and close the VBA editor. Return to your Excel worksheet. Select a blank cell adjacent to your data list, and enter the following formula:
=GetColorText(A1)
Replace A1 with the specific cell reference you wish to extract text from. Press Enter to execute the function and extract only the text with the specified font color (red, by default) from the chosen cell. To apply this extraction to other cells in your list, drag the fill handle (at the lower-right corner of the selected cell) down or across the range as needed.
Now, all instances of red text within each cell will be extracted and displayed in the corresponding formula cell. This makes it efficient to separate colored text for further analysis or presentation.
Tip: In the VBA code provided, the line TextColor = RGB(255,0,0) determines the target color (red in this case). If you need to extract text of a different color, adjust the RGB value to match the font color you want (e.g., for blue, use TextColor = RGB(0,0,255)).
It is important to note that this method will only extract text from the cell that strictly matches the specified font color. If the text color is a close shade but not identical in RGB values, it may not be recognized. Double-check font color codes in your worksheet if extraction isn’t producing expected results.
If you encounter issues where the formula does not return any text, ensure macros are enabled in your Excel environment, and verify that the font colors actually match the RGB values set in the code. Remember, user-defined functions like this are available only in the workbook where you added the code unless you save it as an add-in or re-add it to other workbooks.
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
Relative articles:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in