2

## 請問excel怎麼取出字串中的數字轉換成國字，字串無固定規則

randyjian414 4 月前3872 瀏覽

1. 英文字可能出現在字串任何位置
2. 英文字母沒有固定位置和字元數

14d → 十四Ｄ
A223→ Ａ二二三
1A17B→ 一Ａ十七Ｂ

2016/12/28補充

27 → 二十七
1348 → 一三四八

2016/12/28回應，(抱歉還沒過新手任務，請容許小弟用編輯的方式回應)

wonton iT邦高手 6 級 ‧ 4 月前 檢舉

### 2 個回答

3
wonton
iT邦高手 6 級 ‧ 4 月前

http://blog.twtnn.com/2014/09/excel.html

``````Function Num2Str(Str As String) As String
For i = 1 To Len(Str)
a = Mid(Str, i, 1)
Select Case True
Case a = 0
aa = aa & "零"
Case a = 1
aa = aa & "一"
Case a = 2
aa = aa & "二"
Case a = 3
aa = aa & "三"
Case a = 4
aa = aa & "四"
Case a = 5
aa = aa & "五"
Case a = 6
aa = aa & "六"
Case a = 7
aa = aa & "七"
Case a = 8
aa = aa & "八"
Case a = 9
aa = aa & "九"
Case Else
aa = aa & a
End Select
Next i
Num2Str = aa
End Function
``````

wonton iT邦高手 6 級 ‧ 4 月前 檢舉

17是一七還是十七

223是二二三而不是兩百二十三

wonton iT邦高手 6 級 ‧ 4 月前 檢舉

``````Function StrParse(Str As String) As String
Dim a_str(10)
Index = 1
For i = 1 To Len(Str)
a = Mid(Str, i, 1)
If IsNumeric(a) Then
a_str(Index) = a_str(Index) & a
For j = i + 1 To Len(Str)
b = Mid(Str, j, 1)
If IsNumeric(b) Then
a_str(Index) = a_str(Index) & b
i = j
Else
i = j - 1
j = Len(Str)
Index = Index + 1
End If
Next j
Else
a_str(Index) = a_str(Index) & a
For j = i + 1 To Len(Str)
b = Mid(Str, j, 1)
If IsNumeric(b) Then
i = j - 1
j = Len(Str)
Index = Index + 1
Else
a_str(Index) = a_str(Index) & b
i = j
End If
Next j
End If
Next i

For i = 1 To 10
If IsNumeric(a_str(i)) And Len(a_str(i)) = 2 Then
aa = aa & DoubleDigits2Str(CStr(a_str(i)))
Else
aa = aa & Num2Str(CStr(a_str(i)))
End If
Next i

StrParse = aa
End Function

Function DoubleDigits2Str(Str As String) As String
a = Mid(Str, 1, 1)
Select Case True
Case a = 0
aa = Num2Str(Mid(Str, 1, 1)) & Num2Str(Mid(Str, 2, 1))
Case a = 1
aa = "十" & Num2Str(Mid(Str, 2, 1))
Case Else
aa = Num2Str(Mid(Str, 1, 1)) & "十" & Num2Str(Mid(Str, 2, 1))
End Select

DoubleDigits2Str = aa
End Function

Function Num2Str(Str As String) As String
For i = 1 To Len(Str)
a = Mid(Str, i, 1)
Select Case True
Case a = 0
aa = aa & "零"
Case a = 1
aa = aa & "一"
Case a = 2
aa = aa & "二"
Case a = 3
aa = aa & "三"
Case a = 4
aa = aa & "四"
Case a = 5
aa = aa & "五"
Case a = 6
aa = aa & "六"
Case a = 7
aa = aa & "七"
Case a = 8
aa = aa & "八"
Case a = 9
aa = aa & "九"
Case Else
aa = aa & a
End Select
Next i
Num2Str = aa
End Function
``````

1

iT邦超人 1 級 ‧ 4 月前

``````//處理 A1
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"0","零"),"1","一"),"2","二"),"3","三"),"4","四"),"5","五"),"6","六"),"7","七"),"8","八"),"9","九")
``````

wonton iT邦高手 6 級 ‧ 4 月前 檢舉

wonton iT邦高手 6 級 ‧ 4 月前 檢舉

.
.
.