2

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
dickinsonk9
  • 43
  • 6
  • This gives me the same response as posted. `gold--blue-glass-ornament-collection-set-of-3` – dickinsonk9 Oct 24 '22 at 19:59
  • I have added an image to for visual representation instead of just words. – dickinsonk9 Oct 24 '22 at 20:07
  • 1
    I would've done something like this `=LOWER(REGEXREPLACE(A5,"\W+","-"))` and also another function to remove extra dashes from each end. – chuthan20 Oct 24 '22 at 20:19
  • 1
    For future reference, always provide 5-10 inputs and expected outputs as a markdown table. – TheMaster Oct 24 '22 at 20:36
  • 1
    Make sure to add input and expected output as **text table** (NOT as IMAGE/LINK) to the question. [Click here](https://webapps.stackexchange.com/a/161855/) to create a table easily. Adding such tables greatly increases your chances of getting a elegant answer, as **it is easier to copy/paste**. If you share spreadsheets, your question maybe closed, as questions here must be [self contained](https://meta.stackoverflow.com/a/260455). Your table should be a [mre].[Your email address can also be accessed by the public](https://meta.stackoverflow.com/questions/394304/), if you share Google files. – TheMaster Oct 24 '22 at 20:36
  • 1
    Thank you for the updates. I have included a table with five examples, with four of them showing as per the original formula I used. – dickinsonk9 Oct 24 '22 at 21:00

2 Answers2

3

try:

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

some stuff needs to be escaped with \

and maybe one more wrapper:

=LOWER(SUBSTITUTE(REGEXREPLACE(SUBSTITUTE(C2, " ", "-"), "[&\(\)\/']", ), "--", "-")
player0
  • 124,011
  • 12
  • 67
  • 124
  • 1
    Thank you. I tried the first one, but didn't think about trying another SUBSTITUTE function. Gosh. I totally missed that. The second one worked for my purpose! – dickinsonk9 Oct 24 '22 at 20:09
3
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,"[&()/ ']+","-"))
TheMaster
  • 45,448
  • 6
  • 62
  • 85
  • I appreciate your edits - I've tried each of your methods, and on an independent sheet with only my example, it works. With the sheet I'm using it on, the third solution provides me with `gold-blue-glass-ornament-collection-set-of-3-` where there is an extra hyphen at the end. I checked the title for any extra space, and there was none. (I also included the backslashes before the amp, parens.) For the lines with an apostrophe, it adds a hyphen there (where its not needed). – dickinsonk9 Oct 24 '22 at 20:25
  • 1
    @dickinsonk9 [Backslashes are not needed here](https://stackoverflow.com/questions/399078/what-special-characters-must-be-escaped-in-regular-expressions). The hyphen is present, because it replaces the last `)` with `-`. The substitute one shouldn't add the extra hyphen: `=REGEXREPLACE(SUBSTITUTE(C2, " ", "-"),"[&()/']-",)`. – TheMaster Oct 24 '22 at 20:27