find and replace using WildCard

Question

I have a column range (stored as text) where I have to replace second last digit from 7 to 5 for only cells ending in "-fsa". Here is an example

Find

52881871-fsa

Replace

52881851-fsa

I tried using wildcard, however it only works in find function but doesn't work in replace function.

Thank You for the help!

I used following in my code, the find does what's asked but replace doesn't

Selection.Replace What:=("??????7?-fsa"), Replacement:=("??????5?-fsa"), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False

`What:=("??????7?-fsa)` is missing the closing quote `What:=("??????7?-fsa")` — Pᴇʜ, May 11 '22 at 09:16
thanks, sorry that was a typo on my part, however the code doesn't still produce the intended result. — Tartans, May 11 '22 at 09:18
Thank you PEH, that works !! however it only does one value at a time, is there way to do for whole column ? e.g all in Column A, do I have to put a counter loop ? — Tartans, May 11 '22 at 09:35
Do some research on how to use `.FindNext` or see [Find and FindNext for Excel VBA](https://stackoverflow.com/questions/30380490/find-and-findnext-for-excel-vba) — Pᴇʜ, May 11 '22 at 12:34

score 0 · Answer 1 · answered May 11 '22 at 09:23

You can use wildcards only in the find pattern but not in the replace pattern. You need to use Selection.Find instead of .Replace. And then split the found string to replace the 7 with a 5.

Dim FoundAt As Range
Set FoundAt = Selection.Find(What:=("??????7?-fsa"), _
    LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
    False)
    
If Not FoundAt Is Nothing Then
    FoundAt.Value = Left$(FoundAt.Value, 6) & "5" & Right$(FoundAt.Value, 5)
End If

Alternatively you can do that with regular expressions: How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

find and replace using WildCard

1 Answers1