Regex to find a pattern, then insert into the pattern

Question

Using Excel VBA

I'm not sure if you can do this with regex but what i'm trying to do is for a sting like

"ThisIsAn ExampleString"

Find each instance of a lowercase letter followed immediately by an uppercase letter, and then insert a pipe into it.

So the final result would be like

"This|Is|An Example|String"

So I'm guessing the pattern is "[a-z][A-Z]"

I'm starting to think i'm barking up the wrong tree with regex and should just try some sort of function.

Edit:

Thanks to all who gave answers below, you have given me the solution.

For anyone else reading this, this is what I ended up with:

Sub PipeInsert()

    Dim cell As Range
    For Each cell In Selection
        cell.Value = ReplaceTest(cell.Value, "([a-z])([A-Z])", "$1|$2")
    Next

End Sub


Function ReplaceTest(str1, patrn, replStr)
    Dim regEx

    ' Create regular expression.
    Set regEx = New RegExp
    regEx.Pattern = patrn
    regEx.Global = True

    ' Make replacement.
    ReplaceTest = regEx.Replace(str1, replStr)
End Function

FYI you can make your questions easier to read by formatting the text, especially code, using the markup options available to you. (Back ticks and 4-space indentations for code, etc) — wally, Oct 08 '15 at 16:30

score 4 · Accepted Answer · edited May 23 '17 at 11:52

4

Regex replace is fine :)

=regex("ThisIsAn ExampleString", "([a-z])([A-Z])", "$1|$2")

https://regex101.com/r/zF4mM5/1

_{I grabbed the =regex() function from this answer - never worked with Excel VBA before...}

edited May 23 '17 at 11:52

Community

1
1

answered Oct 08 '15 at 16:22

ʰᵈˑ

11,279
3
26
49

You posted as I was typing - perfectly confirms what I was theorising! Thanks. – wally Oct 08 '15 at 16:28
Won't that just replace the whole string with "s|I". I can see the solution involves this method but I don't think this is quite there. I guess I need to look for an 'in place' replacement version of this. – Coder375 Oct 08 '15 at 17:22

score 1 · Answer 2 · answered Oct 08 '15 at 16:27

You'll be using a function in your chosen language (Excel VBA in this situation) - which I know nothing about. I assume it has the ability to match and to replace using regular expressions...

If it can perform replacements, you'll want something like this:

s/([a-z])([A-Z])/\1|\2/g

g=Globally
s=Replace
\1 and \2 refer to the bracketed groups from the left-hand-side.

Having a quick google gives me the impression VB macros have a "RegExp" class available to them - which can perform replacements:

https://msdn.microsoft.com/en-us/library/xwewhkd1(v=vs.110).aspx?cs-save-lang=1&cs-lang=vb#code-snippet-1

Dim rgx As New Regex(pattern)
Dim result As String = rgx.Replace(input, replacement)

pattern would be ([a-z])([A-Z]) in my proposal; and replacement would be $1|$2

I don't have Excel available on this machine, this is purely theoretical!

score 0 · Answer 3 · answered Oct 08 '15 at 16:27

0

You can use lookarounds here.

(?<=[a-z])(?=[A-Z])

Replace by |.See demo.

https://regex101.com/r/cJ6zQ3/46

answered Oct 08 '15 at 16:27

vks

67,027
10
91
124

Regex to find a pattern, then insert into the pattern

3 Answers3