Sorting in Numbers

Hi MB_1014,

Data in Column B are Text strings and will sort alphabetically. Separate the numerical part into column C like this:

Formula in C2 is VALUE(TEXTAFTER(B2,C$1,1))

TEXTAFTER pulls all characters in B2 after HG (or whatever is in the Header Cell C1).

VALUE converts those characters to a number.

Formula in D2 is C$1&C2 to recombine the HG with the number part.

Sort the table by Column C Ascending

If Column D is now what you want, you can:

• Save as Template for future use, or
• Copy Column D and Menu > Edit > Paste Formula Results to "fix" the results. Then delete Columns B and C.

Regards,

Ian.

Edit: On rereading, I now realise that Column D is not needed. Column B is now sorted 🤔.

Ian.

In cases where I want a list of text+index numbers to sort correctly, whether in Numbers or in a file structure or whatever app, I pad the numeric part so all have the same number of digits. I am assuming you want all the HG's together then HH's, etc, as HG100, HG101, HH100, HH101 and not sorted as HG100, HH100, HG101, HH101

If you are using three digits max then it would be HG011 not HG11

Here's another approach using recent features in Numbers. If there are more alpha codes in front than just HG then this is a more compact solution (needs just one extra column), but comes at the cost of a more complex formula.

This uses regular expressions to extract the alpha [\D] and numeric [\d] portions, then REPT to pad the numeric portion with leading zero(s) if needed, and then puts the two back together again into a string that will sort in the order described.

If the numeric portion has more than 3 digits then simply increase the 3 in the formula.

=REGEX.EXTRACT($B2,"[\D]+")&REPT(0,3−LEN(REGEX.EXTRACT($B2,"[\d]+")))&REGEX.EXTRACT($B2,"[\d]+")

Replace the , in the formula with ; for regions that use , as the decimal separator.

SG

Ian,

I'm giving you an upvote here for introducing me to "TEXTAFTER". And TEXTBETWEEN and TEXTBEFORE by proximity.

They have more power than SUBSTITUTE, which would have been my choice otherwise. As in: VALUE(SUBSTITUTE(Table 1::A2,"HG","")).

Jerry

Thanks Jerry!