Note: The other languages of the website are Google-translated. Back to English

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.

doc-extract-text-color-1

Extract text based on font color from each cell

Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
  • Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
  • Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

arrow blue right bubble 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
'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. 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.

doc-extract-text-color-2

Tip: In the above VBA code, you can change the color rgb in this script TextColor = RGB(255, 0, 0) to others to meet your need.


Relative articles:


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

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!
officetab bottom
Comments (14)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hello, when i tried using vbBlue, it did not work. Help please? Thanks!
This comment was minimized by the moderator on the site
Give the font color to the exct blue color.
Because there are many type of blue color "light blue, sky blue, dark blue"
vba match only exct color.??????
This comment was minimized by the moderator on the site
The code works well if the red strings are continuous. In case they are separated in the source cell, they are glued to each other in output cell. I mean there is no space in the output if the red text strings are away from each other. Can you please provide a solution for this?
This comment was minimized by the moderator on the site
Hey, Thanks for the Tip. Works great with Red and Black.
What if I want to parse "Purple"? changing it vbPurple does not work.
Thanks
This comment was minimized by the moderator on the site
Buonasera, grazie mille un'ottima funzione. Però ho un problema, quando val nel foglio ed inserisco es. = GetColorText (A1) mi restituisce il valore giusto però come riavvio il file mi da errore #VALORE!, se poi clicco 2 volte sopra mi da il valore corretto e così via. che posso fare?
This comment was minimized by the moderator on the site
dimenticavo io ho Excel 2010
This comment was minimized by the moderator on the site
thanks, very interesting code.
This comment was minimized by the moderator on the site
The code works well if the red strings are continuous. In case they are separated in the source cell, they are glued to each other in output cell. I mean there is no space in the output if the red text strings are away from each other. Can you please provide a solution for this?
This comment was minimized by the moderator on the site
Try this, it should work
Function GetColorText(pRange As Range) As String
'Updatedtogetmultiple
Dim xOut As String
Dim xValue As String
Dim i As Long
Dim wasRed As Boolean
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)
wasRed = True
ElseIf wasRed = True Then
wasRed = False
xOut = xOut & ";"
End If

Next

GetColorText = xOut
End Function

This comment was minimized by the moderator on the site
Thanks a ton it worked perfectly well. You are a savior Tim :-) 
This comment was minimized by the moderator on the site
I was still unable to extract the blue text (#1166BB). Is there a way to use the Hex: #1166BB to get the color text I need?
This comment was minimized by the moderator on the site
Hi, Robert E Perez, I have updated the VBA code, now, it supports to get colored text by rgb code. You can try the code again.
This comment was minimized by the moderator on the site
I have a particular text in a cell seperated by Comma and Having colour for each text. While using Delimiter funtion,I am unable to retain the font colour of text .Appraciate your help!
This comment was minimized by the moderator on the site
Hi, jdhjjd, I do not understand your question clearly. Do you want to extract the specific text from cells and keep its font color as below screenshot shown?
https://www.extendoffice.com/images/stories/comments/sun-comment/split_and_keep_font_color.png
or split cells and keep each text font color?
https://www.extendoffice.com/images/stories/comments/sun-comment/split_and_keep_font_color-2.png
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations