通過我的作業和復制他人，我拼湊了一個 Excel VBA Sub，它將帶有（文本組）和（數字組）組的長字串分隔成一個替換字串，每個單獨的組之間有空格；根據此示例： ? “123abc12aedsw2345der” ? …應用選擇 Sub() 然后變為： ? “123 abc 12 aedsw 2345 der” 它根據“選擇”將字串轉換為原始單元格中的字串，因此我目前只剩下原始單元格中的更改資料問題：我想將其更改為一個函式，其中轉換后的資料將出現在函式單元格中，并且保持原始細胞完好無損。我已經完成了數百個這樣的作業，但我似乎無法讓它作為一個獨立的功能作業。在完成和作業的子例程下面，我試圖轉換為一個獨立的函式，以便從作業表上的任何位置呼叫：
Sub SplitTextNumbersSelection()
Dim c As Range
'********** Inserts Space Before Number Groups ******************************
For n = 1 To 10
For Each c In Selection
c = InsertSpace(c.Text)
Next
Next n
'****************Inserts Space Before Letter Groups ***********************
For n = 1 To 10
For Each c In Selection
c = InsertSpace2(c.Text)
Next
Next n
'****************************************
End Sub
Function InsertSpace(str As String) As String
With CreateObject("vbscript.regexp")
.Pattern = "([a-z])(\d)"
'.Pattern = "(\d)([a-z])"
InsertSpace = .Replace(str, "$1 $2")
End With
End Function
Function InsertSpace2(str As String) As String
With CreateObject("vbscript.regexp")
'.Pattern = "([a-z])(\d)"
.Pattern = "(\d)([a-z])"
InsertSpace2 = .Replace(str, "$1 $2")
End With
End Function
uj5u.com熱心網友回復：
簡單一點：
Function PrepString(v As String)
Dim rv As String, i As Long, c1, c2
For i = 1 To Len(v) - 1
c1 = Mid(v, i, 1)
c2 = Mid(v, i 1, 1)
If (c1 Like "[a-z]" And c2 Like "[0-9]") Or _
(c2 Like "[a-z]" And c1 Like "[0-9]") Then
rv = rv & c1 & " "
Else
rv = rv & c1
End If
Next i
PrepString = rv & Right(v, 1)
End Function
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/432889.html
上一篇：將列均值作為函式的一部分