1

Suppose I have in a cell the following unsorted list-like text:

- VAL03
- VAL04
- VAL01
- VAL05
- VAL10
- VAL07
- VAL08
- VAL06
- VAL02
- VAL09

I would like to sort that list-like text. That is, in another cell I would like to get this:

- VAL01
- VAL02
- VAL03
- VAL04
- VAL05
- VAL06
- VAL07
- VAL08
- VAL09
- VAL10

How can I make that?

Note: I know the SORT function but it seems to be useless for such a case.

1
  • In real life, do you need to do this for just one cell? Or will you need to have this done for a range of cells? And if the latter, does that range run vertically, horizontally or both? (I understand that each cell will contain a list, but I need to know the larger context.) For best chances of getting the requested help, share a link to a spreadsheet set up with some realistic data (which accurately portrays your real-world application in both type and range). For instance, will those leading dashes be there? If not, don't include them in your sample data, etc.
    – Erik Tyler
    Commented Mar 13, 2022 at 3:25

2 Answers 2

2

Please see my important comment to your original post. However, if I go only by what is included in your post (i.e., that there is one cell with alphanumeric data wherein all numeric portions are normalized to contain the same number of digits and where each element is separated by a line break), then the solution below will do what you want.

Suppose your original data is in A2. In some unused cell, place the following formula:

=JOIN(CHAR(10),SORT(TRANSPOSE(SPLIT(A2,CHAR(10)))))

2
  • Pretty! I always learn a lot from you
    – Daniele
    Commented Mar 13, 2022 at 8:09
  • If we are doing things right in life, we are each learning from everyone along the way.
    – Erik Tyler
    Commented Mar 13, 2022 at 21:13
-1

that type of list will sort normally with the "sort range A to Z" Data's submenu

The SORT() function works well with something like:

=Sort(A2:A,1,true)

Of course the sort will fail with values of 3 digits mixed with those that only have 2

2
  • The question is about the content in a cell, not in a range.
    – Backo
    Commented Mar 12, 2022 at 21:23
  • I apologize for not understanding the question correctly
    – Daniele
    Commented Mar 13, 2022 at 8:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.