Skip to Content
0

How to sort an alphanumerical string alphabetically?

Apr 10, 2017 at 08:24 AM

86

avatar image

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.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

8 Answers

Best Answer
Ian Waterman Apr 10, 2017 at 08:48 AM
0

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Ian

This worked, I'd forgottent to sort on the formula!

Thank you.

0
Abhilash Kumar
Apr 10, 2017 at 09:22 AM
0

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

Mid({YourField}, 14)

-Abhilash

Show 1 Share
10 |10000 characters needed characters left characters exceeded

As usual, thank you for your help Abhilash.

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

0
Dirty Rony Apr 10, 2017 at 09:21 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Ian Waterman Apr 10, 2017 at 09:48 AM
0

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

Ian

Share
10 |10000 characters needed characters left characters exceeded
Dirty Rony Apr 10, 2017 at 10:08 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Ian Waterman Apr 10, 2017 at 11:33 AM
0

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Thank you Ian.

I already marked your original answer as the correct one, did you see it?

0
Dirty Rony Apr 18, 2017 at 10:54 AM
0

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?

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Just add a sum Summary to your new Trim Group footer

Ian

PS -Usually best to start a new thread as original is marked as answered so users may not bother to look.

0

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)
0
Ian Waterman Apr 19, 2017 at 07:26 AM
0

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Thank you.

0