整理房间号

问题：

 

函数解决：

G1公式：{=SUBSTITUTE(INDEX(LEFT(A$1:A$13,2)&"-"&TRIM(MID(SUBSTITUTE(A$1:A$13,"-",REPT(" ",30)),COLUMN(B:G)*30-29,30)),SMALL(IF(LEN(TRIM(MID(SUBSTITUTE(A$1:A$13,"-",REPT(" ",30)),COLUMN(B:G)*30-29,30)))>2,ROW($1:$13)*100+COLUMN(A:F)),ROW(A1))/100,MOD(SMALL(IF(LEN(TRIM(MID(SUBSTITUTE(A$1:A$13,"-",REPT(" ",30)),COLUMN(B:G)*30-29,30)))>2,ROW($1:$13)*100+COLUMN(A:F)),ROW(A1)),100)),"东1",,2)}
H1公式：{=MOD(SMALL(IF(LEN(TRIM(MID(SUBSTITUTE(SUBSTITUTE(A$1:A$13," ",REPT(" ",30)),"-",REPT(" ",30)),COLUMN(B:G)*30-29,30)))>2,ROW($1:$13)*10^8+COLUMN(A:F)*10^6+B$1:B$13),ROW(A1)),10^5)}