• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel 2016: Sorting text having numbers

syp1677

Member
I am having problem in sorting text with numbers as described in below pic:
82667

I am using Excel 2016, I am using below array formula in D2:
=IFERROR(SMALL(IF((COUNTIF($D$1:D1, $A$1:$A$10)=0)*ISNUMBER($A$1:$A$10), $A$1:$A$10, "A"), 1), INDEX($A$1:$A$10, MATCH(SMALL(IF(ISTEXT($A$1:$A$10)*(COUNTIF($D$1:D1, $A$1:$A$10)=0), COUNTIF($A$1:$A$10, "<"&$A$1:$A$10), ""), 1), IF(ISTEXT($A$1:$A$10), COUNTIF($A$1:$A$10, "<"&$A$1:$A$10), ""), 0)))

Best Regards,
S
 

Attachments

Last edited:
You have Power Query, so right-click the table in column C after changing the contents of column A. The sort is on the number(s) between the first alpha to numeric change in the string, to the next numeric to alpha change in the string; the red below:
R3-1.C
R6-1.F
R7-1.G
R1-1.A
R2-1.B
R4-1.D
R5-1.E
R10-1.J
R9-1.I
R8-1.H
 

Attachments

You have Power Query, so right-click the table in column C after changing the contents of column A. The sort is on the number(s) between the first alpha to numeric change in the string, to the next numeric to alpha change in the string; the red below:
R3-1.C
R6-1.F
R7-1.G
R1-1.A
R2-1.B
R4-1.D
R5-1.E
R10-1.J
R9-1.I
R8-1.H
Thank you so much.
Is there anyway we can do using array function
 
You can also try this one
complete the formula by pressing CTRL_SHIFT+ENT

=IF(ISBLANK(A1),"",INDEX($A$1:$A$10,MATCH(SMALL(1*MID($A$1:$A$10,2,FIND("-",$A$1:$A$10)-2),ROW(A1)),1*MID($A$1:$A$10,2,FIND("-",$A$1:$A$10)-2),0)))

R6-1.FR1-1.A
6​
R1-1.A
R7-1.GR2-1.B
7​
R2-1.B
R3-1.CR3-1.C
3​
R3-1.C
R1-1.AR4-1.D
1​
R4-1.D
R2-1.BR5-1.E
2​
R5-1.E
R4-1.DR6-1.F
4​
R6-1.F
R5-1.ER7-1.G
5​
R7-1.G
R9-1.IR8-1.H
9​
R8-1.H
R10-1.JR9-1.I
10​
R9-1.I
R8-1.HR10-1.J
8​
R10-1.J
 
Back
Top