Substitute with Wildcards

[Alex0013]

Hey All,

I'm wondering if someone would be able to help me with a UDF. What I'm trying to do is basically have an Excel Substitute formula that takes wildcards.

Example:

A1: example#00ff00ColorTextHere

=SubstituteWild(A1,"#??????","#??????>")

In my example, I essentially want to insert a ">" symbol after each hex color code in my string. "A1" might have several hex codes in the 1 cell, so I can't just use mid functions, I want to use substitute so it evaluates the whole string.

I'm guessing the UDF would have to find "#", and then look at the next 6 digits, store them, then replace them again with the new "format".

Ideally, making it dynamic so it can be used elsewhere would be awesome (aka, make it treat "?" as a true wildcard), but hard coding everything to always assume hex codes and 6 characters would at least get me by for this project.

Any ideas?

Thanks!
Alex

 

[Jonmo1]

Try

=REPLACE(A1,SEARCH("#",A1)+7,0,">")

 

[Alex0013]

Hey Jonmo1,

Wow, that was super quick! This will do the trick! Super easy way to solve my issue at least, so it works for me!

Enjoy the rest of your day!
Alex

 

[Alex0013]

Actually...Sorry I spoke to soon. It only works on the first instance of #. It didn't work if it happens multiple times

 

[Jonmo1]

Ahh, ok. Not sure how to do that.

Substitute doesn't work with wildcards (as you've discovered).

 

[Alex0013]

Yeah, that's why I'm hoping a UDF pro might be able to help me. I'm trying to learn VBA a bit myself, but this one is stumping me...

 

[pgc01]

Hi Alex

Try:

Function AddPlus(s As String, sAdd As String)

With CreateObject("VBScript.RegExp")
    .Pattern = "#[0-9a-fA-F]{6}"
    .Global = True
    AddPlus = .Replace(s, "$&" & sAdd)
End With
End Function

Use like:

=AddPlus(A2,">")

 

[Jonmo1]

That's much better than what I was working on.
I haven't gotten a grip on RegExp yet.

 

[Alex0013]

Hey PGC,

That works great! Would you be willing to explain a little of it for me? Just so I understand exactly what it's doing?

What is the [0-9a-fA-F]? Also what does .Global=True do? And lastly, the "$&"?

Sorry for the questions, just trying to understand VBA better!

Thanks,
Alex

 

[Jonmo1]

Not that it matters anymore, but here's what I did.
Maybe at least it will be easier to understand.

Public Function MyFind(c As Variant)
Dim MyStr As String, x As Long
MyStr = c
Do
    x = InStr(1 + x, MyStr, "#")
    If x = 0 Then Exit Do
    MyStr = Application.Replace(MyStr, x + 7, 0, ">")
Loop
MyFind = MyStr
End Function

Then use in a formula as
=MyFind(A1)