Extract 5th string onwards but exclude 6th string onwards if found a specificlist of words

Hi

Would like to extract the 5th string onwards but need to exclude the 6th string onwards only if the 6th word has DMZ, DB, WEB, APP, HOT, COLD. For example

C2_F6_AM_SHS_GROUPGST_DB Result: GROUPGST
C2_F6_AM_SHS_ECLAIM Result: ECLAIM
C2_F6_AM_SHS_EQUIBASE Result: EQUIBASE
C2_F6_AM_SHS_TREX_APP Result: TREX
C2_F6_AM_CAD_STARS_DMZ Result: STARS
C3_F2_AD_SHS_ORMS_HOT Result: ORMS
C3_F4_AD_SHS_ORMS_COLD Result: ORMS
C3_F4_AD_SHS_FINSS_AMLA_ETL Result: FINSS_AMLA_ETL
C1_F2_AD_WBP_TIPLUS_HOT_B4EOD Result: TIPLUS
C0_F2_AU_CB_AMBGAIX85_DB_FS Result: AMBGAIX85

I used this formula =MID(J46,SEARCH("_",J46,SEARCH("_",J46))+11,256), but unable to trim the 6th string if found the list of words in the same formula.

Can anyone please advise. Thank you.

=TRIM(MID(SUBSTITUTE(A1,"_",REPT(" ",LEN(A1))),(5-1)*LEN(A1)+1,LEN(A1)))

change A1 with your actual cell reference

Hi,
The formula from NeedForExcel works because it did not exclude from C3_F4_AD_SHS_FINSS_AMLA_ETL if found DMZ, DB, WEB, APP, HOT, COLD at the 6th string.

But this formula did not work. if found WEB in the 5th string without an underscore, it should not exclude. _DMZ, _DB, _WEB, _APP, _HOT, _COLD will always comes after an underscore. Example:

C2_F3_AM_GIN_AMGWEB : This Formula did not work. Result should be AMGWEB because no underscore.

C2_F3_AM_REB_ISENTRIC_WEB : Formula worked as the result shows ISENTRIC. Reason is it has an underscore _WEB
C3_F4_AD_SHS_ORMS_COLD_B4EOD : Formula worked as the result shows ORMS. Reason is _COLD onwards has to be trimmed.

Not really. I'm just informing that all these words will comes with an underscore before or after.

Thank you all for the contribution. Formula form Hemesh worked.

The formula is really complex which I need time to understand.

Hi nflsales,
Sorry for the late reply. The formula worked but it did not retain the 6th string if it's not part of the list of {"dmz","db","web","app","hot","cold"}.

Example
C3_F4_AD_SHS_ORMS_B4EOD_COLD, result should be ORMS_B4EOD but displays ORMS_B4EOD_COLD
C3_F4_AD_SHS_ORMS_COLD_B4EOD, formula worked where it shows ORMS

Meaning after the 6th string, if these {"dmz","db","web","app","hot","cold"} are not exist, then it should retain otherwise remove from that word onwards.