vba proc for a replace function using wildcard

I require vba proc for a replace function to be able to specify a find string using a known prefix & a known suffix with a wildcard for the unknown portion (wildcard will always be between the prefix and suffix).

And replace all instances of find criteria within text and replace with replace text (in this example replace text = nothing)
The find string may exist 1, many or no time within the text string.

Ideally I would like the macro to accommodate eith * or ? wildcards.

Text = “abc 1234 def xyz abc 1234 def”
Replace = “”


Eg 1
Find = “bc*de”
Result = “abcdef xyz abcdef”

Eg 2
Find = “bc 1*4 de”
Result = “abc 14 def xyz abc 14 def”

Eg 3
Find = “bc 1?34 de”
Result = “abc 134 def xyz abc 134 def”

Eg 4
Find = “bc 1??? de”
Result = “abc 1 def xyz abc 1 def”

Eg 5
Find = “bc ?? de”
Result = “abc 1234 def xyz abc 1234 def” (match not found)

Any help would be appreciated

Hello wotadude,

Your best option is to use Regular Expressions. If you can post a sample of the data and the types of matches and replacements you want, I can help you with the coding.

Hello wotadude,

Thanks for the files. After looking at the your post and the workbook, I am going to use the matching method in your post because it is consistent with the wildcard meanings. This is an interesting challenge.

Hello wotadude,

Thanks again for the bump. I need you to clarify how to interpret the wild card characters. This post and the workbook use the characters differently. The standard meaning of "?" is to match any single character and the asterisk "*" is zero or more characters. The number or pound sign "#" is used to match a single digit.