Excel 儲存格字串串接的問題

王作桓

Dn198218.8F17841F3A6B4D33B6D43C03BA74C6C0(zh-tw,TechNet.10).png

2013年4月

關於外部資料轉入Excel,我們常用”Text to columns”精靈轉換成我們需要的格式。

Dn198218.B0D2C9978133A28BA862B8D2AB8DF797(zh-tw,TechNet.10).png

在實務應用上, 我需要反過來的功能-想要將ㄧ連串的員工帳號使用便程單一的儲存格並用分號「;」隔開(範例如下圖所示).:

Dn198218.D320FAA4DF55990BA2DD46F04E8D91FF(zh-tw,TechNet.10).png

因為追蹤員工訓練狀態,最多有超過500人需要追蹤與聯絡,請問用什麼方法可以快速的將多個存格中的員工帳號串接成一個長字串,並且在字串之間加上分號「;」而不是使用字串串接符號「&」來一格一格的將文字串在一起,那樣就太沒有效率了,此際Concatenate函數乎也派不上用場?

的確如此,Concatenate函數只能一格一格的串接文字,無法一次串接一整個範圍中的文字。

解決方法如下:

以前圖為例,我們可以在C1儲存格中使用下列函數完成字串的串接,並在每一個員工帳號之間加上分號「;」。

Dn198218.note(zh-tw,TechNet.10).gif=REPLACE(SUBSTITUTE(SUBSTITUTE(PHONETIC(A2:
A10),"N",";N"),"U",";U"),1,1,"")

說明:

Phontic:

官方的說明是「從文字字串中抽選注音標示 (假名註解) 字元」,但卻也可以用它來串接連續範圍中的所有文字,它比Concatenate函數更有彈性。

語法:PHONETIC(reference)

例如:PHONETIC(A2:A4) ==將儲存格A2:A4中的所有內容串接成為一長字串。

A2:T台北市 A3:T台中市 A4:K高雄市

PHONETIC(A2:A4)=”T台北市T台中市K高雄市”

要注意的是,儲存格中的資料一定要是文字,而且必須是連續的範圍;如果是數值或日期,將會被忽略。

A2:T台北市 A3:100 A4:2013/4/5 A5:K高雄市

PHONETIC(A2:A5)=”T台北市K高雄市”

Subtititute:

將文字字串中的指定字元以新字元來取代。

語法:SUBSTITUTE(text, old_text, new_text, [instance_num])

例如:SUBSTITUTE(PHONETIC(A2:A4),"T",";T") ==在字元”T”的前面加上分號「;」

A2:T台北市 A3:T台中市 A4:K高雄市

PHONETIC(A2:A4)= ”T台北市T台中市K高雄市”

SUBSTITUTE(PHONETIC(A2:A4),"T",";T")= SUBSTITUTE(”T台北市T台中市K高雄市”,"T",";T")=” ;T台北市;T台中市K高雄市”

SUBSTITUTE(SUBSTITUTE(PHONETIC(A2:A4),"T",";T"),"K",";K")==最後在字元”K”的前面加上分號「;」

SUBSTITUTE(PHONETIC(A2:A4),"T",",T")= SUBSTITUTE(”T台北市T台中市K高雄市”,"T",",T")= SUBSTITUTE(”;T台北市;T台中市K高雄市” ,"K",";K")=” ;T台北市;T台中市;K高雄市”

Replace:

根據指定的字元數,以不同的文字字串來取代文字字串的某一部分。

語法:REPLACE(old_text, start_num, num_chars, new_text)

例如:REPLACE(SUBSTITUTE(SUBSTITUTE(PHONETIC(A2:A4),"T",";T"),"K",";K"),1,1,"") ==刪除長字串中的第一個分號「;」

A2:T台北市 A3:T台中市 A4:K高雄市

REPLACE(SUBSTITUTE(SUBSTITUTE(PHONETIC(A2:A4),"T",";T"),"K",";K"),1,1,"")= REPLACE(” ;T台北市;T台中市;K高雄市”,1,1,"")= ” T台北市;T台中市;K高雄市”

顯示: