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

1 reply

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