Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Dec 09, 2020 12:05 PM
This has been asked a few times but I can’t seem to find the solution that works.
I have a formula "CONCATENATE({For Formula},{Market},{Product Management},{Product Category},{Gender})"
but its output adds unwanted quote marks like so:
What I really want is to have it be separated by “/” but I’ve exhausted the time I have trying to fix that. How can I get this to output something with consistent quote marks - or comma separation - or preferably the /
Thanks!
Dec 10, 2020 12:32 PM
Hey Matt, the only way that I can make the double quotes appear is to have the field values inside double quotes.
Otherwise I get this…
And if I use this formula,
CONCATENATE(Text1,"/",Text2)
I get this
Did I miss something from your questions? I must have.
Dec 10, 2020 02:14 PM
Thanks Augmented, yeah have tried that but the formula won’t run at all without the brackets or quote marks. Maybe due to the spaces in the Field names?
Dec 10, 2020 02:16 PM
Yes. Sorry, I simplified my example field names. Does it work if you put the curly braces back in your new formula?
Dec 10, 2020 02:36 PM
No, have tried every version with and without so maybe its not syntax related?
Dec 10, 2020 02:37 PM
What’s odd is that when it does work with the quotes, even that is not consistent - note there’s no lead-in quote after Photography
Dec 10, 2020 04:24 PM
No, you always need double quotes around the backslashes. Did you try it with curly braces around the fields and double quoted backslashes?
Dec 10, 2020 04:50 PM
Yes, have tried combinations of quotes, double quotes, single quotes, etc… Out on location the next few days so will try again fresh next week. Thanks for the help and ideas.
Dec 10, 2020 11:21 PM
Regarding the extraneous quotes, what type of fields are these? Something tells me that the field type will provide a clue to the quotes (and the spaces inside the quotes). My gut says that there are some linked records and/or lookup/rollup fields in the mix somewhere.
That aside, you can always strip out the quotes (and their adjacent spaces) after the concatenation is complete by using the SUBSTITUTE()
function a couple times. Try this:
SUBSTITUTE(SUBSTITUTE(CONCATENATE({For Formula},"/",{Market},"/",{Product Management},"/",{Product Category},"/",{Gender}), '" ', ""), ' "', "")
Dec 11, 2020 08:19 AM
Thanks Justin, the fields are links to tables that are all set up the same way.