CODEWITHSUNDEEP
excelexcel-formula
user761065
user761065

Reputation: 123

Order the numbers of each row

I have a formula =SMALL(RIGHT($A3:$f3,1),{1,2,3,4,5,6}) and it works fine to extract one digit from the data. Now I have a different set of data but I couldn't make it work with these data. The data I have is like this:

L18 L2  R3  R19 R21 R22
L23 L12 L15 L17 L2  R13
L11 L17 L2  R2  R9  R24
L22 L5  L9  R9  R24 R27

With the function, I should be able to get these results:

2   3   18  19  21  22
2   12  13  15  17  23
2   2   9   11  17  24
5   9   9   22  24  27

So they have one letter and 1 or 2 digit numbers at the end. For each row, I would like to extract the digits and arrange them from smallest to largest. How can I do that?

Upvotes: 1

Views: 82

Answers (2)

CallumDA
CallumDA

Reputation: 12113

Try this:

=SMALL(--MID($A1:$F1,2,255),{1,2,3,4,5,6})

enter image description here

Given your initial formula I presume you know that this is an array formula and needs to be entered using Ctrl+Shift+Enter

Upvotes: 1

Praveen DA
Praveen DA

Reputation: 354

you can do this in two Phase

As given below

=VALUE(MID(A2,LEN(LEFT(A2,1))+1,LEN(A2)-1))

=SMALL($A8:$F8,1)

enter image description here

Upvotes: 1

Related Questions