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.
Recommended Productivity Tools for Excel/Office
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.
Recommended Productivity Tools
Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.
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...
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 2 months agoThe 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?
To post as a guest, your comment is unpublished.· 2 months agothanks, very interesting code.
To post as a guest, your comment is unpublished.· 3 months agodimenticavo io ho Excel 2010
To post as a guest, your comment is unpublished.· 3 months agoBuonasera, 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?
To post as a guest, your comment is unpublished.· 1 years agoHey, Thanks for the Tip. Works great with Red and Black.
What if I want to parse "Purple"? changing it vbPurple does not work.