Excel Convert XML cell to columns

I have an excel spreadsheet with XML in a single column. I know that you can import XML data into Excel; but only imports the entire file. However, I need to do this on a cell by cell basis.

With VBA you could probably do it, but is there a stock way of doing this in Excel?

EDIT #1:

To be clear, I haven't written any VBA code to do this, but I can manage that myself. My question is whether or not this is existing functionality like "Text to Columns". So far I've attempted Googling but only found examples of importing entire XML files.

Carlos Bribiescas

Posted 2014-04-22T15:03:13.377

Reputation: 185

the link you posted (http://office.microsoft.com/en-us/excel-help/import-xml-data-HP010206405.aspx) tells you about data maps. AFAIK, you just specify the schema and can pick and choose what you import in each cell you don't have to import the whole file

– seanv507 – 2016-12-27T13:53:53.053

1Not sure why this was voted down but if you could elaborate I would appreciate it... – Carlos Bribiescas – 2014-04-22T16:21:34.750

1I don't know for sure why it received a down vote, but I can tell you it may have been because there little evidence of an effort on your part to attempt resolving this. If you [edit] your question with details of what you've tried so far, examples of code you've attempted, etc., it will improve the quality of the question. – CharlieRB – 2014-04-22T16:27:49.977

http://office.microsoft.com/en-gb/excel-help/import-xml-data-HP010206405.aspx --- http://support.microsoft.com/kb/305795 --- http://stackoverflow.com/questions/2631544/quick-convert-xml-to-excel – Dave – 2014-04-23T14:21:12.087

Answers

Have a look at the newest stock formulas for Excel 2013: =WEBSERVICE and =FILTERXML

Here is an example: In cell A1 place the following webservice formula. It will retrieve the latest weather for John F Kennedy airport.

=WEBSERVICE("http://www.aviationweather.gov/adds/dataserver_current/httpparam?dataSource=metars&requestType=retrieve&format=xml&hoursBeforeNow=3&mostRecent=true&stationString=KJFK")

In cell A2 enter the following formula. It will parse the XML data using XPATH to get the station identifier KJFK. Using XPATH you should be able to extract any portion of the XML you want.

=FILTERXML(A1,"/response/data/METAR/station_id")

wbeard52

Posted 2014-04-22T15:03:13.377

Reputation: 3 149

I would "Save As" the XML sheet to a Text file (e.g File / Save & Send / Change File Type / Text (tab-delimited) (*.txt) ).

Then I would use Windows Explorer to change the file extension to .xml. Then you can import it into Excel using the Data ribbon / From Other Sources / From XML Data Import.

Mike Honey

Posted 2014-04-22T15:03:13.377

Reputation: 2 119