Sort Values Lowest to Highest

[KatieTrev]

Hello,

I'm looking for some help on sorting values from lowest to highest.

I have rows of data with numbers in each cell separated with the separator and are in a random order.
I would like to the values in each row to sort from lowest to highest.

Please see example attached.

Any help would be appreciated.

Thank you,

Katie

[hrlngrv]

First, data quality. Cell A1 contains 0.1|2.2|.4.9|9.9|5.2|1|6|3.3. .4.9 isn't a valid number. Should that be 4.9? Note that A1 also contains 1 as 1; on the other hand, A4 contains 9.0. Is it necessary to retain the .0 in 9.0? I'm going to assume A1 should contain 4.9 and 9 is interchangeable with 9.

If you're using Excel 2013 (2103 won't be released for another 81 years), this would be most efficiently done using VBA macros. However, since you're asking this in the General forum, I'll figure I need to stick with formulas.

This needs to be a multiple cell per result approach.

M1:

Please Login or Register  to view this content.

N1:

Please Login or Register  to view this content.

Fill N1 right into O1:U1.

C1:

Please Login or Register  to view this content.

Select C1:U1 and fill down into C2:U4. This puts sorted, |-separated arrays into cells in column C.

Now for VBA. With Excel running, save your workbook as either an .XLSM or .XLSB file so that it will retain VBA modules. Press [Alt]+[F11] to display the VBA Editor. There should be a pane in the upper-left which shows labels followed by workbook names in parentheses. Select the entry for your workbook. Issue the VBA Editor menu command Insert > Module to add a general VBA module to the workbook. Copy the code below and paste it into that VBA module.

Please Login or Register  to view this content.

Switch back to Excel, and try the following formula.

G1:

Please Login or Register  to view this content.

If that works, fill G1 down into G2:G4.