Remove second part of hyphenated word

[kbianco]

Hi there,

Trying to convert text within one cell to a simplified version. For example, I have a cell that reads "UK Fund Mid-Cap Blend" but I want to convert this to "Mid Blend". Right now, I am using the below formula to strip "UK Fund Mid-Cap Blend" down to "Mid-Cap Blend". cell C9 represents the full text string.

=MID(C9,FIND("@",SUBSTITUTE(C9," ","@",LEN(C9)-LEN(SUBSTITUTE(C9," ",""))-1))+1,100)

I want to take this one step further and remove the "-Cap" portion of Mid-Cap, so that the cell just reads "Mid Blend". Also, i want to be able to use this across a variety of inputs. I.e Will need to convert Mid-Cap Growth to Mid Growth.

Any ideas?

Thanks!
Kayla

 

[Anonymous1378]

Perhaps try this since you have Excel 365?

=TEXTJOIN(" ",1,TAKE(TEXTBEFORE(TEXTSPLIT(C9," "),"-",,,1),,-2))

 

[kbianco]

wow - that worked perfectly! thank you so much. appreciate the help