Deleting a sentence that starts with a specific string but ends differently

Question

My VBA is kind of rusty. I have the following problem: I have a column that contains labels for a questionnaire, and there is a lot of extra words tacked on to each label making them almost unreadable. The sentences I need to get rid of are very similar but come with different punctuation and can either appear in the beginning of the cell of in the middle. All sentences start with the word "Using". Here are some examples of cells that I need to clean up (3 main types):

1) "ABC123: - Using a scale of 1 to 5 ... . SomeText1" (sentence to remove starts with "Using" and ends with ".")

2) "DEF456: - Using a 1 to 5 point scale ... : SomeText2" (sentence to remove starts with "Using" and ends with ":")

3) "SomeTextLongerThan20Characters - Using a 1-5 point sca" (sentence to remove starts in the middle of the cell and is cut off in the middle)

I need these 3 cases to look like this:

1) "ABC123: SomeText1"

2) "DEF456: SomeText2"

3) "SomeTextLongerThan20Characters"

Here is my code that I could not get to work:


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

The last Case Else is commented out since I figured I don't need it.

I'd appreciate any help. This seems like a simple wildcard/find/replace issue, but I can't figure it out.

Sounds like a [regular expression](https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops) may solve your problem — cybernetic.nomad, Feb 27 '20 at 21:06

REXXman · Answer 1 · 2020-02-28T11:44:20.017

You should be able to put the following in a blank workbook to test.

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

score 0 · Accepted Answer · answered Feb 28 '20 at 12:01

If your original code works for you besides the case statement. This will get the case statement working.

Sub Edit_String()
'
' Edit_String Macro
' Replaces chosen string with another string or nothing
'

Dim MyRange, c As Range
Dim strA, strB 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]


For Each c In MyRange
    c.Select
    Select Case True
        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

score 0 · Answer 3 · answered Jul 25 '22 at 07:45

I was googling for this but if there was a way to use excels find and replace to do this. I figured it out myself and I want to put the answer here if anyone else was in the same boat as me. The trick is to type in the search the start of the word with a asterix, add one space, put another asterix and what it should end with.

Deleting a sentence that starts with a specific string but ends differently

3 Answers3