Results 1 to 5 of 5

Thread: Trim Text after 3rd Underscore but retain format

  1. #1
    Junior Member
    Join Date
    Mar 2012
    Posts
    21
    Rep Power
    0

    Trim Text after 3rd Underscore but retain format

    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!

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    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
    Last edited by Admin; 05-11-2012 at 01:40 PM.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    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.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  5. #5
    Junior Member
    Join Date
    Mar 2012
    Posts
    21
    Rep Power
    0
    Yes, Rick gave me the solution to this problem on MrExcel....thank you so much!

Similar Threads

  1. Trim all Cells in a Worksheet - VBA
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 6
    Last Post: 08-21-2015, 08:22 AM
  2. Replies: 3
    Last Post: 06-01-2013, 11:31 AM
  3. Replies: 3
    Last Post: 04-05-2013, 08:24 AM
  4. Convert Text In YYYYMMDD Format To Date Format
    By S M C in forum Excel and VBA Tips and Tricks
    Replies: 1
    Last Post: 02-28-2012, 12:04 AM
  5. Replies: 3
    Last Post: 04-10-2011, 07:15 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •