SOLVED: Find a XML tag in a Cell and return corresponding value in adjacent cell

[Praneeth30]

Hi,
I am looking for a formula which gives me the XML tag value.

I have a XML as below in a cell (3,A) of attached sheet now i have to find <DIKY_CAE_CLM_ID> tag from this cell and get its corresponding value '1008001438' and display it in cell (3,B).

<DIKY_CLM_SYS_ID>COSM</DIKY_CLM_SYS_ID>
<DIKY_INVN_CTL_NBR>1008001438</DIKY_INVN_CTL_NBR>
<DIKY_PROCESS_FLOW_KEY>ACCMAINT</DIKY_PROCESS_FLOW_KEY>
<DIKY_SUFFIX_CD>01</DIKY_SUFFIX_CD>
<DIKY_GLOBL_SYS_ID>1001</DIKY_GLOBL_SYS_ID>
<DIKY_RESP_SYS_ID>1001</DIKY_RESP_SYS_ID>
<DIKY_CAE_CLM_ID>1008001438</DIKY_CAE_CLM_ID>
<DIKY_CAE_CLM_SUB_ID>01</DIKY_CAE_CLM_SUB_ID>
<DIKY_EVNT_CD>00</DIKY_EVNT_CD>
<DIKY_TRANS_ID>00</DIKY_TRANS_ID>
</CHCOMMST_REC>

The value in DIKY_CAE_CLM_ID could be of 35 bytes at maximum. Please assist here.

 

[Marc L]

Hi !

C3 formula : =FIND($B$2 & ">",A3)+LEN($B$2)+1

B3 formula : =MID(A3,C3,FIND("<",A3,C3)-C3)

Do you like it ? So thanks to click on bottom right Like !
​

 

[Praneeth30]

Hey Marc, Thanks its working and i also tried the below formula which also worked.

=MID(A3,FIND("DIKY_CAE_CLM_ID",A3)+16,FIND("DIKY_CAE_CLM_ID",RIGHT(A3,LEN(A3)-FIND("DIKY_CAE_CLM_ID",A3)))-18)

Pls mark this post as solved.