I have string values in Excel like the following:
category_topic1_sports_4
category_topic1_business_4
category_topic1_entertainment_4
category_topic1_gaming_4
Is there a formula that would get rid of the sports but keep the rest of the string?
What I am aiming to have: category_topic1_4
I tried to do this substitute function, but failed:
=SUBSTITUTE(REPLACE(SUBSTITUTE(D4,"_s",REPT(" ",100)&"_"),100,100,"")," ","")
You can use the following formula replacing only A1 in the first line with the cell with text you want to replace. This assumes that you will always be replacing the 3rd word only:
=LET(split,TEXTSPLIT(A1,"_"),
removed_3rd,CHOOSECOLS(split,1,2,4),
TEXTJOIN("_",TRUE,removed_3rd))
TEXTSPLIT splits each word by the delimiter, in this case the underscore.
CHOOSECOLS allows you to choose which pieces from the split you want to keep, in this case, the 1st, 2nd, and 4th word.
TEXTJOIN puts them back together adding the underscore back between each word.
LET lets you create variable names for each piece of your formula to make it easier to read/follow and it makes each piece reusable, if needed.
I just learned of a more succinct method (from @Ron Rosenfeld). Rather than using CHOOSECOLS, you can use INDEX and specify the array items you want to return. You can rearrange to use LET if you want it more readable.
=TEXTJOIN("_",,INDEX(TEXTSPLIT(A1,"_"),1,{1,2,4}))