Remove Text String from Text String

[rockchalk33]

1	need 00001 help please-__-	need help please-__-
KXY247	with KXY247 this	with this
35	major 35 brain fart	major brain fart

Column A is showing a string of what I need removed from Column B with the final result being Column C. I have about 33000 rows this needs to run on wish me luck!

I feel this should be super simple but man am I struggling to find a solution!

Devin

 

[shaowu459]

please have this a try:

MrExcel.xlsx
	A	B	C
1	34	Wilson combat 34 extended ejector 45	Wilson combat extended ejector 45
2	35	Case 00035 stockman med folder chrome vanadium clip/spey/sheepfoot yellow hndl	Case stockman med folder chrome vanadium clip/spey/sheepfoot yellow hndl
3	35	Cci 0035 standard round nose (lrn)	Cci standard round nose (lrn)
4	35	Psp 035 belt slide iwb/owb med/lg	Psp belt slide iwb/owb med/lg
Sheet4

Cell Formulas
Range	Formula
C1:C4	C1	=LOOKUP(1,0/(LEN(SUBSTITUTE(B1,REPT(0,ROW($1:$10)-1)&A1,""))<LEN(B1)),SUBSTITUTE(B1,REPT(0,ROW($1:$10)-1)&A1,""))

 

[shaowu459]

Add TRIM to remove unncessary blanks.

MrExcel.xlsx
	A	B	C
1	34	Wilson combat 34 extended ejector 45	Wilson combat extended ejector 45
2	35	Case 00035 stockman med folder chrome vanadium clip/spey/sheepfoot yellow hndl	Case stockman med folder chrome vanadium clip/spey/sheepfoot yellow hndl
3	35	Cci 0035 standard round nose (lrn)	Cci standard round nose (lrn)
4	35	Psp 035 belt slide iwb/owb med/lg	Psp belt slide iwb/owb med/lg
Sheet4

Cell Formulas
Range	Formula
C1:C4	C1	=TRIM(LOOKUP(1,0/(LEN(SUBSTITUTE(B1,REPT(0,ROW($1:$10)-1)&A1,""))<LEN(B1)),SUBSTITUTE(B1,REPT(0,ROW($1:$10)-1)&A1,"")))

 

[FormR]

Here is actual data, we would want C1 to be "Wilson combat extended ejector 45", C2 to be "Case stockman med folder chrome vanadium clip/spey/sheepfoot yellow hndl", etc..

Hi, here's another option you can try.

Book2
	A	B	C
1	34	Wilson combat 34 extended ejector 45	Wilson combat extended ejector 45
2	35	Case 00035 stockman med folder chrome vanadium clip/spey/sheepfoot yellow hndl	Case stockman med folder chrome vanadium clip/spey/sheepfoot yellow hndl
3	35	Cci 0035 standard round nose (lrn)	Cci standard round nose (lrn)
4	35	Psp 035 belt slide iwb/owb med/lg	Psp belt slide iwb/owb med/lg
Sheet1

Cell Formulas
Range	Formula
C1:C4	C1	=TRIM(SUBSTITUTE(B1,TRIM(RIGHT(SUBSTITUTE(LEFT(B1,FIND(A1,B1)+LEN(A1)-1)," ",REPT(" ",99)),99)),"",1))

 

[mikerickson]

Try
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1," 0"," ")," 0"," ")," 0", " ")," 0", " "),A1,"",1))

 

[rockchalk33]

Try
=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B1," 0"," ")," 0"," ")," 0", " ")," 0", " "),A1,"",1))

Hi, here's another option you can try.

Book2
	A	B	C
1	34	Wilson combat 34 extended ejector 45	Wilson combat extended ejector 45
2	35	Case 00035 stockman med folder chrome vanadium clip/spey/sheepfoot yellow hndl	Case stockman med folder chrome vanadium clip/spey/sheepfoot yellow hndl
3	35	Cci 0035 standard round nose (lrn)	Cci standard round nose (lrn)
4	35	Psp 035 belt slide iwb/owb med/lg	Psp belt slide iwb/owb med/lg
Sheet1

Cell Formulas
Range	Formula
C1:C4	C1	=TRIM(SUBSTITUTE(B1,TRIM(RIGHT(SUBSTITUTE(LEFT(B1,FIND(A1,B1)+LEN(A1)-1)," ",REPT(" ",99)),99)),"",1))

Add TRIM to remove unncessary blanks.

MrExcel.xlsx
	A	B	C
1	34	Wilson combat 34 extended ejector 45	Wilson combat extended ejector 45
2	35	Case 00035 stockman med folder chrome vanadium clip/spey/sheepfoot yellow hndl	Case stockman med folder chrome vanadium clip/spey/sheepfoot yellow hndl
3	35	Cci 0035 standard round nose (lrn)	Cci standard round nose (lrn)
4	35	Psp 035 belt slide iwb/owb med/lg	Psp belt slide iwb/owb med/lg
Sheet4

Cell Formulas
Range	Formula
C1:C4	C1	=TRIM(LOOKUP(1,0/(LEN(SUBSTITUTE(B1,REPT(0,ROW($1:$10)-1)&A1,""))<LEN(B1)),SUBSTITUTE(B1,REPT(0,ROW($1:$10)-1)&A1,"")))

BOOM! These are all working great!! Thank you guys so much!!!!!