Previous Topic Next Topic

Need to extract specific XML data from a cell in excel

Closed
alvin07 Posts 1 Registration date Tuesday 7 January 2014 Status Member Last seen 7 January 2014 - 7 Jan 2014 à 12:38
 SM - 1 Apr 2019 à 05:59
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 response

Réponse 1 / 1
venkat1926 Posts 1863 Registration date Sunday 14 June 2009 Status Contributor Last seen 7 August 2021 811
9 Jan 2014 à 00:37
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
1 Apr 2019 à 05:59
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

How to extract data from one excel file to another
zamorin -
zamorin -

2 responses
How do I extract specific data from a cell in excel?
mcat79 -
ac3mark -

5 responses
Copying data from one Excel sheet to another.
Remedial -
Kav -

30 responses
Extract data from one sheet to another
phanikr -
Mazzaropi -

1 response