查找Excel单元格中的子字符串并设置其格式
查找Excel单元格中的子字符串并设置其格式
提问于 2012-12-13 12:55:52
作为Excel编程的新手，我在web上搜索了一个函数/UDF/VBA，我可以在Excel中使用它来查找单元格中的子字符串，并在此单元格中加粗/着色。
我下面有一张这样的床单。-列A包含要部分格式化的文本，-列B包含我正在查找的子串，-列C包含子串(B)在字符串(A)中的位置
A B C
1 ABCDEFGHIJKLM FGHI 6-9
2 NOPQRSTUVWXYZ UVWXY 8-12
...下面是我需要获得的信息：
A B C
1 ABCDE**FGHI**JKLM FGHI 6-9
2 NOPQRST**UVWXY**Z UVWXY 8-12
...是否可以将它应用于整个列，而不是只应用于行。
任何帮助或建议都将不胜感激。谢谢！
回答 1
Stack Overflow用户
回答已采纳
发布于 2012-12-13 13:13:47
' just add a loop and some checkings
Sub t()
Dim heightA As Long
Dim heightB As Long
Dim height As Long
Dim i As Long
heightA = Cells(Rows.Count, 1).End(xlUp).Row
heightB = Cells(Rows.Count, 2).End(xlUp).Row
If heightA < heightB Then
height = heightA
Else
height = heightB
End If
If height > 1 Then
For i = 1 To height
Range("A" & i).Font.Bold = False
''Replace the formula with the full string
Range("A" & i) = Range("A" & i).Value
'Find the starting position of the string in B within the string produced by the formula
If Range("B" & i).Value <> "" Then
myPos = InStr(1, Range("A" & i).Value, Range("B" & i).Value, 1)
If myPos > 0 Then
'Bold the string from B column
Range("A" & i).Characters(myPos, Len(Range("B" & i).Value)).Font.Bold = True
End If
End If
Next i
End If
End Sub编辑：使用MASTER_STR，SUBSTR1，POS1(开始-结束)，SUBSTR2，POS2，e.t.c.格式的位置。
Sub t()
Dim heightA As Long
Dim heightB As Long
Dim height As Long
Dim i As Long
Dim length As Long
Dim tmp
Dim width As Long
heightA = Cells(Rows.Count, 1).End(xlUp).Row
heightB = Cells(Rows.Count, 2).End(xlUp).Row
If heightA < heightB Then
height = heightA
Else
height = heightB
End If
If height > 1 Then
For i = 1 To height
Range("A" & i).Font.Bold = False
''Replace the formula with the full string
Range("A" & i) = Range("A" & i).Value
'Find the starting position of the string in B within the string produced by the formula
width = Cells(i, Columns.Count).End(xlToLeft).Column
If width >= 3 Then
For j = 3 To width Step 2
If Cells(i, j).Value <> "" Then
tmp = Split(Cells(i, j).Value, "-")
myPos = CLng(tmp(0))
length = CLng(tmp(1)) - myPos + 1
'myPos = InStr(1, Range("A" & i).Value, Range("B" & i).Value, 1)
If myPos > 0 Then
'Bold the string from B column
Range("A" & i).Characters(myPos, length).Font.Bold = True
End If
End If
Next j
End If
Next i
End If
End Sub示例输入
ABCDEFG A 1-1 C 3-5
ABCDEFG A 1-1 C 3-3页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/13853212
复制相关文章
相似问题
腾讯云开发者
