Zero Padding a value for sorting purposes

[CripZZ23]

I have a field that will have a format similar to the following:

AA-1
AA-2
AA-10
AA-1 (1)
AA-10 (1)
AA-2 (12)
Etc.

Essentially, it is 2 Letters, a -, then a number (1 or 2 digits). Optionally there is a space following by 1 or 2 digits inside of ( ).

I would like to add a field that calculates a sort order based on the left value - similar to the following:

Value	Sort Value
AA-1	AA-01
AA-2	AA-02
AA-10	AA-10
AA-1 (1)	AA-01 (01)
AA-10 (1)	AA-10 (01)
AA-2 (12)	AA-02 (12)

We want to be able to sort the column. Any assistance would be appreciated.

 

[Joe4]

Welcome to the Board!

For a value in A1, you can use this formula to create your Sort value:

=IF(OR(LEN(A1)=4,MID(A1,5,1)=" "),LEFT(A1,3) & "0" &RIGHT(A1,LEN(A1)-3),A1)

 

[CripZZ23]

Thanks for the help! Does this add logic to pad the value inside the ( ) as well? It works for the -1 to -01, but it doesn't seem to work for the (1) to (01).

 

[Joe4]

Sorry, I missed that part. Makes it a bit more complex, but try this:

=LET(x,IF(OR(LEN(A1)=4,MID(A1,5,1)=" "),LEFT(A1,3) & "0" &RIGHT(A1,LEN(A1)-3),A1),IF(ISNUMBER(FIND("(",x)),IF(FIND(")",x)-FIND("(",x)=2,SUBSTITUTE(x,"(","(0"),x),x))

And here is the proof!

 

[Scott Huish]

=LET(x,IFERROR(REPLACE(A1,SEARCH("-? ",A1&" ")+1,0,"0"),A1),IFERROR(REPLACE(x,SEARCH("(?)",x)+1,0,"0"),x))

Book1
	A	B
1	AA-1	AA-01
2	AA-2	AA-02
3	AA-10	AA-10
4	AA-1 (1)	AA-01 (01)
5	AA-10 (1)	AA-10 (01)
6	AA-2 (12)	AA-02 (12)
Sheet1

Cell Formulas
Range	Formula
B1:B6	B1	=LET(x,IFERROR(REPLACE(A1,SEARCH("-? ",A1&" ")+1,0,"0"),A1),IFERROR(REPLACE(x,SEARCH("(?)",x)+1,0,"0"),x))