how to force $1 to be a literal instead of submatch when using regexp.replace method?

Posted on 2011-10-30
Last Modified: 2012-05-12
This is mainly for the regular expressions zone.

Line1 below works exactly as expected. It replaces the pattern with the text "works fine"
But, line2 fails, because it interprets the $1 as submatch #1 which is the word "quick".

line1: MsgBox RegExpReplace("the quick brown fox", "(quick|fast) brown", "works fine")
line2: MsgBox RegExpReplace("the quick brown fox", "(quick|fast) brown", "See Cell $A$1")

I tried using the standard escape character  "$A\$1" but that did not work.

I came up with the following workaround, but it is ugly, and I am sure there is a better way

msgbox replace(RegExpReplace("the quick brown fox", "(quick|fast) brown", "xxxxxx"),"xxxxxx","See Cell $A$1")


Question by:rberke
    LVL 74

    Accepted Solution

    Try doubling-up the dollar symbol:  $$1.
    LVL 92

    Expert Comment

    by:Patrick Matthews
    That should work.  At least it did when I tested it just now :)

    Adding that as a comment to, which is where that function came from...
    LVL 5

    Author Closing Comment


    I knew there would be an easy way.

    LVL 5

    Author Comment

    and thanks again to mathewspatrick for his wonderful function which I use every day.


    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    I spend far too much time on the web keeping up with the news: politics, the environment, computer stuff, the Experts Exchange. It's never-ending. But many of the most informative web pages are overwhelmed with noise: scrolling banners, flashing tex…
    It's here again; Microsoft is launching a new version of Internet Explorer: Internet Explorer 9, with noticeable changes on its interface, functions and new tools. As they say on its promotional video: "It's time to play, on a more beautiful web", f…
    This Micro Tutorial will demonstrate how to add subdomains to your content reports. This can be very importing in having a site with multiple subdomains.
    This Micro Tutorial will demonstrate how nuggets on the Web are formatted by using Chrome Developer Tools. These tools would not only view the site's CSS but it can also modify it and save the CSS to use on your own site.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now