Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: Concatenate adding quote marks

3419 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Matt_Frederick
6 - Interface Innovator
6 - Interface Innovator

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:

Screen Shot 2020-12-09 at 12.02.54 PM

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!

10 Replies 10
augmented
10 - Mercury
10 - Mercury

Hey Matt, the only way that I can make the double quotes appear is to have the field values inside double quotes.

image

Otherwise I get this…

image

And if I use this formula,

CONCATENATE(Text1,"/",Text2)

I get this

image

Did I miss something from your questions? I must have.

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?

Screen Shot 2020-12-10 at 2.11.44 PM

Yes. Sorry, I simplified my example field names. Does it work if you put the curly braces back in your new formula?

No, have tried every version with and without so maybe its not syntax related?

Screen Shot 2020-12-10 at 2.35.11 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 PhotographyScreen Shot 2020-12-10 at 2.37.26 PM

No, you always need double quotes around the backslashes. Did you try it with curly braces around the fields and double quoted backslashes?

image

image

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.

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}), '" ', ""), ' "', "")

Thanks Justin, the fields are links to tables that are all set up the same way.

Screen Shot 2020-12-11 at 8.16.36 AM

Screen Shot 2020-12-11 at 8.18.40 AM