贊助廠商

學不完.教不停.用不盡文章列表

回答網友提問:參考下圖,如何在 Excel 的資料清單中取出序號中的某一段字元?

例如,序號中共有三段字元,分別以「-」隔開,如果要取出中間一段字元,而不採用手動「資料剖析」方式,該如何處理?

Excel-取出多段字元其中一段(非資料剖析,SUBSTITUTE,MID,REPT)

 

【公式設計與解析】

本例假設三段文字都不會超過20個字。

1. 字串長度固定

儲存格C2:=MID(A2,5,6)

複製儲存格C2,貼至儲存格C2:C15。

利用 MID 函數在儲存格A2的內容中第5個字起始,取6個字元。

 

2. 字串長度不固定

儲存格C2:=SUBSTITUTE(MID(SUBSTITUTE(A2,"-",REPT(" ",20)),20,20)," ","")

複製儲存格C2,貼至儲存格C2:C15。

(1) REPT(" ",20)

利用 REPT 函數產生20個空格。

(2) SUBSTITUTE(A2,"-",REPT(" ",20))

利用 SUBSTITUTE 函數,將儲存格A2內容中的「-」字元以20個空格取代。

(3) MID(SUBSTITUTE(A2,"-",REPT(" ",20)),20,20)

利用 MID 函數由第20個字取20個字元。

(4) SUBSTITUTE(MID(SUBSTITUTE(A2,"-",REPT(" ",20)),20,20)," ","")

最後再利用 SUBSTITUTE 函數,將第(3)式傳回值裡字串中的空格消除。

Excel-取出多段字元其中一段(非資料剖析,SUBSTITUTE,MID,REPT)

學不完.教不停.用不盡文章列表

文章標籤

vincent 發表在 痞客邦 留言(0) 人氣()

Google 表單拿來搜集資料很方便,但是如果使用者不乖乖的依規定格式輸入資料,後續取得資料時,恐怕得又要花一些時間來整理資料。如果能在獲取資料時,就能盡量正確,可以省下一些麻煩。

參考這篇:Google表單的驗證輸入資料的規則運算式

Google 表單裡運用「規則運算式」,可以幫的上忙。其中會用到:

【符號】

^:起始符號

$:結尾符號

[]:群集符號

{}:數量符號

【字元符號】

【例】\w:所有英文字元([a-zA-Z])

【例】\W:所有非英文字元([^a-zA-Z0-9])

【例】\d:所有數字([0-9])

【例】\D:所有非數字([^0-9])

【例】+ :前一個字元顯示至少1次({1,})

【例】? :前一個字元顯示0或1次({0,1})

【例】* :前一個字元顯示0或1次以上({0,})

詳細內容可參閱:https://support.google.com/docs/answer/3378864


常見的驗證有:

(1) Email位址

在 Google 表單中已設了以文字方式處理電子郵件地址格式,所以不需再使用運算式。

在Google表單中利用規則運算式驗證使用者輸入的資料


(2) 身分證字號

限定英文字大寫驗證,驗證運算式:^[A-Z][0-9]{9}$

不限英文字大寫驗證,驗證運算式:^[a-zA-Z][0-9]{9}$或是^\w[0-9]{9}$

在Google表單中利用規則運算式驗證使用者輸入的資料


(3) 手機號碼

假設使用格式:09+8位數字,格式:09XX-XXX-XXX

驗證運算式:^09\d{2}-\d{3}-\d{3}$,使用者必須自行輸入「-」。

在Google表單中利用規則運算式驗證使用者輸入的資料


(4) 信用卡號

信用卡卡號格式:16位數字,格式:XXXX-XXXX-XXXX-XXXX

驗證運算式:^\d{4}-\d{4}-\d{4}-\d{4}$,使用者必須自行輸入「-」。

在Google表單中利用規則運算式驗證使用者輸入的資料


你可以從以下的表單來測試:

文章標籤

vincent 發表在 痞客邦 留言(0) 人氣()

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼