1. ## Sort (2003)

Hi,

We always receive excel files includes medical record number. We would like sort by terminal digit order. However, since some medical record number missing leading zero. So I would to create a button with vb that user can just click a button and then codes will runs to fill the missing leading zero if MRN is less than eight digit, and then sort all medical record by terminal digit order.

Regards,

2. ## Re: Sort (2003)

Hi Gary

I may have missed the point but could you format the range with a custom format with 00000000 so that any so a 7 digit number becomes 01234567.

3. ## Re: Sort (2003)

Yes, I can. But the terminal digit order is mean:

01234567

First sort digit: 5 & 6
Second sort digit: 3 & 4
Third Sort digits: 0,1 & 2
Last sort digits: 7

Sorry for unclear information.

Thanks again.

4. ## Re: Sort (2003)

How would adding a leading zero help with sorting the way you indicate?

5. ## Re: Sort (2003)

leading zero really not help with sorting the way I want, but some time we receive some data doesn't have zero. That's why I would like to add leading zero first if there are missing.

Thanks

6. ## Re: Sort (2003)

I don't understand why you bring the sort order into this. It doesn't seem to have any relation with your question. Jezza has already told you how you can display a leading zero.

7. ## Re: Sort (2003)

Create a TDO column with the formula (presuming that A1 has the number, change as desired):

=MID(RIGHT(REPT("0",8)&A1,8),6,2)&MID(RIGHT(REPT(" 0",8)&A1,8),4,2)&MID(RIGHT(REPT("0",8)&A1,8),1,3)& RIGHT(A1,1)

Copy this down the column...