vba 如何根据字体颜色从具有多种颜色文本的单元格中提取文本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21514704/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to extract text based on font color from a cell with text of multiple colors
提问by user3263430
I have a column of data (A). The data in each cell in column (A) is half one color and half another color. For example, let's say the first portion of the character string is red and the second portion of the character string is black. The length of the red and black character strings within each cell varies with no pattern. The type of characters that are red and black vary with no pattern. There is no space or special character that separates the red characters from the black characters within each cell. I would like to extract and copy the red characters from each cell into a new column (B) using a formula or function. Suggestions?
我有一列数据（A）。(A) 列中每个单元格中的数据一半是一种颜色，一半是另一种颜色。例如，假设字符串的第一部分是红色，字符串的第二部分是黑色。每个单元格内的红色和黑色字符串的长度没有规律地变化。红色和黑色的字符类型各不相同，没有图案。没有空格或特殊字符将每个单元格内的红色字符与黑色字符分开。我想使用公式或函数将每个单元格中的红色字符提取并复制到新列 (B) 中。建议？
(A) Original..........(B) Red
(A) 原版..........(B) 红色
abjksglkjaf..........abjk
abjksglkjaf.........abjk
kjd3kdn9j............kjd3kd
kjd3kdn9j…………kjd3kd
2hn89dslkjh..........2hn
2hn89dslkjh..........2hn
回答by Dmitry Pavliv
You can use this user defined function:
您可以使用此用户定义函数：
Function redPart(x As Range) As String
Dim res As String
With x
For i = 1 To Len(.Value)
' red = RGB(255, 0, 0)
If .Characters(i, 1).Font.Color = RGB(255, 0, 0) Then
res = res & .Characters(i, 1).Text
End If
Next
End With
redPart = res
End Function
just write in cell B1formula =redPart(A1)and drag it down.
只需在单元格B1公式中写入=redPart(A1)并将其向下拖动即可。
Result:
结果：
