Excel 2016: Sorting text having numbers

[syp1677]

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


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

 

[p45cal]

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

 

[syp1677]

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

 

[vletm]

syp1677
As Ask an Excel Question
You could solve this like this sample's five steps.

 

[bosco_yip]

Or,

In D2, copied down:

=IF(ROW(A1)<=COUNTA(A$1:A$99),INDEX(A$1:A$10,MATCH(ROW(A1),0+MID(A$1:A$10,2,FIND("-",A$1:A$10)-2),0)),"")

 

[Naresh Yadav]

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.F	R1-1.A	6​	R1-1.A
R7-1.G	R2-1.B	7​	R2-1.B
R3-1.C	R3-1.C	3​	R3-1.C
R1-1.A	R4-1.D	1​	R4-1.D
R2-1.B	R5-1.E	2​	R5-1.E
R4-1.D	R6-1.F	4​	R6-1.F
R5-1.E	R7-1.G	5​	R7-1.G
R9-1.I	R8-1.H	9​	R8-1.H
R10-1.J	R9-1.I	10​	R9-1.I
R8-1.H	R10-1.J	8​	R10-1.J

 

[syp1677]

@Naresh Yadav, @bosco_yip and @vletm
Thank you so much..