Trimming leading zero (0)

[CripZZ23]

I have a filed with data similar to the following:

AA-01
AA-02
AA-02 (01)
AA-10
AA-10 (01)
AA-10 (12)

Essentially it is always two letters followed by a - then a number (1 or 2 digits, but possibly zero padded to the left), and optionally another 1 to 2 digits in ( ) that is also potentially zero padded to the left.

I would like to remove the leading zeros so that they look like the right column below:
AA-01 AA-1
AA-02 AA-2
AA-02 (01) AA-2 (1)
AA-10 AA-10
AA-10 (01) AA-10 (1)
AA-10 (12) AA-10 (12)

I'm struggling - any help?

 

[RoryA]

Here's one way:

=SUBSTITUTE(SUBSTITUTE(A1,"-0","-"),"(0","(")

 

[ExcelToDAX]

Assuming your cell values are in A1:A6, you could do something like this:

=LET(cellValues,A1:A6,TRIM(TEXTSPLIT(cellValues,"-")&"-"&LET(firstNumbers,TEXTSPLIT(TEXTAFTER(cellValues,"-")," "),IF(LEFT(firstNumbers,1)="0",RIGHT(firstNumbers,1),firstNumbers))&" "&LET(lastNumbers,IFNA(TEXTAFTER(cellValues," "),""),IF(LEFT(lastNumbers,2)="(0","("&RIGHT(lastNumbers,2),lastNumbers))))

But Rorys is way easier and more efficient.