Removing first set of numbers in string

Question

I have a list of items:

B100Q91, B75NX2, BR100, XN20ZN..

I want to remove the first set of numbers in every item, so that it looks like this:

BQ91, BNX2, BR, XNZN..

My approach looks like this:

Function RemoveFirstNumbers(Txt As String) As String
With CreateObject("VBScript.RegExp")
Dim posn As Integer
posn = GetPositionOfFirstNumericCharacter(Txt)
1
If (IsError(posn)) = True Then Replace(Txt, 1, 1) As String
Dim posn As Integer
Else
End With
End Function
End If
GoTo 1

with 

Public Function GetPositionOfFirstNumericCharacter(ByVal s As String) As Integer
Dim i As Integer
    For i = 1 To Len(s)
        Dim currentCharacter As String
        currentCharacter = Mid(s, i, 1)
        If IsNumeric(currentCharacter) = True Then
            GetPositionOfFirstNumericCharacter = i
            Exit Function
        End If
    Next i
End Function

Note that vb.net is not the same as VBA and you should tag regexp - I've made those changes for you. — SJR
– SJR, Commented Dec 3, 2020 at 21:57

basodre · Accepted Answer · 2020-12-04 14:24:55Z

2

EDIT to package the below as a function

Function ReplaceFirstDigits(sLookupExpression) as String
    Dim oReg As Object

    Set oReg = CreateObject("VBScript.Regexp")

    With oReg
        .Global = True
        .ignorecase = True
        .MultiLine = False
        .Pattern = "([A-Za-z]+)(\d+)(\w*)"
    End With

    ReplaceFirstDigits = oReg.Replace(sLookupExpression, "$1$3")
End Function

Then you can call the function directly from the spreadsheet with a standard syntax, such as =ReplaceFirstDigits(A1)

If you wanted to use a regular expression, code such as the following might do the trick for you.

Sub Test()
    Dim oReg As Object
    
    Set oReg = CreateObject("VBScript.Regexp")
    
    With oReg
        .Global = True
        .ignorecase = True
        .MultiLine = False
        .Pattern = "([A-Za-z]+)(\d+)(\w*)"
    End With
    
    Debug.Print oReg.Replace(Selection, "$1$3")

End Sub

edited Dec 4, 2020 at 14:24

answered Dec 3, 2020 at 21:16

basodre

5,7901 gold badge18 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ron Rosenfeld Over a year ago

You can simplify that with .Global=False and .Pattern="\d+" Multiline is not needed. Your output would come from .Replace(Selection, "") With .Global=False, only the first set of digits will be matched.

Quant1337 Over a year ago

Hi, thanks for taking the time. How can I call this macro in excel?

basodre Over a year ago

@Quant1337 You can create a function using the interior logic. Replace Sub Test with Function Test (or replace 'Test' with something more descriptive). Then return the value from the function. I'll make an edit to my post showing these changes.

DS_London · Accepted Answer · 2020-12-04 08:57:55Z

1

EDITED:

If you don't want to use RegExp, just straight VBA:

Option Explicit

Function RemoveFirstNumber(strInput As String) As String
    Dim strRet As String
    
    Dim bFirstNumber As Boolean
    bFirstNumber = False
    
    Dim strChar As String
    Dim nChar As Integer
    
    For nChar = 1 To Len(strInput)
        strChar = Mid(strInput, nChar, 1)
        
        If IsNumeric(strChar) Then
            bFirstNumber = True
        Else
            If bFirstNumber Then 
               strRet = strRet & Mid(strInput, nChar)
               Exit For
            End If
            
            strRet = strRet & strChar
        End If
    Next nChar
    
    RemoveFirstNumber = strRet
End Function

edited Dec 4, 2020 at 8:57

answered Dec 3, 2020 at 21:26

DS_London

4,3311 gold badge12 silver badges33 bronze badges

2 Comments

Quant1337 Over a year ago

Hi your code works, but only on some examples. Examples like BR100D, BRX100W, where only one letter is on the right side don't work - do you have an idea why?

DS_London Over a year ago

@Quant1337 Ah yes ... didn’t test that case! I’ve edited the code, so hopefully this works better (not at my PC right now).

Collectives™ on Stack Overflow

Removing first set of numbers in string

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related