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.