Question 22349 in SEO Question posted in
Some basic information to get you started with this can be found here.

RegEx to Double Hyphens from URL – SEO

I’ve created a Google Spreadsheet that helps assist me in creating products for my company’s website. We have a field that allows us to create the seo keyword part of the URL in relation to that particular product. So, ideally, the url would look like:
www.example.com/gold-blue-glass-ornament-collection-set-of-3.

Excluding the domain, the spreadsheet only needs the SEO Keyword which is this:
gold-blue-glass-ornament-collection-set-of-3. I would like to be able to filter out multiple characters, such as the ampersand, parenthesis, apostrophes, and double hyphens. I’ve completed all but the last one and I am stuck with this last bit.

My formula is:

LOWER(REGEXREPLACE(SUBSTITUTE(C2, " ", "-"),"[&()/’]",""))

The information currently comes from an adjacent column with the title of the product. Title being in this instance: Gold & Blue Glass Ornament Collection (Set of 3). I have tried multiple variations of the RegEx, and the substitute which ends up with something like:
gold--blue-glass-ornament-collection-set-of-3 where the ampersand gives me a double hyphen.

Any suggestions?

EDIT VISUAL ADDITION

google sheets example of output

EDIT: I didn’t realize my quote didn’t put my original escape characters in the initial formula, so this has been updated!

Third Edit: Since I’m a newbie in terms of writing questions:

Input Output Desired Output
Gold & Blue Glass Ornament Collection (Set of 3) gold–blue-glass-ornament-collection-set-of-3 gold-blue-glass-ornament-collection-set-of-3
Poppies Glass Ornament Collection (Set of 3) poppies-glass-ornament-collection-set-of-3
Calla Lilies Glass Ornament Collection (Set of 3) calla-lilies-glass-ornament-collection-set-of-3
The Flamingoes Glass Ornament Collection (Set of 3) the-flamingoes-glass-ornament-collection-set-of-3
Japanese Bridge Glass Ornament Collection (Set of 3) japanese-bridge-glass-ornament-collection-set-of-3
Van Gogh’s Specialty Glass Ornament Collection (Set of 3) van-goghs-glass-ornament-collection-set-of-3
Tags:

2

Answers


  1. 0

    try:

    =LOWER(REGEXREPLACE(SUBSTITUTE(C2, " ", "-"), "[&()/']", ))

    some stuff needs to be escaped with

    and maybe one more wrapper:

    =LOWER(SUBSTITUTE(REGEXREPLACE(SUBSTITUTE(C2, " ", "-"), "[&()/']", ), "--", "-")
    Login or Signup to reply.
  2. 0 
    Gold & Blue Glass Ornament Collection (Set of 3)

    after first substitute becomes:

    Gold-&-Blue-Glass-Ornament-Collection-(Set-of-3)

    When [&()/'] is used, it removes &()/', but -&- becomes --. To avoid this, just add - at the end:

    [&()/']-

    As a formula:

    =REGEXREPLACE(SUBSTITUTE(C2, " ", "-"),"[&()/']-",)

    Without SUBSTITUTE, you can also use

    [&()/' ]+

    One or more(+) of & or () or / or (space) or ' to be replaced with one -

    =LOWER(REGEXREPLACE(C2,"[&()/ ']+","-"))
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search