Replacing long string values-CodePudding

I have many rows with the first cell (column A) with a long string value (around 100 characters)

I am trying to replace these long string values with shorter ones:

Sub text_replacement()

Dim row As Range
Dim sheet As Worksheet
Set sheet = ActiveSheet

For i = 1 To sheet.UsedRange.Rows.Count

    Set row = sheet.Rows(i)
    If Cells(i, 1) = "This is a long string value*" Then
        Cells(i, 1).Value = "Short and standard value"
    End If

Next i

End Sub

I am using the "*" because all these long string values always starts the same. I don't know what is not working, but the below code does not have any effect on the values (and it does not give me any error either)

CodePudding user response：

Wildcards don't work with =. However, much more efficient to use Range.Replace, which does support wildcards, and no loop.

ActiveSheet.Range("A:A").Replace _
   What:="This is a long string value*", _
   Replacement:="Short and standard value", _
   LookAt:=xlWhole

CodePudding user response：

You're expecting the * at the end of the string to match any value after that string. This is not how the = comparator works. = requires that the values be exactly the same.

You want to check if the string starts with the key value. In VBA, this can be done a few ways - check the answers to this question:

Test if string begins with a string?