EXCEL 公式運算

A1應該放入欄位標籤「頭銜」(Title)，A2公式為「=IF(H2="男","Mr",IF(H2="女","Ms",""))」
在D2儲存格「郵遞收件人」公式在沒有逗號時不能操作，所以修改為：
=SUBSTITUTE(IF(LEN(TRIM(A2))>0,PROPER(TRIM(A2))&" ","")&UPPER(LEFT(B2,FIND(" ", B2)))&PROPER(MID(B2,FIND(" ", B2)+1,IF(ISNUMBER(FIND(" ", B2,FIND(" ", B2)+1)),FIND(" ", B2,FIND(" ", B2)+1)-FIND(" ", B2)-1,LEN(B2)-FIND(" ", B2))))&IF(LEN(B2)-LEN(SUBSTITUTE(B2," ",""))>=2,"-"&LOWER(RIGHT(B2,LEN(B2)-FIND(" ", B2,FIND(" ", B2)+1))),""),LOWER(RIGHT(B2,LEN(B2)-IF(ISNUMBER(FIND(",",B2)),FIND(",",B2)+1,LEN(B2)))),PROPER(RIGHT(B2,LEN(B2)- IF(ISNUMBER(FIND(",",B2)),FIND(",",B2)+1,LEN(B2)))))

至於郵遞收件人是中文姓名要加「先生」或「小姐」，是否放在「頭銜」同一行上嗎(即是男士名是中文用「先生」，是英文用「Mr」)？

2015-05-27 16:19:22 補充：
可將A2公式修改為：
=IF(CODE(LEFT(B2,1))>255,IF(H2="男","先生",IF(H2="女","小姐","")),IF(H2="男","Mr",IF(H2="女","Ms","")))

2015-05-27 16:20:46 補充：
在「郵遞收件人」D2儲存格公式相應修改為：
=IF(CODE(LEFT(A2,1))<256,IF(LEN(TRIM(A2))>0,PROPER(TRIM(A2))&" ","")&SUBSTITUTE(UPPER(LEFT(B2,FIND(" ", B2)))&PROPER(MID(B2,FIND(" ", B2)+1,IF(ISNUMBER(FIND(" ", B2,FIND(" ", B2)+1)),FIND(" ", B2,FIND(" ", B2)+1)-FIND(" ", B2)-1,LEN(B2)-FIND(" ", B2))))&

2015-05-27 16:21:10 補充：
IF(LEN(B2)-LEN(SUBSTITUTE(B2," ",""))>=2,"-"&LOWER(RIGHT(B2,LEN(B2)-FIND(" ", B2,FIND(" ", B2)+1))),""),LOWER(RIGHT(B2,LEN(B2)-IF(ISNUMBER(FIND(",",B2)),FIND(",",B2)+1,LEN(B2)))),PROPER(RIGHT(B2,LEN(B2)- IF(ISNUMBER(FIND(",",B2)),FIND(",",B2)+1,LEN(B2))))),TRIM(B2)&TRIM(A2))

2015-05-27 16:24:58 補充：
由於公式太長，請將兩段自行接駁，中間不能有空格。

2015-05-28 16:51:04 補充：
由於並未提及「台啟」、「台鑒」、「收」在甚麼情況用那個詞，假設可能自訂每次不同，請自行置換公式中「台啟」字眼(但不要覆蓋了「台啟」前面的空格)：
=IF(CODE(LEFT(A2,1))<256,IF(LEN(TRIM(A2))>0,PROPER(TRIM(A2))&" ","")&SUBSTITUTE(UPPER(LEFT(B2,FIND(" ", B2)))&

2015-05-28 16:52:13 補充：
PROPER(MID(B2,FIND(" ", B2)+1,IF(ISNUMBER(FIND(" ", B2,FIND(" ", B2)+1)),FIND(" ", B2,FIND(" ", B2)+1)-FIND(" ", B2)-1,LEN(B2)-FIND(" ", B2))))&IF(LEN(B2)-LEN(SUBSTITUTE(B2," ",""))>=2,

2015-05-28 16:53:05 補充：
"-"&LOWER(RIGHT(B2,LEN(B2)-FIND(" ", B2,FIND(" ", B2)+1))),""),LOWER(RIGHT(B2,LEN(B2)-IF(ISNUMBER(FIND(",",B2)),FIND(",",B2)+1,LEN(B2)))),

2015-05-28 16:53:22 補充：
PROPER(RIGHT(B2,LEN(B2)- IF(ISNUMBER(FIND(",",B2)),FIND(",",B2)+1,LEN(B2))))),TRIM(B2)&TRIM(A2)&"　台啟")

2015-05-28 16:54:26 補充：
由於公式太長，請將四段自行接駁，中間不能有空格。