Replacing long string values

Question

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)

score 3 · Accepted Answer · answered Jan 04 '23 at 17:02

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

score 2 · Answer 2 · answered Jan 04 '23 at 17:00

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?

Replacing long string values

2 Answers2