4282 |
Excel如何將不規則資料排序-長度不同 |
如果在Excel工作表中A欄,資料如下,如果要將二個減號(-)中間數值不足位數前面補0,由小到大排序,要如何處理呢?
一、建立輔助欄
首先我們要將資料排序的數值擷取出來到B欄方便我們依據該值排序。
點取B1儲存格輸入「輔助欄」字串,可以由使用者自行設定。
點取B2儲存格輸入「=TEXT(MID(A2,FIND("-",A2,1)+1,FIND("-",A2,FIND("-",A2,1)+1)-FIND ("-",A2,1)-1),"000000")」後,將公式複製到B3:B11儲存格。
如果要不足位數後面補0,則可以將公式更改為「=MID(A2,FIND("-",A2,1)+1,FIND("-",A2,FIND ("-",A2,1)+1)-FIND("-",A2,1)-1)&REPT(0,6-LEN(MID(A2,FIND("-",A2,1)+1,FIND("-",A2,FIND ("-",A2,1)+1)-FIND("-",A2,1)-1)))」。
二、排序
當我們將要排序的數值擷取出來後,接著就是排序囉!
點取B2:B11中任一儲存格,再點取「常用 > 排序與篩選 > 從A到Z排序」指令。
結果如下圖。
這時只要將B1:B11框選起來,再按DELETE鍵清除內容即可。
三、公式說明
=TEXT(MID(A2,FIND("-",A2,1)+1,FIND("-",A2,FIND("-",A2,1)+1)-FIND("-",A2,1)-1),"000000")
首先找到第一個減號的位置(FIND("-",A2,1))。
再找到第二個減號的位置(FIND("-",A2,FIND("-",A2,1))。
透過一MID函數抓取二個減號間的字元,由於第一個減號所在位置不是要抓取的第一個字元,必須由再加1位置開始才是第一個字元位置,而第二個減號的位置減掉第一個減號的位置會包含第二個減號,故必須將減後數值再減一才是真正要居的數值長度(MID(A2,FIND("-",A2,1)+1,FIND("-",A2,FIND("-",A2,1)+1)-FIND ("-",A2,1)-1))
而不足位數要補0,則可以透過TEXT函數將其格式設成6個0,不足位數即會補0(TEXT(MID(A2, FIND("-",A2,1)+1,FIND("-",A2,FIND("-",A2,1)+1)-FIND("-",A2,1)-1),"000000"))
留言列表