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.

Quote from Carim

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

Display More

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