how to extract text between various hyphens or underscores

Hi,

How do i extract each word out individually from cell H2? if cell H2 is "Amenities_BackRest_DisabledToilet.rfa" and H3 is "Amenities_HandDryer.rfa" etc etc

As an example I want":
cell I2 to show "Amenities"
column J2 to show "Backrest"
column K2 to show "DisabledToilet" without the .rfa

Regards,

Michael

I2: =SUBSTITUTE(TRIM(MID(SUBSTITUTE($H2,"_",REPT(" ",99)),COLUMNS($I:I)*99-98,99)),".rfa",)

copy across and down

Thanks Teethless Mama. This works a treat!

You're Welcome!

any chance you can explain the formula? i would love to understand it