Previous Topic Next Topic

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
Related:

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
Abbi -
vcoolio -

1 response
Unlock safe folder without losing any data
Mintu -
HelpiOS -

1 response
How To Fix Mint Mobile Data Not Working?
sachin327 -
joshitha -

2 responses