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

问题描述

我有一列数据（A）。列（A）中每个单元格中的数据是半色和半色。例如，假设字符串的第一部分是红色，字符串的第二部分是黑色。每个单元格内的红色和黑色字符串的长度根据没有模式而变化。红色和黑色的字符类型不随任何模式而变化。没有空格或特殊字符将红色字符与每个单元格中的黑色字符分开。我想使用公式或函数提取和复制每个单元格的红色字符到一个新的列（B）。建议？

（A）原始..........（B）红色

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

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

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

解决方案

您可以使用此用户定义函数：

 函数redPart（x As Range）As String 
 Dim res As String 
 With x 
 For i = 1 To Len（.Value）
'red = RGB（255,0,0）
如果.Characters（i，1）.Font.Color = RGB（255， 0）then 
 res = res& .Characters（i，1）.Text 
 End If 
 Next 
 End With 
 redPart = res 
结束函数
  

只需在单元格 B1 中写入 = redPart（A1）

img src =https://i.stack.imgur.com/i1PsH.jpgalt =输入图片说明here>>

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) Original..........(B) Red

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

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

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

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

Result:

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