Excel set name format

設 CHAN, TAI MAN 在儲存格A1，可在任意儲存格輸入

=LEFT(A1,FIND(",",A1)+2)&SUBSTITUTE(LOWER(MID(A1,FIND(",",A1)+3,LEN(A1)))," ","-")

2012-05-19 13:50:49 補充：
SORRY 漏了省去 ","

正確應為：

=SUBSTITUTE(LEFT(A1,FIND(",",A1)+2),",","")&SUBSTITUTE(LOWER(MID(A1,FIND(",",A1)+3,LEN(A1)))," ","-")

我比較喜歡用自定函數解決這個問題，因為會較易明白，程式碼如下：

Function FormattedName(ByRef Name As String) As String
Dim i As Integer
Dim isComma, isFirstLetter As Boolean

For i = 1 To Len(Name)
If isComma = False Then
If Mid(Name, i, 1) = "," Then
isComma = True
FormattedName = FormattedName + Space(1)
Else
FormattedName = FormattedName + Mid(Name, i, 1)
End If
ElseIf isFirstLetter = False Then
If Mid(Name, i, 1) <> Space(1) Then
isFirstLetter = True
FormattedName = FormattedName + UCase(Mid(Name, i, 1))
End If
Else
If Mid(Name, i, 1) = Space(1) Then
FormattedName = FormattedName + "-"
Else
FormattedName = FormattedName + LCase(Mid(Name, i, 1))
End If
End If
Next
End Function

這個自定函數連複姓和多字名也一併考慮，實用性和可素生較高。