我正在尋找一些 VBA 的幫助。
我有下一張桌子
| header1 |
|---|
| 000模型測驗0模型值00用戶0 |
| Perman000User0Model Name000000 |
| 000Perman00000000000000000000名稱 |
所以我需要像這樣用一個“，”替換所有的 Ceros
| header1 |
|---|
| ,模型測驗,模型驗證,用戶, |
| Perman，用戶，模型名稱， |
| ,Perman,姓名 |
是否有公式的組合來做到這一點？或者用 VBA 中的代碼？
uj5u.com熱心網友回復：
請嘗試下一個功能：
Function replace0(x As String) As String
Dim matches As Object, mch As Object, arr, k As Long
ReDim arr(Len(x))
With CreateObject("VbScript.regexp")
.Pattern = "^[0] $"
.Global = True
If .test(x) Then
replace0 = .replace(x, ",")
End If
End With
End Function
可以使用以下方法對其進行測驗：
Sub replaceAllzeroByComma()
Dim x As String
x = "000Perman00000000000000000000Name"
'x = "000Model Test0Model Val00User0"
'x = "Perman000User0Model Name000000"
Debug.Print replace0(x)
End Sub
Immediate Window一次取消選中選中的行，然后在(中查看結果Ctrl G，在 VBE 中)
uj5u.com熱心網友回復：
如果你有 Microsoft 365，則可以使用：
=IF(LEFT(A1)="0",",","")&TEXTJOIN(",",TRUE,TEXTSPLIT(A1,"0"))&IF(RIGHT(A1)="0",",","")
- 拆分為零
- 使用逗號分隔符連接拆分文本
- 必須專門測驗第一個字符
- 以及@TM指出的最后一個字符
uj5u.com熱心網友回復：
另一種選擇是按如下方式檢查字符陣列：
- a) 通過將輸入字串原子化為
tmp單個字符陣列String2Arr() - b) 檢查
tmpvia中的零字符CheckChar - c) 執行負過濾，保留每個 0 序列中的第一個零，通過
Filter(tmp, delChar, False) - d) 回傳連接字串
Function Rep0(ByVal s As String, Optional delChar As String = "0")
'Purp.: replace first zero in each 0-sequence by ",", delete any remaining zeros
Dim tmp: tmp = String2Arr(s) ' a) atomize string to character array
Dim i As Long
For i = LBound(tmp) To UBound(tmp) ' b) check zero characters
Dim old As String: CheckChar tmp, i, old, delChar
Next
tmp = Filter(tmp, delChar, False) ' c) negative filtering preserving non-deletes
Rep0 = Join(tmp, vbNullString) ' d) return cleared string
End Function
幫助程式
Sub CheckChar(ByRef arr, ByRef i As Long, ByRef old As String, _
ByVal delChar As String, Optional replChar As String = ",")
'Purp.: replace 1st delChar "0" in array (depending on old predecessor)
If Left(arr(i), 1) = delChar Then ' omit possible string end character
If Not old Like "[" & delChar & replChar & "]" Then arr(i) = replChar
End If
old = arr(i) ' remember prior character
End Sub
Function String2Arr(ByVal s As String)
'Purp.: atomize input string to single characters array
s = StrConv(s, vbUnicode)
String2Arr = Split(s, vbNullChar, Len(s) \ 2)
End Function
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/536518.html
標籤：擅长VBA数据表数据清理
上一篇：VBA使用變數呼叫單元格