Need to extract specific XML data from a cell in excel [Closed]

Posts
1
Registration date
Tuesday January 7, 2014
Status
Member
Last seen
January 7, 2014
- - Latest reply:  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
See more 

1 reply

Best answer
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
784
5
Thank you
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.

Say "Thank you" 5

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM 6114 users have said thank you to us this month

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.