close

4282

Excel如何將不規則資料排序-長度不同

如果在Excel工作表中A欄,資料如下,如果要將二個減號(-)中間數值不足位數前面補0,由小到大排序,要如何處理呢?

image

一、建立輔助欄

首先我們要將資料排序的數值擷取出來到B欄方便我們依據該值排序。

點取B1儲存格輸入「輔助欄」字串,可以由使用者自行設定。

點取B2儲存格輸入「=TEXT(MID(A2,FIND("-",A2,1)+1,FIND("-",A2,FIND("-",A2,1)+1)-FIND ("-",A2,1)-1),"000000")」後,將公式複製到B3:B11儲存格。

image

如果要不足位數後面補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)))」。

image

二、排序

當我們將要排序的數值擷取出來後,接著就是排序囉!

點取B2:B11中任一儲存格,再點取「常用 > 排序與篩選 > 從A到Z排序」指令。

image

結果如下圖。

image

這時只要將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"))


arrow
arrow
    創作者介紹

    錦子老師 發表在 痞客邦 留言(0) 人氣()