Excel Convert XML cell to columns

microsoft excelworksheet-functionxml

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.

Best Answer

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")
Related Question