跳到主要內容

如何從Excel中的單元格中基於字體顏色提取文本？

如果您在Excel的每個單元格中都有一個帶有紅色文本的數據列表，如下面的屏幕截圖所示，您知道如何僅提取紅色文本嗎？ 現在，我將介紹一種快速的方法來解決難題，該難題根據Excel中單元格中的字體顏色提取文本。

文檔提取文本顏色 1

根據字體顏色從每個單元格中提取文本

箭頭藍色右氣泡 根據字體顏色從每個單元格中提取文本

在Excel中，您只能使用定義的函數根據字體顏色提取文本。

1。 按 Alt + F11鍵 鍵一起打開Microsoft Visual Basic for Applications窗口。

2。 點擊 插入 > 模塊 並將下面的VBA代碼複製到彈出的窗口中。

VBA：基於字體顏色提取文本

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.然後保存並關閉對話框，並選擇數據列表旁邊的空白單元格，鍵入此公式 = GetColorText（A1） （A1表示要從中提取文本的單元格），按 Enter 鍵以獲取所需的文本，然後拖動自動填充手柄將公式填充到所需的範圍。

現在您可以看到所有紅色文本均已提取。

文檔提取文本顏色 2

尖端：在上面的VBA代碼中，你可以把這個腳本TextColor = RGB(255, 0, 0)中的顏色rgb改成其他的來滿足你的需要。

相關文章：

最佳辦公生產力工具

🤖 Kutools 人工智慧助手：基於以下內容徹底改變數據分析： 智慧執行   |  生成代碼  |  建立自訂公式  |  分析數據並產生圖表  |  呼叫 Kutools 函數...
熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup  |   多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧   |  工作簿和工作表管理器   |  資源庫 （自動文字）   |  日期選擇器   |  合併工作表   |  加密/解密單元格   |  按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 （過濾粗體/斜體/刪除線...）...
前 15 個工具集:  12 文本 工具 (添加文本, 刪除字符，...）   |   50+ 圖表 類型 (甘特圖，...）   |   40+ 實用 公式 (根據生日計算年齡，...）   |   19 插入 工具 (插入二維碼, 從路徑插入圖片，...）   |   12 轉化 工具 (數字到單詞, 貨幣兌換，...）   |   7 合併與拆分 工具 (高級合併行, 分裂細胞，...）   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能，體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

產品描述

Office選項卡為Office帶來了選項卡式界面，使您的工作更加輕鬆

  • 在Word，Excel，PowerPoint中啟用選項卡式編輯和閱讀，發布者，Access，Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%，每天為您減少數百次鼠標點擊！
Comments (14)
No ratings yet. Be the first to rate!
jdhjjd
jdhjjd
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!
Reply
0
0
Sunny
Sunny     jdhjjd
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
Reply
Report
0
0
Robert E Perez
Robert E Perez
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?
Reply
0
0
Sunny
Sunny     Robert E Perez
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.
Reply
Report
0
0
prudwi
prudwi
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?
Reply
0
0
Tim
Tim     prudwi
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

Reply
0
0
Prudwi
Prudwi     Tim
This comment was minimized by the moderator on the site
Thanks a ton it worked perfectly well. You are a savior Tim :-) 
Reply
0
0
Kazam Raza
Kazam Raza
This comment was minimized by the moderator on the site
thanks, very interesting code.
Reply
0
0
Bobbo69
Bobbo69
This comment was minimized by the moderator on the site
dimenticavo io ho Excel 2010
Reply
Report
0
0
Bobbo69
Bobbo69
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?
Reply
Report
0
0
JM
JM
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
Reply
0
0
Madhav
Madhav
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?
Reply
0
0
mj
mj
This comment was minimized by the moderator on the site
Hello, when i tried using vbBlue, it did not work. Help please? Thanks!
Reply
0
0
amit
amit     mj
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.??????
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations