how to extract data between first and 2nd last delimiter.

[faizal Izham]

Hi All,

I would like to know if there is an excel formula to extract data in the middle between first and 2nd last delimiter.
I have a sample data as below.

device_hostname1_rootvg_lun1_1_vol
device_hostname1_vgDBapp_lun397_1_vol
device_hostname1_vgDBbkp_lun398_1_vol
device_hostname1_vgDButils_lun399_1_vol
device_hostname1_2_Arch1_SharedLUN01_lun400_1_vol
device_hostname1_2_Arch1_SharedLUN02_lun401_1_vol
device_hostname1_2_Arch1_SharedLUN03_lun402_1_vol

and i wanted to have a formula which can output the data as per below.

hostname1_rootvg_lun1
hostname1_vgDBapp_lun397
hostname1_vgDBbkp_lun398
hostname1_vgDButils_lun399
hostname1_2_Arch1_SharedLUN01_lun400
hostname1_2_Arch1_SharedLUN02_lun401
hostname1_2_Arch1_SharedLUN03_lun402

Thank You in advance.

Faizal

<tbody>
</tbody>

 

[Peter_SSs]

Thank You Peter and Steve for posting alternative code to get things done.. Appreciate your help very much..

No problem. Glad to contribute.