从字符串中删除所有初始数字，直到达到非数值字符为止。

I am trying to remove the first numbers of a string of characters (remove all numbers until first non-numerical character is reached). Some strings have starting numbers formatted in the form of "14 214" where it should read 14214. This is the special space for separating numbers, and if the string in A1 starts by 14 214 then

我正在尝试删除一串字符的第一个数字(删除所有数字，直到第一个非数字字符到达)。有些字符串的起始数字格式化为“14214”，应该是14214。这是分离数字的特殊空间，如果A1中的字符串从14214开始

ISNUMBER(LEFT(A1,3)*1)=TRUE

So that means that the space is not a problem, I just have to check for the first non-numerical character.

这意味着空间不是问题，我只需要检查第一个非数字字符。

I thought of the following VBA function:

我想到了以下VBA函数:

Function RemoveNumbers(Txt As String) As String

i = 1
Do While i < 9
    If (IsError(Left(Txt, i) * 1)) = "False" Then
    i = i + 1
Else
    RemoveNumbers = Right(Txt, Len(Txt) - i)
End If
Loop
End Function

But it returns #VALUE!

但它返回#价值!

Is the function correctly written? Do you have any suggestions?

这个函数的书写正确吗?你有什么建议吗?

Thanks

谢谢

2 个解决方案

#1

Walk along the string from left to right, looking at each character.
If the char is a space do nothing, if its a number replace it with a space otherwise return the string with leading spaces removed:

从左到右沿着线走，看看每个字符。如果char是一个空格，什么都不做，如果它是一个数字，用空格替换它，否则返回带前导空格的字符串:

Function RemoveNumbers(txt As String) As String
    Dim i As Long
    For i = 1 To Len(txt)
        Select Case Mid$(txt, i, 1)
            Case " ":
            Case "0" To "9": Mid$(txt, i, 1) = " "
            Case Else
                Exit For
        End Select
    Next
    RemoveNumbers = LTrim$(txt)
End Function

#2

Good solution from Alex K.

Alex K提出了很好的解决方案。

I would just like to add that the basic problem with the original program was that iserror does not catch the number conversion error - as soon as that occurs the whole function exits and you just get a value error because RemoveNumbers is not set. Also the error doesn't occur when you have left(txt,i)="14 ", but only on the next character when you have left(txt,i)="14 2". To make it work you would have to do something like this

我想补充的是,原始程序的基本问题是,返回错误不抓数量转换错误,一旦发生整个函数退出,你就得到一个值错误,因为RemoveNumbers也不设置。错误不会发生当你离开(txt,我)=“14”,但只有在下一个角色,当你离开(txt,I)=“14 2”。要使它工作，你必须做这样的事情

Function RemoveNumbers(Txt As String) As String

On Error GoTo Handler

i = 1

Do While i <= Len(Txt)
    firstNumber = Left(Txt, i) * 1
    i = i + 1
Loop

Handler: 
    RemoveNumbers = Right(Txt, Len(Txt) - i + 1)    
End Function