VBA:在字符串中的upppercase字母之后或之前放置一个空格 [英] VBA: Put a space after or before a upppercase letter in a string
问题描述
我有一系列代表客户姓名的文字,但每个词/名称之间没有空格(例如: JohnWilliamsSmith )。然而,单词可以区分,因为每个单词的第一个字母是大写字母。
所以我需要将客户字符串列表转换为常规格式,每个字符之间有空格字。所以我希望JohnWilliamsSmith 成为John Williams Smith 。然而,我想不到立即实现这一点,我相信没有任何Excel公式组合可以提供这个结果。
解决办法是设置一个宏。它可能是一个 Function
用作公式,或者模块中的代码用于处理特定范围内的数据(想象列表位于 Range(A2:A100)
)。 有人知道我该怎么做吗? $ b $ div class =h2_lin>解决方案
函数AddSpaces(PValue As String)As String
Dim xOut As String
xOut = VBA.Left(PValue,1)
For i = 2 To VBA.Len(PValue)
xAsc = VBA.Asc(VBA.Mid(PValue,i, 1))
如果xAsc> = 65并且xAsc< = 90那么
xOut = xOut& & VBA.Mid(PValue,i,1)
其他
xOut = xOut& VBA.Mid(PValue,i,1)
End If
Next
AddSpaces = xOut
End Function
注意:使用这个函数公式是= Addspace(A1)。
I have a sequence of texts that represent customers names, but they do not have spaces between each word/name (for example: JohnWilliamsSmith). However the words can be differentiated because the first letter of each word is uppercase.
So I need to transpose this list of customers strings to their regular format, with spaces between each word. So I want JohnWilliamsSmith to become John Williams Smith. However, I couldn't think of an immediate way to achieve this, and I believe that no combination of Excel formulas can offer this result.
Thus, I think the only solution would be to set up a Macro. It might be a Function
to be used as a formula, or a code in module to work the data in a certain range (imagine that the list is in Range ("A2: A100")
).
Does anyone have any idea how I can do this?
Function AddSpaces(PValue As String) As String
Dim xOut As String
xOut = VBA.Left(PValue, 1)
For i = 2 To VBA.Len(PValue)
xAsc = VBA.Asc(VBA.Mid(PValue, i, 1))
If xAsc >= 65 And xAsc <= 90 Then
xOut = xOut & " " & VBA.Mid(PValue, i, 1)
Else
xOut = xOut & VBA.Mid(PValue, i, 1)
End If
Next
AddSpaces = xOut
End Function
NB: Use this Function formula is =Addspace(A1).
这篇关于VBA:在字符串中的upppercase字母之后或之前放置一个空格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!