Removing last X number of characters depending on the ending of the value

  • Hi all,


    I am trying to create a formula where depending on what the ending of the value in the cell is, it deletes 1 or 2 of the last characters.


    For example I could have two different codes such as:


    AB12-34567-ABC
    AB12-34567-AB


    I want to make a formula that will remove either 1 or two of the last characters to turn it into AB12-34567-A regardless of if there is 2 or 3 characters after the last -


    The only problem is these codes are very inconsistent in length before the last - so I can't just do a simple length counting and then using that unless there is a way to count what's after the last - as that is the only thing that stays consistent in the code.


    The only thing I have managed to get sort of working is I can do a =COUNTIF(A2,"*-???") and then if it counts it then you do the =IF(B2=1,LEFT(A2,LEN(A2)-2),LEFT(A2,LEN(A2)-1))


    Now combining the two formulas does work to get =IF(COUNTIF(A2,"*-???")=1,LEFT(A2,LEN(A2)-2),LEFT(A2,LEN(A2)-1)) but it looks a bit messy to me and I am sure there is a neater way of doing this with a formula, I can do it on a macro however that isn't suitable for my needs so ideally a single formula would be great.


    Thanks in advance.

  • Hi,


    If your objective is consistently to get the first 12 digits ...


    Code
    =left(A1,12)


    should do the job ...


    But if the length before the second [SIZE=18px]-[/SIZE] can fluctuate ... you should test :


    Code
    =LEFT(A1,IFERROR(FIND(CHAR(1),SUBSTITUTE(A1,"-",CHAR(1),2)),"")+1)


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)


  • The length fluctuates but the second formula works perfectly, thank you.

  • Glad you could solve your question ...:wink:


    Thanks for your Thanks ...AND for the Like ...:smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!