How to drop middle part of the string name in excel?

[Nicklim]

Hello, I hope to find help here. I need to drop middle part of a string in excel.
This is what I have:
OptionABCDcorrect
OptionDFTYuKlgkincorrecr
OptionGdhfjksjsbakvalidargument

This is what I need:
Optioncorrect
Optionincorrecr
Optionvalidargument

So basically, I need to drop middle part of the string.

Thank you!

 

[Rick Rothstein]

Hello, I hope to find help here. I need to drop middle part of a string in excel.
This is what I have:
OptionABCDcorrect
OptionDFTYuKlgkincorrecr
OptionGdhfjksjsbakvalidargument

This is what I need:
Optioncorrect
Optionincorrecr
Optionvalidargument

So basically, I need to drop middle part of the string.

You have overly generalize whatever it is that you need as your 2nd and 3rd examples are not dropping the "middle part" of the string. Using your last example for demonstration purposes, you left the first 6 and last 13 characters, so what you removed hardly qualifies as the "middle part" or the text (removing the middle part would have left the same number of characters from before and after whatever was removed). You need to show us real examples of your text and tell us how you are identifying what needs to be dropped from those examples.

 

[Nicklim]

Thanks Rick,

What I’m trying to do is to leave beginning and the end and drop middle part that contains different characters in each row.

Here is another example.

This is what I have

Creative:BAN_XXX_AAA_100X100_AAA_DEMO_OPX_SINGLE
Creative:BAN_XXX_AAA_100X100_AAA_DEMO_PMT_889NA_Married
Creative:BAN_XXX_AAA_100X100_AAA_DEMO_CONTX_Family

The part I need to drop contains different characters and varies in length.

I need something like this

Creative: BAN_XXX_AAA_Single
Creative: BAN_XXX_AAA_Married
Creative: BAN_XXX_AAA_Family

I hope this makes sense.

Thank you.

 

[admiral100]

Hi,

Assuming we always start with the third "_"

=LEFT(A1,FIND("*",SUBSTITUTE(A1,"_","*",3)))&MID(SUBSTITUTE(A1,"_","*",LEN(A1)-LEN(SUBSTITUTE(A1,"_",""))),FIND("*",SUBSTITUTE(A1,"_","*",LEN(A1)-LEN(SUBSTITUTE(A1,"_",""))))+1,10)

 

[AlKey]

Try this
Enter in B1 and copy down

[B]=SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(A1,"_",REPT(" ",100)),300))," ","_")&"_"&TRIM(RIGHT(SUBSTITUTE(A1,"_",REPT(" ",50)),50))[/B]

Book1
	A	B
1	Creative:BAN_XXX_AAA_100X100_AAA_DEMO_OPX_SINGLE	Creative:BAN_XXX_AAA_SINGLE
2	Creative:BAN_XXX_AAA_100X100_AAA_DEMO_PMT_889NA_Married	Creative:BAN_XXX_AAA_Married
3	Creative:BAN_XXX_AAA_100X100_AAA_DEMO_CONTX_Family	Creative:BAN_XXX_AAA_Family
Sheet4

 

[Nicklim]

Try this
Enter in B1 and copy down

[B]=SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(A1,"_",REPT(" ",100)),300))," ","_")&"_"&TRIM(RIGHT(SUBSTITUTE(A1,"_",REPT(" ",50)),50))[/B]

#DAE7F5 " />#DAE7F5 ;text-align: center;color: #161120">

	A	B
1	Creative:BAN_XXX_AAA_100X100_AAA_DEMO_OPX_SINGLE	Creative:BAN_XXX_AAA_SINGLE
2	Creative:BAN_XXX_AAA_100X100_AAA_DEMO_PMT_889NA_Married	Creative:BAN_XXX_AAA_Married
3	Creative:BAN_XXX_AAA_100X100_AAA_DEMO_CONTX_Family	Creative:BAN_XXX_AAA_Family

<colgroup><col style="width: 25pxpx" https:="" www.mrexcel.com="" forum="" usertag.php?do="list&action=hash&hash=DAE7F5"" target="_blank"></colgroup><colgroup><col><col></colgroup><thead>
</thead><tbody>
</tbody>

#DAE7F5 ;color: #161120">Sheet4​

Wow! Thank you, AlKey! Worked like a magic!

 

[jtakw]

Hi,

Another way, a bit shorter:

Book1
	A	B
1	Creative:BAN_XXX_AAA_100X100_AAA_DEMO_OPX_SINGLE	Creative:BAN_XXX_AAA_ SINGLE
2	Creative:BAN_XXX_AAA_100X100_AAA_DEMO_PMT_889NA_Married	Creative:BAN_XXX_AAA_ Married
3	Creative:BAN_XXX_AAA_100X100_AAA_DEMO_CONTX_Family	Creative:BAN_XXX_AAA_ Family
Sheet289

Cell Formulas
Range	Formula
B1	=TRIM(LEFT(A1,FIND(" ",SUBSTITUTE(A1,"_"," ",3)))&RIGHT(SUBSTITUTE(A1,"_",REPT(" ",99)),99))

 

[AlKey]

A little bit shorter

[B]=LEFT(A1,FIND("_",A1,FIND("_",A1)+5))&TRIM(RIGHT(SUBSTITUTE(A1,"_",REPT(" ",50)),50))[/B]

 

[jtakw]

Don't know if OP has varying length of Text string before the 3rd underscore, but that assumes the fixed length of 3 characters in between 2nd and 3rd underscores, so it may or may not work.

If we were to make the assumption that the leading Text is fixed length, then even this will do:

=LEFT(A1,21)&TRIM(RIGHT(SUBSTITUTE(A1,"_",REPT(" ",99)),99))