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
Excel conditional formatting if another cell contains specific text ✓ - Excel Forum
How to change date format in excel - Guide
Extract audio from video - Guide
If cell contains specific text then return value in another cell vba ✓ - Excel Forum

1 reply

Answer 1 / 1

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
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.

Subscribe To Our Newsletter!