+ Reply to Thread
Results 1 to 4 of 4

IF ELSE filter to trim end of string and show specific character in string

  1. #1
    Registered User
    Join Date
    04-16-2010
    Location
    usa
    MS-Off Ver
    Excel 2003
    Posts
    2

    IF ELSE filter to trim end of string and show specific character in string

    Hi,

    I have a column of filenames and would like to

    1) produce a corresponding column with a varying number of characters trimmed from the end given a specific rule
    2) produce another column showing a specific character in the filename on the condition that the character appears

    Examples of filenames
    longexample1+D.pdf
    shortexample2.pdf
    example3+A.pdf
    longexample4+B.pdf
    reallylongexample5.pdf

    In one column i want the filenames to be reduced by either 4 or 6 characters from the right depending upon if they end '.pdf' or '+X.pdf'
    longexample1
    shortexample2
    example3
    longexample4
    reallylongexample5

    In another column i would like the character immediately following the '+' to appear. If there is no '+', then nothing should be returned. This would look like
    D

    A
    B


    The filenames will always be of varying length but the final 4 characters will always be '.pdf. and if a '+' appears the final 6 digits will always be '+X.pdf' with X being variable.

    Have tried using search, find, if-else formulas but can't seem to merge them correctly to get the desired results. Am using excel 2003.

    Any help would be much appreciated. Thankyou.

  2. #2
    Registered User
    Join Date
    04-29-2008
    Location
    Tennessee
    MS-Off Ver
    2007
    Posts
    37

    Re: IF ELSE filter to trim end of string and show specific character in string

    if the first filename is in A2, paste this in B2

    =IF(ISERROR(FIND("+",A2)),LEFT(A2,LEN(A2)-4),LEFT(A2,LEN(A2)-6))

    and this in C2

    =IF(ISERROR(FIND("+",A2)),"",MID(A2,FIND("+",A2)+1,1))

  3. #3
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,218

    Re: IF ELSE filter to trim end of string and show specific character in string

    Try this

    Copy & PasteSpecial > Values to finalise
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    04-16-2010
    Location
    usa
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: IF ELSE filter to trim end of string and show specific character in string

    Thankyou Rebuild8 and Marcol - both those solutions worked perfectly. They will save me hundreds of hours and headaches!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1