• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

rename - split up multiple underscore formula help

jk51

Member
Hi,

need help on create excel split formula on how to get remove after the 4th underscore to the right.

For example

H1_1_12i_P_8-19yrs change to H1_1_12i_P
H2_3_01i_P_75+ change to H2_3_01i_P
H3_5_48i_M_under 20 change to H3_5_48i_M

Thank you,

Mr Singh
 

AliGW

Active Member
Remove or return?

Is this what you want?

=SUBSTITUTE(TRIM(LEFT(SUBSTITUTE($A1,"_",REPT(" ",100)),(100*4)-1))," ","_")

Or is it this?

=SUBSTITUTE(TRIM(MID(SUBSTITUTE($A1,"_",REPT(" ",100)),(100*4)-1,1000))," ","_")
 
Top