excel - 删除以特定字符串开头但结尾不同的句子
问题描述
我的 VBA 有点生锈了。我有以下问题:我有一列包含调查问卷的标签,每个标签上都附加了很多额外的单词,使它们几乎无法阅读。我需要删除的句子非常相似,但带有不同的标点符号,可以出现在中间单元格的开头。所有句子都以“使用”一词开头。以下是一些我需要清理的单元格示例(3 种主要类型):
1) "ABC123: - Using a scale of 1 to 5 ... . SomeText1"
(要删除的句子以“Using”开头,以 结尾"."
)
2) "DEF456: - Using a 1 to 5 point scale ... : SomeText2"
(要删除的句子以“Using”开头,以 结尾":"
)
3)"SomeTextLongerThan20Characters - Using a 1-5 point sca"
(删除语句从单元格中间开始,中间被截断)
我需要这 3 个案例看起来像这样:
1)"ABC123: SomeText1"
2)"DEF456: SomeText2"
3)"SomeTextLongerThan20Characters"
这是我无法开始工作的代码:
Sub Edit_String()
'
' Edit_String Macro
' Replaces chosen string with another string or nothing
'
Dim MyRange, c As Range
Dim strA, strB As String
For Each c In MyRange
Select Case Left(c.Text, 20)
Case Left(c.Text, 20) Like "*- Using*"
strA = "- Using*."
Case Left(c.Text, 20) Like "*: Using*"
strA = "- Using*:"
' Case Else
' If Left(c.Text, 20) <> "*Using*" Then strA = "- Using*"
End Select
Selection.Replace What:=strA, Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next c
MsgBox ("macro finished running")
End Sub
最后一个Case Else
被注释掉,因为我认为我不需要它。
我会很感激任何帮助。这似乎是一个简单的通配符/查找/替换问题,但我无法弄清楚。
解决方案
您应该能够将以下内容放入空白工作簿中进行测试。
Sub GetSentence()
Dim MyRange, c As Range
Dim strA, strB As String
Dim SplitItUp As Variant
Dim LeftPart, RightPart As String
[A1] = "ABC123: - Using a scale of 1 to 5. ... . SomeText1"
[A2] = "DEF456: - Using a 1 to 5 point scale ... : SomeText2"
[A3] = "SomeTextLongerThan20Characters - Using a 1-5 point sca"
Set MyRange = [a1:a3]
UsingLit = " - Using"
For Each c In MyRange
SplitItUp = Split(c.Value, UsingLit)
If UBound(SplitItUp) = 0 Then
Debug.Print UsingLit + " Not Found"
Else
LeftPart = Trim(SplitItUp(0))
RightPart = Trim(SplitItUp(UBound(SplitItUp)))
If InStr(RightPart, ":") Then
SplitItUp = Split(c.Value, ":")
RightPart = SplitItUp(UBound(SplitItUp))
Else
SplitItUp = Split(c.Value, ".")
If UBound(SplitItUp) > 0 Then
RightPart = SplitItUp(UBound(SplitItUp))
Else
RightPart = ""
End If
End If
End If
Debug.Print LeftPart + " " + RightPart
Next c
End Sub
推荐阅读
- paypal - 如果贝宝计划具有“非活动”状态?
- php - 使用 Arduino(通过 Xampp)在 phpmyadmin 上保存 RFID 详细信息
- java - 如何显示网络变化状态的对话?
- java - id从未找到过spring data jpa实体
- javascript - map() 返回带有对象的值对
- python - 如何使用字典访问特定的 Dataframe 行?
- html - 找不到虚拟主机 url
- yii2 - 如何在 MODULES 中使用 MODELS
- python - 不想等待 socket.accept() 每次循环迭代
- flutter - 如何在颤动中使用自定义剪辑器制作弯曲的应用栏