Sorting by text field

[vandehey]

Hi, I have a text field than contains a string of numbers, some with letters at the end (ex. 1e, 234, 8328v, 743, etc.). Does anyone know a way to sort this list so all the records without letters show up first, then lists the records with the numbers? Here is more info:

If I sort acending, it will return in this type of order: 1, 1e, 1v, 2, 2h, 2i,3, etc. I would like it to sort like this: 1, 2, 3 , 1e, 1v, 2h, 2i, etc. Does anyone have an idea of how to do this?

Thanks!

[BobWeaver]

Make a calculated field cContainsAlpha with this formula:

(TextField<>NumToText(TextToNum(TextField)))

Then put cContainsAlpha into your sort order:

1 cContainsAlpha

2 TextField

[vandehey]

Thanks for the help Bob, that works, but has a problem. When it is sorted, this creates another issue. Numbers now show up in this sort of order: 949, 95, 950, 959, 96, 961 etc. Do I now need to place a leading zero before the number? Is there an easy script to do this, or an option in the field type?

Would it be easier to change the field type to Number, and create a calculation field from that? Thanks agian for the help.

[BobWeaver]

I guess I knew that one was coming. Yes you will need to add leading zeroes. Make another calc field cNumberPart with the formula:

Right("00000000"&TextToNum(TheDataField),8)

Make sure you select "Text" result type from the menu at the bottom of the calc formula dialog box.

Now make your sort order:

1 cContainsAlpha

2 cNumberPart

This will work for numbers as big as 8 digits long.

[vandehey]

Thanks again for the help Bob. It's very, very, close, but not quite perfect. This now orders all the numbers properly, but has a small problem for the data with letters in them. It now shows the records in this order: E27, V27, E28, V28, etc. Ideally, I would like it to be: E27, E28, V27, V28. Is there a tweak we can make to that calc field to fix this?

Note: Before I added the cNumberPart to the sort order, it listed E#'s consecutively, then V#'s consecutively, like I would like them.

Thanks a million for all the help.

[BobWeaver]

Hmmm... in your first post, the data was in the form 1e, 1v, 2h, 2i. Now you have the letter in front of the digit. This makes a big difference to how things sort, and to what you need to do to make things work right. Assuming that your data will never contain more than one letter, and it is in front of the number (as in your last post) then make another calc field cPrefix with the formula:

Left(TheDataField,1)

and put this into the sort order like so:

1 cContainsAlpha

2 cPrefix

3 cNumberPart