Trim Text after 3rd Underscore but retain format

[trankim]

I am hoping you excel geniuses can help me with a formula that will look for the 3rd underscore in a string and truncate all the text after the the 3rd underscore. I need to retain the format before the 3rd underscore though. Note: I may not have the same amount of underscores in each cell, so if there's not enough "underscore", it should return "Text/Logo".

Example:
Intel_CI_City_VisitCI_300x250_Flash.swf



Output Result:
Intel_CI_City_VisitCI

Example 2:
1x1_AMD.gif


Output Result:
Text/Logo



I appreciate any help that anyone can give me. Thanks!

[Admin]

Hi trankim,

Welcome to ExcelFox !

Is it 3rd or 4th ?

If your data in A1, in B1

=IF(LEN(A1)-LEN(SUBSTITUTE(A1,"_",""))>=4,LEFT(A1,FIND("|",SUBSTITUTE(A1,"_","|",4))-1),"Text/Logo")

If it's 3, replace the highlighted 4 with 3

HTH

[Rick Rothstein]

Cross-posted thread on the MrExcel forum...

MrExcel Message Board - View Single Post - Trim Text after 3rd Underscore but retain format (http://www.mrexcel.com/forum/showpost.php?p=3150405&postcount=2)

[Excel Fox]

trankim, welcome to ExcelFox. As highlighted by Rick, your query was cross-posted on MrExcel also. We don't mind cross-posting, but you must understand that in these online communities, cross-posting without intimating that it has been cross-posted is a serious offence. Think of it from a developers' perspective. They are volunteers here who spend their time, effort and expertise to help others. Cross-posting is like calling 10 different cab vendors for a pick and drop, and hoping on the first one that arrives. Hope you ensure that if you have to cross-post, it is clearly mentioned along with the link to the other pages.

[trankim]

Yes, Rick gave me the solution to this problem on MrExcel....thank you so much!