Remove part number from a text string

[Bassplaya7]

Greetings!

I have the following text string in cell H in my spreadsheet. I need a formula that can remove the bold part numbers from within the text string. As you can see they are all different sizes.

[TABLE="width: 696"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Out For Rebuild - Qty:1 / 227-9850 - PUMP GP-AUXILIARY WATER / SR:WHS-MIDLAND[/TD]
[/TR]
[TR]
[TD]Out For Rebuild - Qty:1 / 0R-4566CORE - CORE-HEAD, 3300 / SR:WHS-CARLSBAD[/TD]
[/TR]
[TR]
[TD]Out For Rebuild - Qty:1 / T112-60031-01R-1-83R - STARTER, AIR / SR:CNX-AGAVE[/TD]
[/TR]
[TR]
[TD]Out For Rebuild - Qty:2 / B-0576 - VLV RET,DIS,15-3/4 22.5RHEET / SR:WHS-MIDLAND[/TD]
[/TR]
[TR]
[TD]Out For Rebuild - Qty:1 / T121-60031-01R-103-R - STARTER, TURBINE / SR:WHS-CARLSBAD[/TD]
[/TR]
</tbody>[/TABLE]

Thanks for your help!!

 

[BobUmlas]

Greetings!

I have the following text string in cell H in my spreadsheet. I need a formula that can remove the bold part numbers from within the text string. As you can see they are all different sizes.

[TABLE="width: 696"]
<tbody>[TR]
[TD]Out For Rebuild - Qty:1 / 227-9850 - PUMP GP-AUXILIARY WATER / SR:WHS-MIDLAND
[/TD]
[/TR]
[TR]
[TD]Out For Rebuild - Qty:1 / 0R-4566CORE - CORE-HEAD, 3300 / SR:WHS-CARLSBAD
[/TD]
[/TR]
[TR]
[TD]Out For Rebuild - Qty:1 / T112-60031-01R-1-83R - STARTER, AIR / SR:CNX-AGAVE
[/TD]
[/TR]
[TR]
[TD]Out For Rebuild - Qty:2 / B-0576 - VLV RET,DIS,15-3/4 22.5RHEET / SR:WHS-MIDLAND
[/TD]
[/TR]
[TR]
[TD]Out For Rebuild - Qty:1 / T121-60031-01R-103-R - STARTER, TURBINE / SR:WHS-CARLSBAD
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks for your help!!

DO the first one manually -- that is, enter
Out For Rebuild - Qty:1 / - PUMP GP-AUXILIARY WATER / SR:WHS-MIDLAND
in a cell, say K2. With K2 active press ctrl/E (flash fill)

 

[dreid1011]

Perhaps this:

=MID(A1,FIND("/",A1,1)+2,FIND("@",SUBSTITUTE(A1," - ","@",2))-(FIND("/",A1,1)+2))

Also, I tried the flash fill and this is the result I got:

[TABLE="width: 550"]
<tbody>[TR]
[TD]Out For Rebuild - Qty:1 / - PUMP GP-AUXILIARY WATER / SR:WHS-MIDLAND
[/TD]
[/TR]
[TR]
[TD]Out For Rebuild - Qty:1 / - PUMP GP-AUXILIARY WATER / SR:WHS-MIDLAND
[/TD]
[/TR]
[TR]
[TD]Out For Rebuild - Qty:1 / - PUMP GP-AUXILIARY WATER / SR:WHS-MIDLAND
[/TD]
[/TR]
[TR]
[TD]Out For Rebuild - Qty:2 / - PUMP GP-AUXILIARY WATER / SR:WHS-MIDLAND
[/TD]
[/TR]
[TR]
[TD]Out For Rebuild - Qty:1 / - PUMP GP-AUXILIARY WATER / SR:WHS-MIDLAND
[/TD]
[/TR]
</tbody>[/TABLE]

The bold portions are the only things that changed from the first value done manually.