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

 

[Alex0013]

Hey Jonmo,

This seems to work great as well! I can figure out exactly what's going on in this version as well so I can manipulate it further to add a few other things I already know how to do to finish my requirements. I might be able to do that one with the RegExp as well if I knew what it was doing

Anyways, thank you both very much for your time!

Alex

 

[Rick Rothstein]

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

It can be written without using RegExp easily enough...

Function AddPlus(S As String, sAdd As String) As String
  Dim X As Long, Txt() As String
  Txt = Split(S, "#")
  For X = 1 To UBound(Txt)
    Txt(X) = Left(Txt(X), 6) & sAdd & Mid(Txt(X), 7)
  Next
  AddPlus = Join(Txt, "#")
End Function

 

[pgc01]

Would you be willing to explain a little of it for me? Just so I understand exactly what it's doing?
... just trying to understand VBA better!

Hi

I used a regular expression. In this case it's not about learning vba, it's about learning Regular Expressions.

Regular expressions have been around for more than 35 years, so you'll find easily lots of information about it.

Google "Regular expression" and you'll get many million hits.

Anyway, this page will give you a small introduction:

Regular expressions - An introduction

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

You said you wanted 6 hex digits after the #.
[0-9a-fA-F] means any character between 0 and 9 or between a and z or between A and Z. This is the definition of a hex digit.

.Global=True

means that you want to perform all the possible replacements. With .Global=False (the default) the code would only make 1 substitution.

"$&" is the code for the match.

Hope this helps.

 

[pgc01]

It can be written without using RegExp easily enough...

Function AddPlus(S As String, sAdd As String) As String
  Dim X As Long, Txt() As String
  Txt = Split(S, "#")
  For X = 1 To UBound(Txt)
    Txt(X) = Left(Txt(X), 6) & sAdd & Mid(Txt(X), 7)
  Next
  AddPlus = Join(Txt, "#")
End Function

Hi Rick

Just a remark.
It can indeed, but in this case your code is not checking if after the # you have 6 hex digits, which is what I understood this string is.
You can, of course, add a Like statement to check it.

 

[Rick Rothstein]

Hi Rick

It can indeed, but in this case your code is not checking if after the # you have 6 hex digits, which is what I understood this string is.
You can, of course, add a Like statement to check it.

You are right, so let's do that...

Function AddPlus(S As String, sAdd As String) As String
  Dim X As Long, Txt() As String
  Txt = Split(S, "#")
  For X = 1 To UBound(Txt)
    If Txt(X) Like Application.Rept("[0-9A-Fa-f]", 6) & "[B][COLOR="#FF0000"][!0-9A-Za-z][/COLOR][/B]*" Then
      Txt(X) = Left(Txt(X), 6) & sAdd & Mid(Txt(X), 7)
    End If
  Next
  AddPlus = Join(Txt, "#")
End Function

The results from my code can possibly differ from yours, though, as I decided to treat the character immediately after the sixth hex digit differently than you did. It probably won't come up in the OP's actual data, so both our codes will probably work identically for him, however, I decide placing the sAdd text immediately after the sixth hex digit might not always be correct. If the text in the cell were something like this...

...#12AB3CXYZ...

your code would output this...

...#12AB3C>XYZ...

where I decided to not touch it at all... my code will only add the sAdd text if the seventh characters is not a number or letter. The restriction can easily be remove by eliminating the part I highlighted in red above if the OP wants, but thought extra letters (any letters) or numbers after the sixth digit would disqualify the value as a six-digit hex value.