如何在单词之间添加空格而不删除任何字母

Question

我试着在下面的代码"0a"但是这个代码去掉了0a，替换了空格。我希望得到这样的输出- "0 a "有添加空间的功能吗?

Sub ReSpace()
Columns("H").Replace What:="0a", _
space:="0", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
End Sub

Answer 1

试试这个:

Columns("H").Replace What:="0a", _
replacement:="0 a", _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False

Answer 2

你没有问澄清问题…

如果您需要获得的替换字符串模式，任何字符串要替换，可以使用下一个函数。字节数组提供了一种快速的方法来完成它:

Function strSpacesPattern(x As String) As String
Dim arrB() As Byte: arrB = StrConv(x, vbFromUnicode) 'Place the string in a byte array (without the intermediary 0 if directly converting as arrB = x)
Dim i As Long, k As Long
Dim arrB2() As Byte: ReDim arrB2(Len(x) * 2 - 1) 'declare and redim as second array able to keep the existing bytes, a space byte between each other and one at the end
For i = 0 To UBound(arrB2)           'iterate between array (now empty) elements and fill it alternatively
If i Mod 2 Then
arrB2(i) = Asc(" ")           'a corresponding byte for space, in between each existing array byte
Else
arrB2(i) = arrB(k): k = k + 1 'each element of arrB
End If
Next i
strSpacesPattern = StrConv(arrB2, vbUnicode) & " " 'reconvert the array in a string containing each string byte separated by spaces plus an ending space.
End Function

可以用下面的方法进行测试/使用:

Sub testStringSpacesPattern()
Dim x As String: x = "abcd"
Debug.Print """" & strSpacesPattern(x) & """"
End Sub

:

下一个函数使用每个字节后面包含一个零的字节数组(不使用StrConv):

Function strSpacesPatt(x As String) As String
Dim arrB() As Byte: arrB = x   'Place the string in a byte array
Dim i As Long, k As Long
Dim arrB2() As Byte: ReDim arrB2(Len(x) * 4 - 1) 'declare and redim as second array able to keep the existing bytes, a space byte between each other and a space at the end
For i = 0 To UBound(arrB2) Step 4        'iterate between array (now empty) elements and fill it
arrB2(i) = arrB(k): k = k + 1      'add the real byte
arrB2(i + 1) = 0: k = k + 1        'add the intermediary zero
arrB2(i + 2) = Asc(" "): arrB2(i + 3) = 0 'add the space byte and the intermediary 0
Next i
x = arrB2: strSpacesPatt = x & " ": Exit Function
End Function

测试:

Sub testStringSpacesPattern()
Dim x As String: x = "abcde"
Debug.Print """" & strSpacesPatt(x) & """"
End Sub

事实上，我刚刚玩了VBA，把假设作为一个挑战…:)