Need to extract specific XML data from a cell in excel

Closed

alvin07 Posts 1 Registration date Tuesday January 7, 2014 Status Member Last seen January 7, 2014 - Jan 7, 2014 at 12:38 PM
SM - Apr 1, 2019 at 05:59 AM

Hi,
I have the following data in cell A1 of an excel sheet:

<IntegrationId>392110000112001001</IntegrationId><ProductStoreInfo><AccountProduct><StoreId>00019</StoreId><ActiveToDate/>

i would like to extract the <StoreId> value from this and print it on the adjacent cell.
i would like 00019 printed on the cell B1.

any help would be much appreciated.

regards,
Alvin

Need to extract specific XML data from a cell in excel
Transfer data from one excel worksheet to another automatically - Guide
Number to words in excel - Guide
How to find specific words on a page - Guide
Notepad++ xml plugin - Guide
Tmobile data check - Guide

1 response

Answer 1 / 1

venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Jan 9, 2014 at 12:37 AM

In B1 type the formla

=SEARCH("storeid",A1)

C1 the formula is

=SEARCH("storeid",A1,B1+1)

in D1 the formula is

=MID(A1,B1+LEN("storeid")+1,C1-B1-LEN("storeid")-3)

D1 will give you what you want,. (00019)

It is possible to combine all the three formula and give one formula
but this is easier to debug.

SM
Apr 1, 2019 at 05:59 AM

Thank you so much for this formula. I have been trying to solve an issue for a couple of days now - after a lot of struggle I landed up on this page and your formulas worked great. One request - could you also explain what your formulas are doing on each column - it did not make a lot of sense to me.

Similar discussions

Highlight cell if another cell contains specific text

Unlock safe folder without losing any data

How to use conditional format based on text?

Similar discussions

Subscribe To Our Newsletter!