Return everything after second underscore in a cell

[heathclif]

Cell A1:

blah_blah_everythingelsethatisinthiscell

Return:

everythingelsethatisinthiscell

Is there a MID FIND or anything else that will work?

Besame Much

 

[Jonmo1]

Welcome to the board

Try

=REPLACE(A1,1,FIND("_",A1,FIND("_",A1)+1),"")

 

[Mike LH]

Hi,

Try this

=MID(A1,FIND(CHAR(7),SUBSTITUTE(A1,"_",CHAR(7),2))+1,LEN(A1))

 

[heathclif]

Many thanks to both of you for your awesomeness!

 

[Jonmo1]

ADVERTISEMENT

You're welcome

 

[Rick Rothstein]

Many thanks to both of you for your awesomeness!

If you are going to use Jonmo1's code (3 function calls to 6 for Mike's), and if you plan to copy it down where Column A's cells are blank (perhaps in anticipation of working against future data), then you might want to consider this version of it (returns "" instead of a #VALUE! error)...

=REPLACE(A1,1,FIND("_",A1&"__",FIND("_",A1&"_")+1),"")

Also, here is another formula (uses four function calls though) that will also work (assuming your text is less than 200 characters in length)...

=TRIM(MID(SUBSTITUTE(A1,"_",REPT(" ",200)),400,999))

 

[heathclif]

Hi Rick! Thanks for that!