帖子: 71
主題: 26
精華: 0
積分: 102
點名: 0
作業系統: window 7
軟體版本: office 2010
閱讀權限: 20
性別: 女
來自: HK
註冊時間: 2010-9-23
最後登錄: 2022-6-24

1^# 跳轉到 » 倒序看帖

字體大小: tT

發表於 2018-9-5 16:03 | 只看該作者

[發問] 求一次修正姓名輸入模式的公式

本帖最後由 hkyan 於 2018-9-5 16:05 編輯

要把舊資料一次整理,求公式除去豆號及加回MR 在前, 還有之前轉入有少錯誤(豆號後沒有space),有能一次糾正嗎?
[attach]29345[/attach] 希望效果如此

NAME.rar (6.07 KB)

name update.JPG (27.36 KB)

樓主熱帖

努力學習中,謝謝前輩

faye59

　暱稱: 温奇
　頭銜: 議論者

中學生 Rank: 2

帖子: 104
主題: 5
精華: 0
積分: 110
點名: 0
作業系統: win7
軟體版本: Office2007
閱讀權限: 20
性別: 男
註冊時間: 2017-11-14
最後登錄: 2019-4-30

論壇榮譽勳章論壇贊助勳章

2^#

發表於 2018-9-6 06:46 | 只看該作者

回復 1# hkyan

="MR"&SUBSTITUTE(MID(A1,4,99),",","")
下拉

一分努力，一分收穫。
發問題前可以先搜索內文是否有相關範例。

TOP

hkyan

中學生 Rank: 2

帖子: 71
主題: 26
精華: 0
積分: 102
點名: 0
作業系統: window 7
軟體版本: office 2010
閱讀權限: 20
性別: 女
來自: HK
註冊時間: 2010-9-23
最後登錄: 2022-6-24

3^#

發表於 2018-9-6 12:11 | 只看該作者

回復 2# faye59

處理過程中,問題來了
1) 倘若外藉客沒有中文姓名的, 便會出錯. 例如
CHOU, QUANTAI 修正後變作 MR U QUANTAI

2) 上文提到部份姓名在,號之後沒有space的小錯誤, 是否就不能修正?
現變作這樣MR YIMWAI LEUNG, 能於公式內一并修正為 MR YIM WAI LEUNG 嗎?

努力學習中,謝謝前輩

TOP

准提部林

版主 Rank: 7 Rank: 7 Rank: 7

帖子: 2833
主題: 10
精華: 0
積分: 2889
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2024-11-11

4^#

發表於 2018-9-6 13:19 | 只看該作者

回復 3# hkyan

文字粘在一起, 無分隔符號, 公式是無法判別的!!

TOP

faye59

　暱稱: 温奇
　頭銜: 議論者

中學生 Rank: 2

帖子: 104
主題: 5
精華: 0
積分: 110
點名: 0
作業系統: win7
軟體版本: Office2007
閱讀權限: 20
性別: 男
註冊時間: 2017-11-14
最後登錄: 2019-4-30

論壇榮譽勳章論壇贊助勳章

5^#

發表於 2018-9-6 22:16 | 只看該作者

回復 3# hkyan

不然用vba試試，

NAME.rar (22.13 KB)
用了笨一點的方法達到你要的效果。

Private Sub CommandButton1_Click()
For Each aa In Sheets("工作表1").Range([A1], [A1].End(xlDown)) '讀取A欄全部文字
For i = 1 To Len(aa)
If Mid(aa, i, 1) = "," And Mid(aa, i + 1, 1) <> " " Then '逗點後沒有空白執行加入空白
ar = ar & ", "
Else
ar = ar & Mid(aa, i, 1)
End If
Next
aa.Offset(, 1) = StrType(ar)
ar = ""
Next
End Sub
Function StrType(Mystr)
For i = 1 To Len(Mystr)
k = AscW(Mid(Mystr, i))
Select Case k
Case 32 '空格
Textstring = Textstring & Mid(Mystr, i, 1)
Case 48 To 57 '數字
Textstring = Textstring & Mid(Mystr, i, 1)
Case 65 To 90, 97 To 122 '英文
Textstring = Textstring & Mid(Mystr, i, 1)
Case 0 To 47, 58 To 64, 91 To 96, 122 To 255 '符號
Textstring = Textstring & ""
End Select
Next
StrType = "MR " & Textstring
If Mid(StrType, 4, 1) = " " Then StrType = Mid(StrType, 1, 3) & Mid(StrType, 5, 99) '判斷第四碼是否為空白
End Function

複製代碼

一分努力，一分收穫。
發問題前可以先搜索內文是否有相關範例。

TOP

hkyan

中學生 Rank: 2

帖子: 71
主題: 26
精華: 0
積分: 102
點名: 0
作業系統: window 7
軟體版本: office 2010
閱讀權限: 20
性別: 女
來自: HK
註冊時間: 2010-9-23
最後登錄: 2022-6-24

6^#

發表於 2018-9-7 11:43 | 只看該作者

回復 5# faye59

嘗試中, 另一問有關上回教取替,的公式
=SUBSTITUTE(MID(A1,1,99),",","")

倘若名稱舊有輸入為 CHING, LOK-MAN需修正為 CHING LOK MAN,那麼多條件取替要如何設置才能同時處理 , 及 -

努力學習中,謝謝前輩

TOP

hkyan

中學生 Rank: 2

帖子: 71
主題: 26
精華: 0
積分: 102
點名: 0
作業系統: window 7
軟體版本: office 2010
閱讀權限: 20
性別: 女
來自: HK
註冊時間: 2010-9-23
最後登錄: 2022-6-24

7^#

發表於 2018-9-7 12:37 | 只看該作者

回復 6# hkyan

CHING, LOK-MAN需修正為 CHING LOK MAN才是

努力學習中,謝謝前輩

TOP

faye59

　暱稱: 温奇
　頭銜: 議論者

中學生 Rank: 2

帖子: 104
主題: 5
精華: 0
積分: 110
點名: 0
作業系統: win7
軟體版本: Office2007
閱讀權限: 20
性別: 男
註冊時間: 2017-11-14
最後登錄: 2019-4-30

論壇榮譽勳章論壇贊助勳章

8^#

發表於 2018-9-9 21:14 | 只看該作者

回復 6# hkyan

可以搜尋Asc函數字元碼對照表看看，
下面是修改-這個符號，字元碼為45，在Function中的Select Case中加入一個條件即可。
註解我再打詳細一點。

Private Sub CommandButton1_Click()
For Each aa In Sheets("工作表1").Range([A1], [A1].End(xlDown))'迴圈讀取A欄所有資料，往下讀取
For i = 1 To Len(aa)'迴圈該欄位文字中長度
If Mid(aa, i, 1) = "," And Mid(aa, i + 1, 1) <> " " Then'如果讀到逗號並確認後方是否不為空白，兩者條件均符合執行逗號後面加入空白，如果條件均不成立當前文字加入ar字串中
ar = ar & ", "
Else
ar = ar & Mid(aa, i, 1)
End If
Next
aa.Offset(, 1) = StrType(ar)'判讀字串完畢後ar中的條件，跳入Function程序，結果回傳aa往右加一格位置
ar = ""'清空字串
Next
End Sub
Function StrType(Mystr)
For i = 1 To Len(Mystr)'迴圈該欄位文字中長度
k = AscW(Mid(Mystr, i))'取得的該文字Asc函數轉換字元碼
Select Case k'選取字元碼符合Case中哪一個條件
Case 32 '空格
Textstring = Textstring & Mid(Mystr, i, 1)
Case 48 To 57 '數字
Textstring = Textstring & Mid(Mystr, i, 1)
Case 65 To 90, 97 To 122 '英文
Textstring = Textstring & Mid(Mystr, i, 1)
Case 0 To 44, 46 To 47, 58 To 64, 91 To 96, 122 To 255 '符號
Textstring = Textstring & ""
Case 45 '像是這裡 - 這個符號的字元碼為45，在字串中把它改為空格就可以
Textstring = Textstring & " "
End Select
Next
StrType = "MR " & Textstring'字串完畢後在字串前加入MR+空格，回傳完整字串
If Mid(StrType, 4, 1) = " " Then StrType = Mid(StrType, 1, 3) & Mid(StrType, 5, 99)'如果文字中第四碼這一個字為空白，重新字串1~3及5~最後一碼，等同於刪除第四碼空格
End Function