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
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 |
CodePudding user response:
try:
=LOWER(REGEXREPLACE(SUBSTITUTE(C2, " ", "-"), "[&\(\)\/']", ))
some stuff needs to be escaped with \
and maybe one more wrapper:
=LOWER(SUBSTITUTE(REGEXREPLACE(SUBSTITUTE(C2, " ", "-"), "[&\(\)\/']", ), "--", "-")
CodePudding user response:
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,"[&()/ '] ","-"))