How turn a cell into proper XML exportation?

jbesclapez

Board Regular
Joined
Feb 6, 2010
Messages
235
Hi there,

I am using excel to create an XML file.
I am getting a problem I can not solve easily: How to convert a cell that contains text with single and double quotes to my XML. I looked at <![CDATA[ but cannot make it work for me. As an example is usually better than words, here it is:

Here is the data i want to send for expression
'ThisIsMyExpression
api.Message(si,"Check this message")

Here is what the system is expecting :
<Expression="'ThisisMyExpression&#xD;&#xA;api.Message(&quot;test&quot;)"/>

In excel it looks like this
1603716048364.png

And my formula in excel is
="<Expression="&""""&" "&G4&""""&" />"

As i mentioned previous I tried to use ![CDATA[ but without successs too.

Thanks for your help
 

Attachments

  • 1603715983321.png
    1603715983321.png
    5.6 KB · Views: 1
  • 1603716035335.png
    1603716035335.png
    5.6 KB · Views: 1

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

jbesclapez

Board Regular
Joined
Feb 6, 2010
Messages
235
Hi guys,
I found a workaround using directly an excel formula that will spot the unauthorized characters and swap them with what I need.
Here is the formula i use:
TRIM(SUBSTITUTE(SUBSTITUTE(G4,CHAR(34),"&quot;"),CHAR(10),"&#xD;&#xA;"))
Did not know we could find an ENTRY in excel...
Cheeers
 

Watch MrExcel Video

Forum statistics

Threads
1,127,420
Messages
5,624,693
Members
416,041
Latest member
behnejm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top