在Google Sheets同一单元格内实现多样式文本

要实现单个单元格内合并不同数组的姓名并分别设置样式，Excel内置函数无法完成字符级的格式控制，必须用VBA代码来实现。以下是具体的代码和步骤：

实现代码 #

打开Excel，按Alt+F11打开VBA编辑器，插入一个新模块，粘贴以下代码：

Sub MergeArraysWithFormat()
    Dim array1 As Variant, array2 As Variant, array3 As Variant, array4 As Variant
    Dim cell As Range
    Dim i As Integer, currentPos As Integer
    
    ' 替换成你的实际数组内容
    array1 = Array("张三", "李四")
    array2 = Array("王五", "赵六")
    array3 = Array("孙七", "周八")
    array4 = Array("吴九", "郑十")
    
    ' 指定目标单元格，可根据实际修改工作表和单元格
    Set cell = ThisWorkbook.Sheets("Sheet1").Range("A1")
    cell.ClearContents ' 清空原有内容
    
    currentPos = 1 ' 记录当前字符插入位置
    
    ' 处理array1：红色字体
    For i = LBound(array1) To UBound(array1)
        cell.Value = cell.Value & array1(i) & ", "
        cell.Characters(Start:=currentPos, Length:=Len(array1(i))).Font.Color = RGB(255, 0, 0)
        currentPos = currentPos + Len(array1(i)) + 2 ' 偏移量包含姓名长度+逗号空格
    Next i
    
    ' 处理array2：绿色字体
    For i = LBound(array2) To UBound(array2)
        cell.Value = cell.Value & array2(i) & ", "
        cell.Characters(Start:=currentPos, Length:=Len(array2(i))).Font.Color = RGB(0, 128, 0)
        currentPos = currentPos + Len(array2(i)) + 2
    Next i
    
    ' 处理array3：橙色字体
    For i = LBound(array3) To UBound(array3)
        cell.Value = cell.Value & array3(i) & ", "
        cell.Characters(Start:=currentPos, Length:=Len(array3(i))).Font.Color = RGB(255, 165, 0)
        currentPos = currentPos + Len(array3(i)) + 2
    Next i
    
    ' 处理array4：灰色字体+删除线
    For i = LBound(array4) To UBound(array4)
        cell.Value = cell.Value & array4(i) & ", "
        With cell.Characters(Start:=currentPos, Length:=Len(array4(i))).Font
            .Color = RGB(128, 128, 128)
            .Strikethrough = True
        End With
        currentPos = currentPos + Len(array4(i)) + 2
    Next i
    
    ' 移除末尾多余的逗号和空格
    If Len(cell.Value) > 0 Then
        cell.Value = Left(cell.Value, Len(cell.Value) - 2)
    End If
End Sub

使用说明 #

1. 将代码中的示例数组替换成你实际的姓名数组
2. 如果目标单元格不是Sheet1的A1，修改代码中Set cell = ...这一行的工作表和单元格引用
3. 回到Excel界面，按Alt+F8选择MergeArraysWithFormat宏并执行，即可完成合并和格式设置

该代码会逐个将数组元素添加到目标单元格，同时为每个数组的姓名设置对应格式，最后自动移除末尾多余的逗号和空格。

内容的提问来源于stack exchange，提问作者wander21