Excel -在数字和单词之间添加空格

Question

我正在寻找一种在文本值和数值之间放置一个空格的方法，但是有一个问题。有时，文本也可能以一个数字开头，我不希望它包含空格。例如

Col A                 Col B
Name1:3-2             Name 1:3-2
Name6:5,4             Name 6:5,4
1 Val55:12-4          1 Val 55:12-4
2 Val 22:43           2 Val 22:43
Name10                Name 10

其中，Col A是值，而Col B包含向值添加空间的公式Col A。

这里有几件事要注意：

1. 在文本结束后，空格总是添加到第一个数字中。
2. 如果值以一个应该忽略的数字开头，那么文本后面的第一个数字应该被添加到其中。
3. 如果已经有一个空格，则不应该添加另一个空间。
4. 数字变化之前的文本长度。
5. 在第一个数字之后的值之间并不总是有一个：

我正在处理的数据集大约是1,000个条目，所以速度是不必要的，只需要一些对所有情况都有效的东西，因为我不想查看那么多条目并添加一个空格。

多亏了加里的学生，编辑了最终解决方案：

' Function Assumes that there are atleast 2 characters in front of the expected space
' Function Assumes that there are no numbers within the text that will cause an early splitting of characters
Public Function SpacedOut(sIn As String) As String
    Dim L As Long, i As Long, Done As Boolean
    Dim sOut As String
    L = Len(sIn)
    Done = False
    sOut = Left(sIn, 1)
    ' Skips the first possible number if it is there if not, we are safe to start at 2
    ' Since there will always be more than one character prior to the expected first number
    For i = 2 To L
        ' Check for a number without a space before it
        If Mid(sIn, i - 1, 1) <> " " And Mid(sIn, i, 1) Like "[0-9]" And Not Done Then
            Done = True
            sOut = sOut & " " & Mid(sIn, i, 1)
        ' Check for a space with a number after it and continue on if this is found
        ElseIf Mid(sIn, i - 1, 1) = " " And Mid(sIn, i, 1) Like "[0-9]" And Not Done Then
            Done = True
            sOut = sOut & Mid(sIn, i, 1)
        ' Append next character
        Else
            sOut = sOut & Mid(sIn, i, 1)
        End If
    Next i
    SpacedOut = sOut
End Function

谢谢，DMan

Answer 1

试试这个小型的UDF：

Public Function SpacedOut(sIn As String) As String
    Dim L As Long, i As Long, Done As Boolean
    Dim sOut As String
    L = Len(sIn)
    Done = False
    sOut = Left(sIn, 1)
    For i = 2 To L
        If Mid(sIn, i - 1, 1) Like "[a-zA-Z]" And Mid(sIn, i, 1) Like "[0-9]" And Not Done Then
            Done = True
            sOut = sOut & " " & Mid(sIn, i, 1)
        Else
            sOut = sOut & Mid(sIn, i, 1)
        End If
    Next i
    SpacedOut = sOut
End Function

EDIT#1:

下面是我的VBE屏幕的一个快照，里面的模块是高亮度的：

​

​