Search code examples
excelexcel-formuladata-cleaning

Sort multiline cell elements by number with string prefix and remove unnecessary spaces

In my current projects it is necessary from time to time to compare two different columns of an excel. To ease the comparison, it would be very useful to do a transformation of the cell contents beforehand.

Every cell has a multiline content consisting of lines of the form "FixedPrefix_Number", e.g. "X_463". Sometimes there are also unnecessary spaces between the different lines.

How can I first remove the unnecessary spaces and then sort the lines by the number (not lexicographic), but really by the number, i.e. X_52 comes before X_124.

This should be possible with some kind of VBA script, but I am really not a Pro in Excel. The column B in the picture points out, what I want to achieve..

Example on what I wanted to achieve

Solution

  • Assuming there is no Excel Constraints as per the tags posted then the following formula should work:

    enter image description here

    =MAP(A2:A5,LAMBDA(x,TEXTJOIN(CHAR(10),1,LET(y, TEXTSPLIT(x,,CHAR(10),1),SORTBY(y,--TEXTAFTER(y,"_"))))))

    Or, One could use the following to fill down:

    =LET(x,TEXTSPLIT(A2,,CHAR(10),1),TEXTJOIN(CHAR(10),1,SORTBY(x,--TEXTAFTER(x,"_"))))