excel - 将字符串更改为大写(如果存在) - VBA

Question

如何仅将存在的特定字符串更改为大写。

If (Cells(i, "A") Like "*roada*") Or (Cells(i, "A") Like "*roadb*") _
Or (Cells(i, "A") Like "*roadc*") etc... Then 'Change only the found string to Uppercase.

每个单元格包含两个或多个单词。示例:单元格 A1 由“roadhouse blues”组成。我只想将“roadh”更改为大写(如果该单元格中存在)。这在 VBA 中可能吗？

Answer 1

这就能解决问题:

Const road As String = "road"

Dim s As String
Dim letterAfterRoad As String

s = "play that roadhouse blues" ' or get contents of some cell
letterAfterRoad = Mid(s, InStr(s, road) + Len(road), 1)
Mid(s, InStr(s, road)) = UCase(road & letterAfterRoad)

Debug.Print s ' returns "play that ROADHouse blues". Write to cell.

<小时/>

如果我是你，我会留意@minitech 的讽刺言论。如果您要查找的是 road?，其中 ? 是字母 a-z，那么让 Like 查找 a-z 而不是手动输入整个字母表...

这是我的做法:

Const road As String = "road"

Dim s As String
Dim charAfterRoad As String
Dim roadPos As Long

s = "play that roadhouse blues"

roadPos = InStr(s, road)
If roadPos > 0 And Len(s) >= roadPos + Len(road) Then
    'Found "road" and there is at least one char after it.
    charAfterRoad = Mid(s, roadPos + Len(road), 1)
    If charAfterRoad Like "[a-z]" Then
        Mid(s, InStr(s, road)) = UCase(road & charAfterRoad)
    End If
End If

Debug.Print s ' returns "play that ROADHouse blues"