Need help replacing text after the the 9th underscore

[ScoobyChoo]

Hello! Got an interesting situation I am looking to resolve
So I have a naming convention that uses underscore to separate out different perimeters

Name_Location_Type_Location_Volume_Channel_Delivery_Supply_Quantity_Top Level Description_Mid Level Description_Low Level Description_Code_Language

Is there a formula that I can use that will search this cell and replace everything after the 9th underscore and replace everything that follows with a different text?
so it an example would like:

Name_Location_Type_Location_Volume_Channel_Delivery_Supply_Quantity_1_2_3_4_5 -> Name_Location_Type_Location_Volume_Channel_Delivery_Supply_Quantity_6_7_8_9_10

 

[Fluff]

Hi & welcome to MrExcel.
What exactly should the text be replaced with & why?

 

[ScoobyChoo]

Thanks!
The text should be replaced with an evergreen standard parameter, our naming convention is generally different for every single order placed but there will always be 1 consistent order that should exist, so in the example: Name_Location_Type_Location_Volume_Channel_Delivery_Supply_Quantity_6_7_8_9_10,
6_7_8_9_10 will always be a naming convention that is needed
So the idea to grab an existing text within in a cell, locate the 9th underscore and replace everything that follows with 6_7_8_9_10

 

[Fluff]

Ok, how about

Fluff.xlsm
	A
1
2	Name_Location_Type_Location_Volume_Channel_Delivery_Supply_Quantity_Top Level Description_Mid Level Description_Low Level Description_Code_Language
3	Name_Location_Type_Location_Volume_Channel_Delivery_Supply_Quantity_6_7_8_9_10
Main

Cell Formulas
Range	Formula
A3	A3	=TEXTJOIN("_",,TEXTBEFORE(A2,"_",9),SEQUENCE(,5,6))

 

[ScoobyChoo]

Sorry Fluff, didnt realize you were asking for the exact replacement. I was using the numbers as an example! Your post solves my problem perfectly, but can the actual text be "Customer Data_Homepage Visitors_Retarget_SS_English"

 

[Fluff]

Yup, you can use

=TEXTBEFORE(A2,"_",9)&"_Customer Data_Homepage Visitors_Retarget_SS_English"

 

[ScoobyChoo]

Thank you so much! This is perfect

 

[Fluff]

You're welcome & thanks for the feedback.