4

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.

5

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")
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.