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>

 

[James006]

Hello,

It seems to me the very step is to use a formula which counts the total number of delimiters ...

Do you already use such a formula ...???

 

[James006]

Re,

A formula to be tested :

=LEFT(A1,IFERROR(FIND(CHAR(1),SUBSTITUTE(A1,"_",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"_",""))-2)),"")-1)

 

[faizal Izham]

Hi James006,

Thanks for your code.. however the code only return as follow.

device_hostname1_rootvg_lun1
device_hostname1_vgDBapp_lun397
device_hostname1_vgDBbkp_lun398
device_hostname1_vgDButils_lun399
device_hostname1_2_Arch1_SharedLUN01
device_hostname1_2_Arch1_SharedLUN02
device_hostname1_2_Arch1_SharedLUN03

I would need to remove "device_" as well from the output. I'm still a beginner in excel.. so far i only googled for what i had wanted and not entirely understand what each formula does.
Appreciate your help on this matter

Thank You.

Faizal

 

[James006]

Hi,

To remove device_ ... you can use following

=SUBSTITUTE(LEFT(A18,IFERROR(FIND(CHAR(1),SUBSTITUTE(A18,"_",CHAR(1),LEN(A18)-LEN(SUBSTITUTE(A18,"_",""))-2)),"")-1),"device_","")

HTH

 

[faizal Izham]

Hi James006,

Thank you.. your code works perfectly.... also manage to find a stupid way of doing things as well by modifying a bit of your code..

=MID(LEFT(C2,IFERROR(FIND(CHAR(1),SUBSTITUTE(C2,"_",CHAR(1),LEN(C2)-LEN(SUBSTITUTE(C2,"_",""))-1)),"")-1),FIND("_",LEFT(C2,IFERROR(FIND(CHAR(1),SUBSTITUTE(C2,"_",CHAR(1),LEN(C2)-LEN(SUBSTITUTE(C2,"_",""))-1)),"")-1))+1,256)
but this one works perfectly

=SUBSTITUTE(LEFT(A18,IFERROR(FIND(CHAR(1),SUBSTITUTE(A18,"_",CHAR(1),LEN(A18)-LEN(SUBSTITUTE(A18,"_",""))-1)),"")-1),"device_","")
Much appreciated and Thank You.

Faizal.

 

[steve the fish]

This should work:

=MID(LEFT(A1,FIND("|",SUBSTITUTE(A1,"_","|",LEN(A1)-LEN(SUBSTITUTE(A1,"_",""))-1))-1),FIND("_",A1)+1,LEN(A1))

 

[James006]

Glad you could fix your problem ...

Thanks for your Thanks ...

 

[Peter_SSs]

In fact, if your sample data is fully representative, then all you would need is this.

Excel Workbook
	A	B
1	device_hostname1_rootvg_lun1_1_vol	hostname1_rootvg_lun1
2	device_hostname1_vgDBapp_lun397_1_vol	hostname1_vgDBapp_lun397
3	device_hostname1_vgDBbkp_lun398_1_vol	hostname1_vgDBbkp_lun398
4	device_hostname1_vgDButils_lun399_1_vol	hostname1_vgDButils_lun399
5	device_hostname1_2_Arch1_SharedLUN01_lun400_1_vol	hostname1_2_Arch1_SharedLUN01_lun400
6	device_hostname1_2_Arch1_SharedLUN02_lun401_1_vol	hostname1_2_Arch1_SharedLUN02_lun401
7	device_hostname1_2_Arch1_SharedLUN03_lun402_1_vol	hostname1_2_Arch1_SharedLUN03_lun402
Extract text

 

[faizal Izham]

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

Kudos to all who have helped..


Faizal