0

Im new in excel formulas. I need an excel formula that can sort delimited numbers in a single cell. Note: I have different lengths of values per cell.

I.g. 349|2|4|11|1|2

Outcome: 1|2|2|4|11|349

Im thinking of reverse concatenating it first then sort it then concatenate it again. But i can't figure it out how to make a working formula for that. Hope someone can help me. Thanks.

Jacob
  • 1
  • I believe you will ultimately need a VBA solution (in the form of a User Defined Function - UDF) to solve this because each number is separated by a delimiter. If you only had numbers in the cell, see [this site](https://www.extendoffice.com/documents/excel/1870-excel-sort-numbers-within-cell.html) for some ideas. – PeterT May 13 '20 at 23:55
  • 1
    Here is a [UDF Solution](https://superuser.com/questions/705131/sorting-values-within-a-cell-in-excel) that you can use. Just change the split to be on `|` instead of `,` – urdearboy May 14 '20 at 00:01
  • See if [this link](https://stackoverflow.com/a/60797214/9808063) helps .. I tried it on the given sample, no issues. It works on alphanumeric as well. – Naresh May 14 '20 at 02:14

1 Answers1

1

This formula solution worked in cell contains numeric value only.

The formula using Textjoin and Filterxml function of which Textjoin available in Office 365 and Excel 2019, and Filterxml available in Excel 2013 and later Excel version

Assume your data housed in A1

In B1, enter array formula (Confirm by pressing Shift + Ctrl + Enter) :

=TEXTJOIN("|",1,IFERROR(1/(1/SMALL(FILTERXML("<a><b>"&SUBSTITUTE(A1,"|","</b><b>")&"</b></a>","//b"),ROW($1:$99))),""))

enter image description here

bosco_yip
  • 3,762
  • 2
  • 5
  • 10