如何从具有多种颜色文本的单元格中提取基于字体颜色的文本 [英] How to extract text based on font color from a cell with text of multiple colors

问题描述

我有一列数据 (A).(A) 列中每个单元格中的数据是一种颜色的一半和另一种颜色的一半.例如，假设字符串的第一部分是红色，字符串的第二部分是黑色.每个单元格中红色和黑色字符串的长度没有规律地变化.红色和黑色的字符类型不同，没有图案.没有空格或特殊字符将每个单元格中的红色字符与黑色字符分开.我想使用公式或函数从每个单元格中提取红色字符并将其复制到新列 (B) 中.有什么建议吗?

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) 原版.......(B) 红色

(A) Original..........(B) Red

abjksglkjaf.......abjk

abjksglkjaf..........abjk

kjd3kdn9j............kjd3kd

kjd3kdn9j............kjd3kd

2hn89dslkjh.......2hn

2hn89dslkjh..........2hn

推荐答案

你可以使用这个自定义函数:

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

只需在单元格B1公式=redPart(A1)中写入并向下拖动即可.

just write in cell B1 formula =redPart(A1) and drag it down.

结果:

这篇关于如何从具有多种颜色文本的单元格中提取基于字体颜色的文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！