TEXT CUTTING

[guna_sekar87]

KINDLY GIVE SINGLE FORMULA FOR THE BELOW EXAMPLES:


EXAMPLE 1)I018_002_003_S-Qualite sonore _ mauvaise quelque soit la source/000000-General/402-Diag procedure/0016


FROM THIS I NEED TO GET : I018_002_003_S/000000/402/0016


EXAMPLE 2)I000_010_018_O-"hands-free" microphone/000000-General/111-Diagnosis Procedure/0010


FROM THIS I NEED TO GET : I000_010_018_O/000000/111/0010


EXAMPLE 3)V007_005_003_S-Reversing lights: erratic or inconsistent operation/000000-General/402-Diag procedure/0016


FROM THIS I NEED TO GET : V007_005_003_S/000000/402/0016

 

[SirJB7]

Hi, guna_sekar87!


First of all please do not write in uppercase as it's synonymous of shouting and I hope that your intention isn't so.


Now assumming your data are in cell A1 and down, in B1 type and copy down as needed:

=IZQUIERDA(A1;HALLAR("-";A1)-1)&EXTRAE(A1;HALLAR("/";A1);HALLAR("-";A1;HALLAR("/";A1))-HALLAR("/";A1))&EXTRAE(A1;HALLAR("/";A1;HALLAR("/";A1)+1);HALLAR("-";A1;HALLAR("/";A1;HALLAR("/";A1)+1))-HALLAR("/";A1;HALLAR("/";A1)+1))&EXTRAE(A1;HALLAR("/";A1;HALLAR("-";A1;HALLAR("/";A1;HALLAR("/";A1)+1)));LARGO(A1)-HALLAR("/";A1;HALLAR("-";A1;HALLAR("/";A1;HALLAR("/";A1)+1)))+1) ----->


In English:

=LEFT(A1,SEARCH("-",A1)-1)&MID(A1,SEARCH("/",A1),SEARCH("-",A1,SEARCH("/",A1))-SEARCH("/",A1))&MID(A1,SEARCH("/",A1,SEARCH("/",A1)+1),SEARCH("-",A1,SEARCH("/",A1,SEARCH("/",A1)+1))-SEARCH("/",A1,SEARCH("/",A1)+1))&MID(A1,SEARCH("/",A1,SEARCH("-",A1,SEARCH("/",A1,SEARCH("/",A1)+1))),LEN(A1)-SEARCH("/",A1,SEARCH("-",A1,SEARCH("/",A1,SEARCH("/",A1)+1)))+1)


Regards!