Delete certain text that is repeated multiple times within a cell

[Miroy72]

Hey Everyone!

I need some help figuring out how to delete certain text that repeats itself within a cell in Excel 2010. Right now I have (for example) all in one cell A1:

"John Smith (Services - 6)/OAI; David Frame (Services - 6)/OAI; Kyle Pilot (Services - 6)/OAI"

and I am trying to delete the "(Services - 6)/OAI" after each name so I only have the names and the semicolons left in B1. Sometimes its only one name in the cell, and sometimes it can be up to four names. I know I could do find and replace but I would like a formula so when I copy and paste this data into my spreadsheet, the formula as a new column would automatically do this for me. I also tried Text to Columns but that separates the names into separate columns (I want to keep the names in same cell) and its not a formula as well.

Thanks for your help!

 

[Scott T]

Try

Excel 2010
	A	B	C	D
2	John Smith (Services - 6)/OAI; David Frame (Services - 6)/OAI; Kyle Pilot (Services - 6)/OAI			John Smith ; David Frame ; Kyle Pilot
Sheet1

Cell Formulas
Range	Formula
D2	=SUBSTITUTE(A2,"(Services - 6)/OAI","")

 

[Miroy72]

Such an easy fix, thanks so much Scott T!

Any way to delete the space after the name and the semicolon?

 

[Scott T]

Put a space before (Services - 6)/OAI

=SUBSTITUTE([COLOR=blue]A2," (Services - 6)/OAI",""[/COLOR])

 

[Miroy72]

Put a space before (Services - 6)/OAI

=SUBSTITUTE([COLOR=blue]A2," (Services - 6)/OAI",""[/COLOR])

Thanks!

 

[Scott T]

You are welcome.