2015年1月14日 星期三

Excel-自動產生文字編號的號碼(LEFT,RIGHT,LEN)

有網友提供一個 Excel 工作表裡中一串由多個文數字組成的編號,希望藉由公式能自動產生最末一碼由 0 ~ 9 變化,該何處理呢?

因為儲存格A1中的這個文數字內容被 Excel 視為文字,無法使用「自動填滿方式」來產生和等差數列般的數值連續變化,只好藉由公式來幫忙了!

(1) 最後一碼產生 0 ~ 9

儲存格A3:=LEFT($A$2,LEN($A$2)-1)&ROW(1:1)

LEN($A$2):使用 LEN 函數計算儲存格A2內容的文字長度(即為字數)。

LEFT($A$2,LEN($A$2)-1):使用 LEFT 函數取出儲存格A2內容中,除了最右邊一個字以外的文字。

ROW(1:1):ROW(1:1)=1,當往下複製時,ROW(1:1)=1會變為 ROW(2:2)=2、ROW(3:3)=3、...、ROW(9:9)=9。

複製儲存格A3,往下各列貼上。

 

(2) 最後二碼產生 00 ~ 99

儲存格C3:=LEFT($A$2,LEN($A$2)-2)&RIGHT("0"&ROW(1:1),2)

LEFT($A$2,LEN($A$2)-2):使用 LEFT 函數取出儲存格A2內容中,除了最右邊二個字以外的文字。

RIGHT("0"&ROW(1:1),2):將 ROW(1:1) 的結果先串接一個「0」,再由 RIGHT 函數從文字由最右邊取二個字。如此可以讓 0 ~ 9 的前方補 0,而 10 ~ 99 不受影響。

複製儲存格A3,往下各列貼上。

 

【想一想】

如果要末三碼產生數列的編碼,該如何修改公式呢?

公式修改為:=LEFT($A$2,LEN($A$2)-3)&RIGHT("00"&ROW(1:1),3)

末三碼產生數列變化的編碼公式,可以含蓋以上一碼變化和二碼變化的公式。

沒有留言:

張貼留言

檢視其他文章

好康東東