 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.

• Hi,

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

Code
1. =left(A1,12)

should do the job ...

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

Code
1. =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 "Thumbs Up" icon, below, in the bottom right corner • The length fluctuates but the second formula works perfectly, thank you.

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