Text field formula help

[smmt2207]

I have a field that is alphanumeric. The majority of the fields are numbers. When I sort they do not sort in numerical order. I would like to add a leading zero to any field that is 3 characters so they will sort correctly. See below example

Current sort
100
101
102
20
201
202
203

Would like to see
020
100
101
102
202
202
203

Thank you in advance

Margie

 

[hilfy]

Try using a formula like this:

If IsNumeric({table.field}) then
  If len({table.field}) < 3 then 
    right('00' + {table.field}, 3)
  else {table.field}
else {table.field}

-Dell


Associate Director, Data & Analytics
Protiviti

http://www.protiviti.com