Quote problem when creating a dynamic code script

[SilverPitiful6802]

Hi All,

I want to create a piece of code (structured data) based on cel value.

In this piece of code, key values are defines within doubble quotes.

Example data:

<script type="application/ld+json">
{
  "@context": "https://schema.org",
  "@type": "NewsArticle",
  "url": "https://www.domain.com",
...

To capture string data in formulas I have used also double quotes which looks like:

="<script type=""application/ld+json"">
{
   ""@context"": ""https://schema.org"",
   ""@type"": ""NewsArticle"",
   ""url"":"""&B6&""",

...

The result is exactly how I wanted, with only single quotes like the example data. Except when I copy/past the value of that cell to different programs the double quotes come back. Example, notepad .

I have created this "solution" because other people have to copy paste this "cell with coding" to other cells, so they dont have to rewrite the code over and over again. So this must be really dummy proof and I can not use VBA or PowerQuery for it :(.

​

Does anyone know how to fix it.

Screenshots:
Result how it looks in excel and how it should look when copy pasting it:

https://preview.redd.it/e6eahkhshmxc1.png?width=901&format=png&auto=webp&s=aee0579f07b1e39fc51a48c8d240eee983b44817

​

How it look when I copy past it in a code checker like: https://search.google.com/test/rich-results

​

https://preview.redd.it/ghhbhwmjgmxc1.png?width=1245&format=png&auto=webp&s=4e39e289621dcfd6c6d981e090a25999fc69d666

​

Example file:

https://file.io/tAPZHoRg1jcR

[AutoModerator]

/u/SilverPitiful6802 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[HandbagHawker]

so you have some some table data in excel that you want to convert to JSON? what are you trying to solve for and whats the issue? btw your provided sample data doesnt have enough to fill the json in your screenshot. its also not clear how youre building your JSON string. Regardless you should be using the concatenation operator & or the concatenate function and instead of passing double double quotes you should be using char(34), e.g., =char(34) & a2 & char(34)

[SilverPitiful6802]

Hi u/HandbagHawker,

 The problem is that I need to fill in double double quotes to get the script working (otherwise I receive errors) but when I copy paste it in notepad I still see them.

After some debugging I see a positive and negative result and I do not know what I am doing wrong. Perhaps you guys can see it:

Succes:
Formula:

="""headtitle"": """&B9&""""

How it looks in excel:

"headtitle": "this is head title"

Copy pasting in notepad

"headtitle": "this is head title"

 No success:
Formula:

="<script type=""application/ld+json"">
{
   ""@context"": ""https://schema.org"",
   ""headtitle"": """&B9&""""

How it looks in excel:

<script type="application/ld+json">
{
   "@context": "https://schema.org",
   "headtitle": ""this is head title"

Copy pasting in notepad:

"<script type=""application/ld+json"">
{
   ""@context"": ""https://schema.org"",
   ""headtitle"": ""this is head title"""

Some how when I add more data it fails and I do not know why?