Skip to Content
avatar image
Former Member

How to sort an alphanumerical string alphabetically?

Hi

I have an alphanumerical records which I would like to get them sorted alphabetically.

The field has the following pattern:

211502 (2015 Azumbre, Verdejo...)

111414 (2014 Les Cardinalices, Blanc...)

I would like to sort it as follows:

Azumbre, Verdejo...

Les Cardinalices, Blanc...

and so forth...

Thank you in advance for any help.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

8 Answers

  • Best Answer
    Apr 10, 2017 at 08:48 AM

    If it always has same format that is first 13 characters are

    '###### (#### '

    You can create formula

    Mid({YourField}, 14, 50)

    Replace 50 with whatever length of field is and then sort on this formula

    Ian

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 10, 2017 at 09:22 AM

    If you are not sure of the length of the field, you could simply use:

    Mid({YourField}, 14)

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      As usual, thank you for your help Abhilash.

      Would you be able to point out a solution for the below?

  • avatar image
    Former Member
    Apr 10, 2017 at 09:21 AM

    Thank you for your answer Ian.

    Your formula does alphabetise the alphanumerical field.

    Nevertheless I run into another issue...

    That colum is preceeded by another column with the product code, hence the items under description are not being kept together, and I wouldn't like to create a group, i.e., I just want the items under description to follow one after the other alphabetically.

    As is, the items are being orderd ascending by code, then alphabetically, as bellow.

    CODE DESCRIPTION ON HAND ON SALE AVAILABLE ON PO

    120621 Chateau Phelan Segur

    120680 Chateua Giscours

    121119 Chateau Phelan Segur

    120789 Chateau Giscours

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 10, 2017 at 09:48 AM

    Just change the order of the groups (move new formula group above product code group) or remove the product code group.

    Ian

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Apr 10, 2017 at 10:08 AM

    Would it be possible to concatenate both recordes (CODE + DESCRIPTION) and still have it alphabetized as I intended?

    Add comment
    10|10000 characters needed characters exceeded

  • Apr 10, 2017 at 11:33 AM

    You do not really need to do that as long as your prodct Code does not have a sort or Group with a higher level than your new Trim Formula Group or Sort

    You can concatenate fielsds but you still need to remove Product Code Group or sort (if one exists)

    Leave Group or Sort in place using the new trim formula.

    Create a new formula

    @Display// if Code = String

    Code&{Your TrimFormula}

    @Display// if Code = number

    totext(Code, 0, "")&{Your TrimFormula}

    Place this in details

    Ian

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Apr 18, 2017 at 10:54 AM

    Apologies in advance in case I am not supposed to add on a further question to this thread...

    After alphabetising by descritpion, how can I add a total for the quantities of the items that match the same description only?

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Thank you Ian.

      The items in the description are grouped by the supplier names and within each supplier I may have more than one item with the same name but with different product code, which I managed with your formula to alphabetise by the description name and not by the product code, as below:

      Mid({YourField}, 14, 50)

      Replace 50 with whatever length of field is and then sort on this formula

      So, what I am trying to achieve is to insert a total under items with the same name (this is because the vintages of the items (codes) are different, but their names are the same).

      Please take a look at the screen shot from the produced report. You will notice that Alta Paseo, Sauvignon Blanc appears twice uncer its supplier - Export Union. How can I have a total for that item only?

      capture.jpg (32.6 kB)
  • Apr 19, 2017 at 07:26 AM

    Assume you want to sum on name , eg Alta Paseo Sauvignon Blanc 6x75cl even though Code is different.

    Two options

    Add another group on description only and add a sum summary

    Or

    Add a Running total that resets on change of description

    Ian

    Add comment
    10|10000 characters needed characters exceeded